Агуулга
Хэмжээг нь өөрчлөх боломжтой, өөрөөр хэлбэл ажлын явцад мөрийн (баганын) тоо нэмэгдэж, буурч болох Excel-ийн өгөгдөл бүхий хүснэгтүүд танд байна уу? Хэрэв хүснэгтийн хэмжээ "хөвөгч" байвал та энэ мөчийг байнга хянаж, засах хэрэгтэй болно.
- Манай хүснэгтэд хамаарах тайлангийн томьёоны холбоосууд
- Манай хүснэгтийн дагуу бүтээгдсэн пивот хүснэгтүүдийн эхний мужууд
- Манай хүснэгтийн дагуу бүтээгдсэн диаграмын эхний мужууд
- Манай хүснэгтийг өгөгдлийн эх сурвалж болгон ашигладаг уналтанд зориулсан мужууд
Энэ бүхэн таныг уйдаахгүй 😉
Мэдээллийн мөр, баганын бодит тоонд автоматаар тохируулагдах динамик "резин" мужийг бий болгох нь илүү тохиромжтой бөгөөд зөв байх болно. Үүнийг хэрэгжүүлэхийн тулд хэд хэдэн арга байдаг.
Арга 1. Ухаалаг ширээ
Нүднүүдийн хүрээг тодруулаад таб дээрээс сонгоно уу Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах):
Хэрэв танд гаж нөлөө болгон хүснэгтэд нэмсэн судалтай загвар хэрэггүй бол гарч ирэх таб дээрээс үүнийг унтрааж болно. Зохион бүтээгч (дизайн). Ийм байдлаар үүсгэсэн хүснэгт бүр нь таб дээрх ижил газарт илүү тохиромжтой нэрээр сольж болох нэрийг хүлээн авдаг. Зохион бүтээгч (дизайн) салбарт Хүснэгтийн нэр (Хүснэгтийн нэр).
Одоо бид "ухаалаг ширээ" рүүгээ динамик холбоосыг ашиглаж болно:
- Хүснэгт 1 – толгойн мөрөөс бусад хүснэгтийг бүхэлд нь холбох (A2:D5)
- Хүснэгт1[#Бүгд] - хүснэгтийг бүхэлд нь холбох (A1:D5)
- Хүснэгт1[Питер] – эхний нүдний толгойгүй муж баганы лавлагаа (C2:C5)
- Хүснэгт1[#Толгой] – баганын нэр бүхий “толгой” холбоос (A1:D1)
Ийм лавлагаа нь томъёонд маш сайн ажилладаг, жишээлбэл:
= SUM (Хүснэгт1[Москва]) - "Москва" баганын нийлбэрийн тооцоо
or
=VPR(F5;Хүснэгт 1;3;0) – хүснэгтээс F5 нүднээс тухайн сарыг хайж олоод Санкт-Петербургийн нийлбэрийг гарга (VLOOKUP гэж юу вэ?)
Таб дээрээс сонгох замаар пивот хүснэгт үүсгэх үед ийм холбоосыг амжилттай ашиглаж болно Оруулах – Пивот хүснэгт (Оруулах – Пивот хүснэгт) ухаалаг хүснэгтийн нэрийг мэдээллийн эх сурвалж болгон оруулах:
Хэрэв та ийм хүснэгтийн фрагментийг (жишээлбэл, эхний хоёр багана) сонгоод ямар ч төрлийн диаграмм үүсгэвэл шинэ мөр нэмэх үед тэдгээр нь диаграммд автоматаар нэмэгдэх болно.
Унждаг жагсаалт үүсгэх үед ухаалаг хүснэгтийн элементүүдийн шууд холбоосыг ашиглах боломжгүй, гэхдээ та тактикийн заль мэхийг ашиглан энэ хязгаарлалтыг хялбархан даван туулах боломжтой - функцийг ашиглана уу. ҮНЭГҮЙ (ШУУД), энэ нь текстийг холбоос болгон хувиргадаг:
Тэдгээр. текст мөр хэлбэрээр ухаалаг хүснэгтийн холбоос (хашилтанд!) бүрэн хэмжээний холбоос болж хувирдаг бөгөөд доош унах жагсаалт нь үүнийг ихэвчлэн хүлээн авдаг.
Арга 2: Динамик нэртэй муж
Хэрэв ямар нэг шалтгааны улмаас таны өгөгдлийг ухаалаг хүснэгт болгон хувиргах нь зохисгүй бол та арай илүү төвөгтэй, гэхдээ илүү нарийн, уян хатан аргыг ашиглаж болно - Excel дээр манай хүснэгтэд хамаарах динамик нэртэй муж үүсгэж болно. Дараа нь ухаалаг хүснэгтийн нэгэн адил та үүсгэсэн хүрээний нэрийг дурын томъёо, тайлан, график гэх мэт чөлөөтэй ашиглаж болно. Энгийн жишээгээр эхэлье:
Даалгавар: хотуудын жагсаалтад хамаарах динамик нэртэй муж үүсгэх ба шинэ хот нэмэх эсвэл устгах үед автоматаар сунах, багасгах.
Бидэнд ямар ч хувилбарт ашиглах боломжтой хоёр суулгасан Excel функц хэрэгтэй болно POICPOZ (ТОГЛОЛТ) мужын сүүлчийн нүдийг тодорхойлох, мөн INDEX (INDEX) динамик холбоос үүсгэх.
MATCH ашиглан сүүлийн нүдийг олох
MATCH(хайх_утга, муж, тохирох_төрөл) – өгөгдсөн утгыг мужид (мөр эсвэл багана) хайж, олсон нүдний дарааллын дугаарыг буцаадаг функц. Жишээлбэл, A1:A5 баганын дөрөв дэх нүдэнд "Гуравдугаар сар" гэсэн үг байрлаж байгаа тул MATCH("Гуравдугаар сар";A0:A4;1) томъёо нь 5-ийн тоог буцаана. Сүүлийн функцийн аргумент Match_Type = 0 нь бид яг тохирохыг хайж байна гэсэн үг юм. Хэрэв энэ аргументыг заагаагүй бол функц нь хамгийн ойрын утгыг хайх горимд шилжих болно - энэ нь бидний массив дахь хамгийн сүүлд эзлэгдсэн нүдийг олоход амжилттай хэрэглэгдэх зүйл юм.
Трикийн мөн чанар нь энгийн зүйл юм. MATCH нь дээрээс доош хүртэлх зайн нүднүүдийг хайдаг бөгөөд онолын хувьд өгөгдсөн хамгийн бага утгыг олох үед зогсох ёстой. Хэрэв та хүснэгтэд байгаа бүх утгаас илт их утгыг хүссэн утгаараа зааж өгвөл MATCH хүснэгтийн хамгийн төгсгөлд хүрч, юу ч олохгүй бөгөөд хамгийн сүүлд бөглөсөн нүдний дарааллын дугаарыг өгнө. Мөн бидэнд хэрэгтэй байна!
Хэрэв манай массивт зөвхөн тоонууд байгаа бол бид хүссэн утгаараа тоог зааж өгч болох бөгөөд энэ нь хүснэгтийн аль нэгээс илүү байх нь ойлгомжтой.
Баталгаажуулахын тулд та Excel-ийн зарчмын хувьд ажиллах боломжтой хамгийн их тоо болох 9E + 307 (9-ыг 10-ийн хүчийг 307-ийг үржүүлж, 9-ийг 307 тэгтэй) ашиглаж болно.
Хэрэв манай баганад текстийн утгууд байгаа бол та хамгийн их боломжтой тоотой тэнцэхүйц REPEAT("i", 255) - 255 үсгээс бүрдэх "i" текстийн мөр - сүүлийн үсэгийг оруулж болно. цагаан толгой. Excel хайлт хийхдээ тэмдэгтийн кодыг харьцуулдаг тул манай хүснэгтэд байгаа ямар ч текст техникийн хувьд ийм урт "ээээээээээээээээээээээээээээээээээээээээээээээээээээээээээ" гэсэн мөрээс "жижиг" байх болно.
INDEX ашиглан холбоос үүсгэнэ үү
Хүснэгт дэх хамгийн сүүлчийн хоосон бус элементийн байрлалыг бид мэдэж байгаа тул энэ нь бидний бүх хүрээтэй холбоос үүсгэх хэвээр байна. Үүний тулд бид функцийг ашигладаг:
INDEX(муж; мөрийн_тоо; баганын_тоо)
Энэ нь нүдний агуулгыг мөр, баганын дугаараар өгдөг, тухайлбал, өмнөх аргын хот, сар бүхий хүснэгтэд =INDEX(A1:D5;3;4) функц нь 1240 – агуулгыг өгнө. 3-р мөр ба 4-р баганаас, өөрөөр хэлбэл D3 нүднүүд. Хэрэв зөвхөн нэг багана байгаа бол түүний дугаарыг орхиж болно, өөрөөр хэлбэл INDEX(A2:A6;3) томъёо нь сүүлийн дэлгэцийн агшинд "Самара"-г өгнө.
Нэг тийм ч тодорхой бус нюанс бий: хэрэв INDEX-ийг ердийнх шиг = тэмдгийн дараа нүдэнд оруулаад зогсохгүй, хоёр цэгийн дараах мужийг лавлах эцсийн хэсэг болгон ашигладаг бол энэ нь цаашид гарахаа болино. эсийн агуулга, гэхдээ түүний хаяг! Тиймээс $A$2:INDEX($A$2:$A$100;3) гэх мэт томъёо нь гаралт дээрх A2:A4 мужийг заах болно.
Эндээс жагсаалтын төгсгөлийг динамикаар тодорхойлохын тулд бид INDEX дотор оруулдаг MATCH функц гарч ирдэг.
=$A$2:INDEX($A$2:$A$100; ТААРУУЛАХ(REP("I";255);A2:A100))
Нэрлэсэн муж үүсгэх
Энэ бүгдийг нэг цогц болгон багцлахад л үлддэг. Таб нээх томъёо (томъёо) Мөн дээр нь дарна уу Нэрийн менежер (Нэр менежер). Нээгдсэн цонхон дээр товчлуур дээр дарна уу бий болгох (шинэ), талбарт манай мужын нэр болон томъёог оруулна уу Range (Лавлагаа):
Энэ нь дээр дарахад л үлддэг OK бэлэн хүрээг ямар ч томьёо, доош унадаг жагсаалт эсвэл диаграммд ашиглаж болно.
- VLOOKUP функцийг ашиглан хүснэгтүүдийг холбох, утгыг хайх
- Автоматаар бөглөх унадаг жагсаалтыг хэрхэн үүсгэх вэ
- Их хэмжээний өгөгдөлд дүн шинжилгээ хийх пивот хүснэгтийг хэрхэн үүсгэх