Агар шумо корбари навкор набошед, пас шумо бояд аллакай қайд карда бошед, ки 99% ҳама чиз дар Excel барои кор бо ҷадвалҳои амудӣ тарҳрезӣ шудааст, ки дар он параметрҳо ё атрибутҳо (майдонҳо) аз сутунҳо мегузарад ва маълумот дар бораи объектҳо ё рӯйдодҳо ҷойгир аст. дар сатрхо. Ҷадвалҳои ҷамъбастӣ, зермаҷмӯаҳо, нусхабардории формулаҳо бо клики дукарата - ҳама чиз махсус барои ин формати додаҳо мутобиқ карда шудааст.
Бо вуҷуди ин, ҳеҷ гуна қоидаҳо бидуни истисно вуҷуд надоранд ва бо басомади хеле мунтазам аз ман мепурсанд, ки агар дар кор ҷадвали дорои самти семантикии уфуқӣ ё ҷадвале, ки дар он сатрҳо ва сутунҳо вазни якхела доранд, пайдо шавад, чӣ кор кунам:
Ва агар Excel то ҳол медонад, ки чӣ гуна ба таври уфуқӣ ҷудо карданро медонад (бо фармони Маълумот - Ҷудокунӣ - Интихобҳо - Сутунҳоро ҷудо кунед), он гоҳ вазъият бо филтр бадтар аст - танҳо асбобҳои дарунсохт барои филтркунии сутунҳо вуҷуд надоранд, на сатрҳо дар Excel. Ҳамин тавр, агар шумо бо чунин вазифа рӯ ба рӯ шавед, шумо бояд роҳҳои ҳалли дараҷаҳои гуногуни мураккабро пайдо кунед.
Усули 1. Функсияи нави ФИЛТР
Агар шумо дар версияи нави Excel 2021 ё обунаи Excel 365 бошед, шумо метавонед аз хусусияти нав ҷорӣшуда истифода баред филтр (ФИЛТР), ки маълумоти сарчашмаро на танҳо аз рӯи сатрҳо, балки аз рӯи сутунҳо низ филтр карда метавонад. Барои кор кардан, ин функсия як қатори ёрирасони уфуқии якченакаро талаб мекунад, ки дар он ҳар як арзиш (TRUE ё FALSE) муайян мекунад, ки мо сутуни навбатиро дар ҷадвал нишон медиҳем ё баръакс пинҳон мекунем.
Биёед сатри зеринро дар болои ҷадвали худ илова кунем ва ҳолати ҳар як сутунро дар он нависед:
- Фарз мекунем, ки мо ҳамеша мехоҳем сутунҳои якум ва охиринро (сарлавҳаҳо ва ҷамъбаст) нишон диҳем, бинобар ин барои онҳо дар ячейкаҳои якум ва охирини массив арзиши = TRUE -ро муқаррар мекунем.
- Барои сутунҳои боқимонда, мундариҷаи чашмакҳои мувофиқ формулае хоҳанд буд, ки вазъиятро бо истифода аз функсияҳо тафтиш мекунад. И (ВА) or OR (Ё). Масалан, ин маҷмӯъ дар доираи аз 300 то 500 аст.
Баъд аз ин, танҳо барои истифодаи функсия боқӣ мемонад филтр барои интихоби сутунҳое, ки дар боло массиви ёрирасони мо арзиши TRUE дорад, интихоб кунед:
Ба ҳамин монанд, шумо метавонед сутунҳоро аз рӯи рӯйхати додашуда филтр кунед. Дар ин ҳолат, функсия кӯмак хоҳад кард COUNTIF (COUNTIF), ки шумораи такрори номи сутуни навбатиро аз сарлавҳаи ҷадвал дар рӯйхати иҷозатдодашуда тафтиш мекунад:
Усули 2. Ба ҷои ҷадвали муқаррарӣ
Дар айни замон, Excel филтркунии уфуқии дарунсохтро аз рӯи сутунҳо танҳо дар ҷадвалҳои ҷамъоварда дорад, аз ин рӯ, агар мо тавонем ҷадвали аслии худро ба ҷадвали пивот табдил диҳем, мо метавонем ин функсияи дарунсохтро истифода барем. Барои ин, ҷадвали сарчашмаи мо бояд ба шартҳои зерин ҷавобгӯ бошад:
- дорои хати як сатри сарлавҳаи "дуруст" бидуни ячейкаҳои холӣ ва муттаҳидшуда - дар акси ҳол барои сохтани ҷадвали пивот кор намекунад;
- дар тамғакоғазҳои сатрҳо ва сутунҳо нусхаҳои такрориро дарбар нагиред - онҳо дар хулоса ба рӯйхати танҳо арзишҳои беназир "фурӯхта мешаванд";
- танҳо рақамҳоро дар диапазони арзишҳо дар бар мегиранд (дар чорроҳаи сатрҳо ва сутунҳо), зеро ҷадвали ҷамъбастӣ ҳатман ба онҳо ягон намуди функсияи ҷамъкуниро татбиқ мекунад (маблағ, миёна ва ғайра) ва ин бо матн кор намекунад
Агар ҳамаи ин шартҳо риоя карда шаванд, пас барои сохтани ҷадвали пивот, ки ба ҷадвали аслии мо монанд аст, онро (аслӣ) бояд аз кросстабба ба ҳамвор (муқарраршуда) васеъ кардан лозим аст. Ва роҳи осонтарини ин кор бо иловаи Power Query аст, як воситаи пурқудрати табдилдиҳии маълумот дар Excel аз соли 2016 сохта шудааст.
Ҳастанд:
- Биёед ҷадвалро ба фармони динамикии "ақл" табдил диҳем Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал).
- Боркунӣ ба Power Query бо фармон Маълумот - Аз Ҷадвал / Диапазон (Маълумот - Аз Ҷадвал / Диапазон).
- Мо сатрро бо ҷамъбаст филтр мекунем (хулосаи ҷамъбастии худро дорад).
- Дар сарлавҳаи сутуни аввал тугмаи ростро клик кунед ва интихоб кунед Сутунҳои дигарро боз кунед (Боз кардани сутунҳои дигар). Ҳама сутунҳои интихобнашуда ба ду табдил дода мешаванд - номи корманд ва арзиши нишондиҳандаи ӯ.
- Филтр кардани сутун бо ҷамъбасти ба сутун воридшуда хусусиятҳои.
- Аз рӯи ҷадвали ҳамвор (нормӣ)-и натиҷавӣ бо фармон ҷадвали пивот месозем Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед… (Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед…).
Акнун шумо метавонед қобилияти филтр кардани сутунҳои дар ҷадвалҳои ҷамъоварда мавҷудбударо истифода баред - аломатҳои муқаррарӣ дар пеши номҳо ва ашёҳо Филтрҳои имзо (Филтрҳои тамғакоғазӣ) or Филтрҳо аз рӯи арзиш (Филтрҳои арзиш):
Ва албатта, ҳангоми тағир додани маълумот, ба шумо лозим меояд, ки дархости мо ва хулосаро бо миёнабури клавиатура навсозӣ кунед. Ctrl+Alt+F5 ё даста Маълумот - Ҳамаро навсозӣ кунед (Маълумот - Ҳамаро навсозӣ кунед).
Усули 3. Макро дар VBA
Ҳама усулҳои қаблӣ, тавре ки шумо ба осонӣ мебинед, маҳз филтр нестанд - мо сутунҳоро дар рӯйхати аслӣ пинҳон намекунем, балки ҷадвали навро бо маҷмӯи сутунҳои додашуда аз аслӣ ташкил мекунем. Агар барои филтр кардани сутунҳо (пинҳон кардани) сутунҳо дар маълумоти сарчашма зарур бошад, он гоҳ муносибати куллан дигар лозим аст, яъне макро.
Фарз мекунем, ки мо мехоҳем сутунҳоро ба таври фаврӣ филтр кунем, ки дар он номи менеҷер дар сарлавҳаи ҷадвал ниқоби дар ячейкаи зарди A4 зикршударо қонеъ мекунад, масалан, бо ҳарфи “А” оғоз мешавад (яъне “Анна” ва “Артур”-ро гиред " дар натиҷа).
Мисли усули аввал, мо аввал як қатори ёрирасонро амалӣ мекунем, ки дар он дар ҳар як ячейка меъёри мо бо формула тафтиш карда мешавад ва арзишҳои мантиқии TRUE ё FALSE барои сутунҳои намоён ва пинҳон мутаносибан нишон дода мешаванд:
Пас биёед макроси оддиро илова кунем. Дар ҷадвали варақ тугмаи ростро клик кунед ва фармонро интихоб кунед сарчашма (Рамзи манбаъ). Рамзи VBA-ро дар равзанаи кушодашуда нусхабардорӣ ва часбонед:
Варақаи кории хусусӣ_Тағйир додан(Ҳадаф аз рӯи диапазон) Агар Target.Address = "$A$4" Пас Барои Ҳар як чашмаки Дар Диапазон("D2:O2") Агар ячейка = Рост бошад, пас cell.EntireColumn.Hidden = False Дигар cell.EntireColumn.Hidden = Охири ҳақиқӣ Агар ячейкаи навбатӣ End Агар End Зер бошад Мантиқи он чунин аст:
- Умуман, ин як коркардкунандаи рӯйдод аст Варақаи корӣ_Тағйир додан, яъне ин макрос дар ҳама гуна тағирот ба ягон чашмаки варақи ҷорӣ ба таври худкор иҷро мешавад.
- Истинод ба чашмаки тағирёфта ҳамеша дар тағирёбанда хоҳад буд Ҳадаф.
- Аввалан, мо тафтиш мекунем, ки корбар маҳз чашмакро бо критерия (A4) иваз кардааст - ин аз ҷониби оператор анҷом дода мешавад. if.
- Пас аз он давра оғоз меёбад Барои ҳар… барои такрор кардани ҳуҷайраҳои хокистарӣ (D2: O2) бо арзишҳои нишондиҳандаи TRUE / FALSE барои ҳар як сутун.
- Агар арзиши ячейкаи хокистарии навбатӣ TRUE (ҳақиқӣ) бошад, он гоҳ сутун пинҳон нест, вагарна мо онро пинҳон мекунем (хосият). пинҳонӣ).
- Функсияҳои массиви динамикӣ аз Office 365: FILTER, SORT ва UNIC
- Ҷадвали ҷамъбастӣ бо сарлавҳаи бисёрсатрӣ бо истифода аз Power Query
- Макросҳо чист, чӣ гуна сохтан ва истифода бурдани онҳо