Мундариҷа
Оё шумо дар 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 (ИНДЕКС) барои сохтани пайванди динамикӣ.
Ҷустуҷӯи ячейкаи охирин бо истифода аз MATCH
MATCH(арзиши_ҷустуҷӯ, диапазон, навъи мувофиқ) – функсияе, ки арзиши додашударо дар диапазон (сатр ё сутун) ҷустуҷӯ мекунад ва рақами тартибии ячейкаеро, ки дар он ҷо ёфт шудааст, бармегардонад. Масалан, формулаи MATCH("Март";A1:A5;0) дар натиҷа рақами 4-ро бармегардонад, зеро калимаи "Март" дар чашмаки чоруми сутуни A1:A5 ҷойгир аст. Далели охирини функсия Match_Type = 0 маънои онро дорад, ки мо мувофиқати дақиқро меҷӯем. Агар ин аргумент муайян карда нашавад, он гоҳ функсия ба ҳолати ҷустуҷӯ барои наздиктарин арзиши хурдтарин мегузарад - маҳз ҳамон чизест, ки барои ёфтани ячейкаи охирини ишғолшуда дар массиви мо бомуваффақият истифода мешавад.
Моҳияти ҳилла оддӣ аст. MATCH чашмакҳоро дар диапазон аз боло то поён ҷустуҷӯ мекунад ва дар назария, вақте ки арзиши хурдтаринро ба арзиши додашуда пайдо мекунад, бояд қатъ шавад. Агар шумо арзишеро, ки баръало калонтар аз ҳар кадоме, ки дар ҷадвал мавҷуд аст, ҳамчун арзиши дилхоҳ муайян кунед, MATCH то охири ҷадвал мерасад, чизе намеёбад ва рақами пайдарпайии ячейкаи охирини пуршударо медиҳад. Ва мо ба он ниёз дорем!
Агар дар массиви мо танҳо рақамҳо мавҷуд бошанд, он гоҳ мо метавонем рақамро ҳамчун арзиши дилхоҳ нишон диҳем, ки он бешубҳа аз ҳар яке аз рақамҳои ҷадвал бузургтар аст:
Барои кафолат, шумо метавонед рақами 9E + 307 (9 маротиба 10 ба 307, яъне 9 бо 307 сифр) истифода баред - шумораи ҳадди аксар, ки Excel метавонад бо он кор кунад.
Агар дар сутуни мо арзишҳои матнӣ мавҷуд бошанд, пас ҳамчун муодили шумораи калонтарини имконпазир шумо метавонед сохтори REPEAT("i", 255) - сатри матнии иборат аз 255 ҳарф "i" - ҳарфи охирини алифбо. Азбаски Excel воқеан рамзҳои аломатҳоро ҳангоми ҷустуҷӯ муқоиса мекунад, ҳар як матни ҷадвали мо аз ҷиҳати техникӣ аз чунин сатри тӯлонии "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" "хурдтар" хоҳад буд:
Бо истифода аз 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 медиҳад.
Ва дар ин ҷо функсияи MATCH ворид мешавад, ки мо онро дар дохили INDEX барои динамикӣ муайян кардани охири рӯйхат дохил мекунем:
=$A$2:ИНДЕКС($A$2:$A$100; МАТЧ(REP("Ман";255);A2:A100))
Диапазони номбаршуда эҷод кунед
Ҳамаашро дар як ягона ҷамъ кардан боқӣ мемонад. Варақаро кушоед формула (Формулаҳо) Ва тугмаи чапро пахш кунед Менеҷери ном (Мудири ном). Дар равзанаи кушодашуда тугмаро пахш кунед сохтан (нав), номи диапазон ва формулаи моро дар майдон ворид кунед диапазон (Истинод):
Барои пахш кардан боқӣ мемонад OK ва диапазони тайёрро дар ҳама гуна формулаҳо, рӯйхатҳои афтанда ё диаграммаҳо истифода бурдан мумкин аст.
- Истифодаи функсияи VLOOKUP барои пайваст кардани ҷадвалҳо ва ҷустуҷӯи арзишҳо
- Чӣ тавр сохтани рӯйхати афтанда ба таври худкор
- Чӣ тавр сохтани ҷадвали пивот барои таҳлили миқдори зиёди маълумот