Excel дээр томъёо хэрхэн бичих вэ? Сургалт. Хамгийн их шаардлагатай томъёо

Өдрийн мэнд

Нэгэн удаа, Excel дээр өөрөө томъёо бичих нь надад үнэхээр итгэмээргүй зүйл байв. Энэ програм дээр би олон удаа ажиллах ёстой байсан, гэхдээ би текстээс өөр юу ч бөглөсөнгүй ...

Мэдэгдэж байгаагаар ихэнх томъёо нь тийм ч төвөгтэй зүйл биш бөгөөд та шинэхэн компьютерийн хэрэглэгчдэд ч хялбархан ажиллах боломжтой. Энэ нийтлэлд би зөвхөн хамгийн их шаардлагатай ажилладаг томъёололуудаа дэлгэхийг хүсч байна.

Ингээд эхэлье ...

1. Үндсэн үйл ажиллагаа ба суурь. Excel-ийн үндсийг сур.

Нийтлэл дэх бүх үйлдлийг Excel хувилбар 2007 дээр үзүүлнэ.

Excel програмыг эхлүүлсний дараа - олон нүдтэй цонх гарч ирнэ - бидний хүснэгт. Хөтөлбөрийн гол онцлог нь таны бичсэн томъёог (тооцоолуур болгон) унших боломжтой юм. Дашрамд хэлэхэд та нүд бүр дээр томъёо нэмж болно!

Томъёо нь "=" тэмдэгээс эхлэх ёстой. Энэ бол урьдчилсан нөхцөл юм. Дараа нь та тооцоолох хэрэгтэй зүйлээ бичнэ үү: жишээлбэл, "= 2 + 3" (ишлэлгүйгээр), Enter товчийг дарахад үр дүн нь "5" гарч ирэх болно. Доорх дэлгэцийн агшинг үзнэ үү.

Чухал! "5" тоог A1 нүдэнд бичсэн боловч томъёогоор ("= 2 + 3") тооцоолно. Хэрэв дараагийн нүдэнд текстийг "5" гэж бичвэл, та нүдний томъёо засварлагч дээр хулганаа авмагц (дээрх мөр, Fx ажиллуулна) - та "5" гэсэн үндсэн дугаарыг харах болно.

Одоо нэг нүдэнд та зөвхөн 2 + 3 утга биш, харин нэмэх хэрэгтэй тоонуудын тоог бичиж болно гэж төсөөлөөд үз дээ. "= B2 + C2" гэж үзье.

Мэдээжийн хэрэг B2 ба C2-д хэдэн тоо байх ёстой, эс тэгвээс Excel нь A1 нүдэнд биднийг үр дүн нь 0 харуулна.

Бас нэг чухал зүйл ...

Томъёо байгаа нүдийг, жишээ нь A1-ийг хуулж аваад өөр нүдэнд буулгахдаа энэ нь хуулбарлагдаж буй "5" утга биш харин томъёо өөрөө юм!

Түүнээс гадна томъёо шууд харьцаагаар өөрчлөгдөнө. хэрэв A1-ийг A2-т хуулсан бол А2 нүдэн дэх томъёо нь "= B3 + C3" болно. Excel нь томъёог өөрөө автоматаар өөрчилдөг: хэрэв A1 = B2 + C2 байвал A2 = B3 + C3 (бүх тоо 1-ээр нэмэгдсэн) нь логик юм.

Үр дүн нь, A2 = 0 байна, учир нь B3 ба C3 эсүүд тодорхойлогдоогүй тул 0-тэй тэнцүү байна.

Тиймээс, та томъёог нэг удаа бичиж, дараа нь хүссэн баганын бүх нүдэнд хуулж болно - Excel нь таны хүснэгтийн мөр бүрт тооцоолох болно!

Хэрэв та B2 ба C2-ийг хуулбарлах явцад өөрчлөгдөхийг хүсэхгүй ба эдгээр нүдэнд байнга хавсарч байхыг хүсч байвал тэдэнд "$" дүрсийг нэмж оруулна уу. Үүний жишээ доор байна.

Ийм байдлаар, А1 нүдийг хаана ч хамаагүй хуулж байрлуулсан тохиолдолд, энэ нь үргэлж холбогдсон нүднүүдэд хамаарна.

2. Мөр дэх утгуудыг нэмэх (SUMM ба SUMMESLIMN томъёо)

Мэдээжийн хэрэг та A1 + A2 + A3 гэх мэт томъёог гаргаж эс бүрийг нэмж болно. Гэхдээ зовж шаналахгүйн тулд Excel-д тусгай томъёо байдаг бөгөөд энэ нь таны сонгосон нүдэнд бүх утгыг нэмж өгдөг!

Энгийн жишээ ав. Нөөцөд хэд хэдэн төрлийн бараа байдаг бөгөөд бүтээгдэхүүн тус бүрийг кг тутамд хэдэн хувь болохыг мэддэг. нөөцөд байгаа. Тооцоолоод үзье, гэхдээ бүгд кг нь хичнээн жинтэй вэ. агуулахад байгаа ачаа.

Үүнийг хийхийн тулд үр дүн гарч ирэх нүдэнд очоод "= SUM (C2: C5)" гэсэн томъёог бичнэ үү. Доорх дэлгэцийн агшинг үзнэ үү.

Үүний үр дүнд сонгогдсон мужийн бүх нүдийг нэгтгэж, та үр дүнг харах болно.

2.1. Нөхцөлд нэмэлт (нөхцөлтэй)

Одоо бид тодорхой нөхцөлтэй байна гэж төсөөлөөд үз дээ, жишээ нь. Нүдэнд байгаа бүх утгыг нэмж оруулаагүй болно (кг, нөөцөнд), гэхдээ зөвхөн тодорхой хэмжээгээр, жишээ нь 100-аас доош үнэтэй байдаг.

Үүнд зориулсан томъёо бий. "SUMMESLIMN". Нэн даруй жишээ, дараа нь томъёонд тэмдэг тус бүрийн тайлбарыг оруулаарай."

= ТӨЛБӨР (C2: C5; B2: B5; "<100")хаана:

С2: С5 - нэмэгдэх багана (тэдгээр нүднүүд);

B2: B5 хэмжээ - нөхцөлийг шалгах багана (жишээ нь үнэ 100-аас бага);

"<100" - нөхцөл нь өөрөө, нөхцөлийг үнийн санал дотор бичсэн болохыг анхаарна уу.

Энэ томъёонд нарийн төвөгтэй зүйл байхгүй, гол зүйл бол пропорциональ байдлыг ажиглах явдал юм: C2: C5; B2: B5 - зөв; C2: C6; B2: B5 - буруу байна. Байна. нийлбэр болон нөхцөлийн хүрээ нь пропорциональ байх ёстой, эс тэгвэл томъёо нь алдааг буцаана.

Чухал! Нэхэмжлэх олон нөхцөл байж болно, жишээ нь. Та 1-р баганаас биш, харин нэн даруй 10-оос олон тооны нөхцлийг бүрдүүлж шалгаж болно.

3. Нөхцлийг хангасан мөрүүдийн тоог тоолох (томъёо нь COUNTIFLY)

Нийтлэг даалгавраас хэдийг бол: эс доторх утгуудын нийлбэр биш, харин тодорхой нөхцлийг хангаж буй эсийн тоог тооцоолох хэрэгтэй. Заримдаа, маш олон нөхцөл байдал байдаг.

Ингээд ... эхэлцгээе.

Үүнтэй адил жишээн дээр 90-ээс дээш үнэтэй бараа бүтээгдэхүүний тоог тооцоолъё. (Харах юм бол 2 төрлийн ийм бүтээгдэхүүн байдаг. Мандарин, жүрж).

Хүссэн нүдэнд байгаа бараагаа тоолохын тулд бид дараах томъёог бичсэн (дээр үзнэ үү).

= Нягтлан бодох бүртгэл (B2: B5; "> 90")хаана:

B2: B5 хэмжээ - бидний тогтоосон нөхцлийн дагуу тэдгээрийг шалгах хүрээ;

">90" - нөхцөл байдал нь өөрөө ишлэлд байрлуулсан болно.

Одоо жишээгээ жаахан төвөгтэй болгоод, дахиад нэг нөхцлийн дагуу данс нэмж оруулъя: 90-ээс дээш үнэтэй + хувьцааны хэмжээ 20 кг-аас бага байх болно.

Томъёо нь дараахь хэлбэрийг агуулна.

= ТӨЛӨВЛӨГӨӨ (B2: B6; "> 90"; C2: C6; "<20")

Энд нэг нөхцлийг эс тооцвол бүх зүйл ижил хэвээр байна (C2: C6; "<20") Дашрамд хэлэхэд ийм нөхцөл байдал маш их байж болно!

Ийм жижиг хүснэгтэд хэн ч ийм томъёо бичих нь тодорхой биш боловч хэдэн зуун эгнээ бүхий хүснэгтийн хувьд энэ бол огт өөр асуудал юм. Жишээлбэл, энэ хүснэгт нь нүднээс илүү юм.

4. Хайлтын утгыг нэг хүснэгтээс нөгөө хүснэгтэд шилжүүлэх (VLOOKUP томъёо)

Бүтээгдэхүүний шинэ үнийн шошго бүхий шинэ хүснэгт бидэнд ирлээ гэж төсөөлөөд үз дээ. За, хэрэв эдгээр зүйлс 10-20 байгаа бол та бүгдийг нь гараар дахин тохируулах боломжтой. Хэрэв ийм хэдэн зуун зүйл байгаа бол? Хэрэв Excel нь нэг хүснэгтээс нөгөө хүснэгтэд тохирох нэрсийг олж хараад, шинэ үнийн тэмдэглэгээг хуучин хүснэгтэд хуулж авбал илүү хурдан юм.

Ийм даалгаврын хувьд томъёог ашигладаг VPRБайна. Нэгэн удаа тэр энэ гайхамшигтай зүйлийг үзэх хүртлээ "IF" логик томъёогоор "ухаалаг" байсан юм!

Ингээд эхэлье ...

Энэ бол бидний жишээ + үнийн шошго бүхий шинэ хүснэгт юм. Одоо бид шинэ үнийн тэмдэглэлийг шинэ хүснэгтээс хуучин хувилбараар автоматаар солих шаардлагатай байна (шинэ үнийн шошго улаан өнгөтэй).

Курсорыг B2 нүдэнд оруулна уу - жишээ нь. бид үнийн шошго автоматаар өөрчлөх шаардлагатай эхний нүдэнд байрладаг. Дараа нь бид доорхи дэлгэцийн агшинг шиг томъёог бичнэ (дэлгэцийн зургийг оруулсны дараа энэ талаар дэлгэрэнгүй тайлбар өгөх болно).

= VLOOKUP (A2; $ D $ 2: $ E $ 5; 2)хаана

А2 байна - шинэ үнийн шошго авахын тулд бидний хайх утга. Бидний хувьд бид шинэ хүснэгтийн "алим" гэсэн үгийг хайж байна.

$ D $ 2: $ E $ 5 - манай шинэ хүснэгтийг бүрэн сонго (D2: E5, сонголт нь зүүн дээд булангаас баруун доод диагональ руу шилждэг), ж.нь. хаана хайлт хийх вэ. Энэ томъёонд "$" тэмдэг оруулах шаардлагатай бөгөөд ингэснээр та энэ томъёог бусад нүдэнд хуулбарлахдаа D2: E5 өөрчлөгдөхгүй болно!

Чухал! "Алим" гэсэн үгийг хайх нь зөвхөн таны сонгосон хүснэгтийн эхний баганад хийгдэх болно. Энэ жишээн дээр "алим" D хэсгийг хайна.

2 - "Алим" гэсэн үг олдсон тохиолдолд функц нь сонгосон хүснэгтийн аль баганаас (D2: E5) хүссэн утгыг хуулж авахыг мэдэх ёстой. Бидний жишээн дээр 2-р баганаас (E) хуулж ав, учир нь бид эхний баганад (D) хайсан. Хэрэв та хайхаар сонгосон хүснэгт нь 10 баганаас бүрдвэл эхний багана нь хайх бөгөөд 2-оос 10 хүртэлх багана - та хуулж авах дугаарыг сонгож болно.

Нь томъёо = VLOOKUP (A2; $ D $ 2: $ E $ 5; 2) бусад бүтээгдэхүүний нэрээр шинэ утгыг орлуулсан - үүнийг бүтээгдэхүүний үнийн тэмдэг бүхий багана дахь бусад нүдэнд хуулбарлах (бидний жишээнд B3: B5 нүдэнд хуулах). Томъёо нь танд хэрэгтэй шинэ хүснэгтийн баганаас утгыг автоматаар хайж олоод хуулна.

5. Дүгнэлт

Энэ нийтлэлд бид Excel-тэй ажиллах, томъёо хэрхэн бичиж эхлэх талаар судлав. Тэд Excel-д ажилладаг ихэнх хүмүүс ихэвчлэн ажиллах ёстой байдаг хамгийн нийтлэг томъёоны жишээг үзүүлэв.

Угсарсан жишээнүүд нь хэн нэгэнд хэрэгтэй бөгөөд түүний ажлыг хурдасгахад тусална гэж найдаж байна. Сайн туршилт хий!

Жич

Мөн та ямар томъёог ашигладаг вэ? Өгүүлэлд өгөгдсөн томъёог ямар нэгэн байдлаар хялбаршуулах боломжтой юу? Жишээлбэл, сул компьютерууд дээр тооцоолол автоматаар хийгддэг том хүснэгтэд зарим утгууд өөрчлөгдвөл компьютер хэдхэн секундын турш хөлддөг, дахин тооцоолж, шинэ үр дүнг үзүүлдэг ...