Мазмұны
Егер сіз жаңа пайдаланушы болмасаңыз, Excel бағдарламасындағы барлық нәрсенің 99% параметрлері немесе атрибуттары (өрістері) бағандар арқылы өтетін және нысандар немесе оқиғалар туралы ақпарат орналасқан тік кестелермен жұмыс істеуге арналғанын байқаған боларсыз. жолдарда. Жиынтық кестелер, аралық қорытындылар, екі рет басу арқылы формулаларды көшіру – барлығы осы деректер пішімі үшін арнайы жасалған.
Дегенмен, ерекшеліксіз ережелер жоқ және жеткілікті тұрақты жиілікпен жұмыста көлденең семантикалық бағдары бар кесте немесе жолдар мен бағандардың мағынасы бірдей болатын кесте кездессе не істеу керектігін сұрайды:
Ал егер Excel әлі де көлденең сұрыптауды білсе (пәрменімен Деректер – Сұрыптау – Параметрлер – Бағандарды сұрыптау), онда сүзу жағдайы нашарлайды – Excel бағдарламасында жолдарды емес, бағандарды сүзуге арналған кірістірілген құралдар жоқ. Сонымен, егер сіз осындай тапсырмаға тап болсаңыз, сізге әртүрлі күрделілік дәрежесіндегі уақытша шешімдерді табуға тура келеді.
1-әдіс. Жаңа FILTER функциясы
Excel 2021 бағдарламасының жаңа нұсқасын немесе Excel 365 жазылымын пайдалансаңыз, жаңадан енгізілген мүмкіндікті пайдалана аласыз. СҮЗГІ (СҮЗГІ), ол бастапқы деректерді жолдар бойынша ғана емес, сонымен қатар бағандар бойынша да сүзе алады. Бұл функция жұмыс істеу үшін қосалқы көлденең бір өлшемді жиым-жолды қажет етеді, мұнда әрбір мән (TRUE немесе FALSE) кестедегі келесі бағанды көрсету немесе жасыру керектігін анықтайды.
Кестенің үстіне келесі жолды қосып, оған әрбір бағанның күйін жазайық:
- Біз әрқашан бірінші және соңғы бағандарды (тақырыптар мен қорытындылар) көрсетуді қалаймыз делік, сондықтан олар үшін массивтің бірінші және соңғы ұяшықтарында = TRUE мәнін орнатамыз.
- Қалған бағандар үшін сәйкес ұяшықтардың мазмұны функциялар арқылы бізге қажет жағдайды тексеретін формула болады. И (ЖӘНЕ) or OR (OR). Мысалы, жиынтық 300-ден 500-ге дейінгі аралықта.
Осыдан кейін функцияны пайдалану ғана қалады СҮЗГІ Көмекші массивімізде TRUE мәні бар бағандарды таңдау үшін:
Сол сияқты, берілген тізім бойынша бағандарды сүзуге болады. Бұл жағдайда функция көмектеседі COUNTIF (COUNTIF), ол рұқсат етілген тізімдегі кесте тақырыбынан келесі баған атауының қайталану санын тексереді:
2-әдіс. Әдеттегі кестенің орнына жиынтық кесте
Қазіргі уақытта Excel бағдарламасында тек жиынтық кестелердегі бағандар бойынша ендірілген көлденең сүзгілеу бар, сондықтан бастапқы кестемізді жиынтық кестеге түрлендіре алсақ, біз бұл кірістірілген функцияны пайдалана аламыз. Ол үшін біздің бастапқы кесте келесі шарттарды қанағаттандыруы керек:
- бос және біріктірілген ұяшықтарсыз «дұрыс» бір жолды тақырып сызығына ие болыңыз – әйтпесе жиынтық кестені құру жұмыс істемейді;
- жолдар мен бағандардың белгілерінде көшірмелерді қамтымаңыз – олар жиынтықта тек бірегей мәндер тізіміне «жығылады»;
- мәндер ауқымындағы сандарды ғана қамтиды (жолдар мен бағандардың қиылысында), өйткені жиынтық кесте оларға міндетті түрде қандай да бір жинақтау функциясын қолданады (қосынды, орташа және т.б.) және бұл мәтінмен жұмыс істемейді.
Егер осы шарттардың барлығы орындалса, бастапқы кестеге ұқсайтын жиынтық кестені құру үшін оны (түпнұсқа) айқас кестеден жалпақ кестеге (қалыпты) кеңейту қажет болады. Мұны істеудің ең оңай жолы - 2016 жылдан бері Excel бағдарламасына енгізілген қуатты деректерді түрлендіру құралы Power Query қондырмасы.
Бұлар:
- Кестені «ақылды» динамикалық пәрменге түрлендірейік Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу).
- Пәрмен арқылы Power Query қолданбасына жүктелуде Деректер – Кестеден / Ауқымнан (Деректер – Кестеден / Ауқымнан).
- Біз жолды қорытындыларымен сүземіз (қорытындының өз қорытындылары болады).
- Бірінші баған тақырыбын тінтуірдің оң жақ түймешігімен басып, таңдаңыз Басқа бағандарды тасалау (Басқа бағандарды ашу). Барлық таңдалмаған бағандар екіге түрленеді – қызметкердің аты-жөні және оның көрсеткішінің мәні.
- Бағанды бағанға кірген қорытындылармен сүзу атрибуттары.
- Пәрмен арқылы алынған жазық (нормаланған) кестеге сәйкес жиынтық кестені құрастырамыз Негізгі бет — Жабу және жүктеу — Жабу және жүктеу… (Басты бет — Жабу және жүктеу — Жабу және жүктеп салу...).
Енді сіз жиынтық кестелерде қол жетімді бағандарды сүзу мүмкіндігін пайдалана аласыз - атаулар мен элементтердің алдындағы әдеттегі құсбелгілер Қолтаңба сүзгілері (Жапсырма сүзгілері) or Мән бойынша сүзеді (Мән сүзгілері):
Және, әрине, деректерді өзгерткен кезде, сіз біздің сұрауымызды және қорытындыны пернелер тіркесімі арқылы жаңартуыңыз керек. Ctrl+Alt+F5 немесе команда Деректер – Барлығын жаңарту (Деректер — Барлығын жаңарту).
3-әдіс. VBA жүйесіндегі макрос
Көріп отырғаныңыздай, алдыңғы әдістердің барлығы дәл сүзгіден өткізілмейді – біз бастапқы тізімдегі бағандарды жасырмаймыз, бірақ түпнұсқадан берілген бағандар жиынтығымен жаңа кестені құрамыз. Егер бастапқы деректердегі бағандарды сүзу (жасыру) қажет болса, онда түбегейлі басқа тәсіл қажет, атап айтқанда, макрос.
Кесте тақырыбындағы менеджердің аты сары A4 ұяшығында көрсетілген масканы қанағаттандыратын, мысалы, «А» әрпінен басталатын бағандарды жылдам сүзгіміз келеді делік (яғни, «Анна» мен «Артурды алыңыз» « болғандықтан).
Бірінші әдістегідей, біз алдымен көмекші диапазон жолын орындаймыз, мұнда әрбір ұяшықта біздің критерий формула арқылы тексеріледі және көрінетін және жасырын бағандар үшін сәйкесінше TRUE немесе FALSE логикалық мәндері көрсетіледі:
Содан кейін қарапайым макросты қосамыз. Парақ қойындысын тінтуірдің оң жақ түймешігімен басып, пәрменді таңдаңыз қайнар көз (бастапқы код). Келесі VBA кодын көшіріп, ашылатын терезеге қойыңыз:
Private Sub Worksheet_Change(ByVal Target As Range) Егер Target.Address = "$A$4" болса, онда Ауқымдағы әрбір ұяшық үшін("D2:O2") Егер ұяшық = True болса, онда cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next ұяшық End If End Sub Оның логикасы келесідей:
- Жалпы, бұл оқиға өңдеушісі Жұмыс парағы_Өзгерту, яғни бұл макрос ағымдағы парақтағы кез келген ұяшыққа жасалған кез келген өзгерісте автоматты түрде іске қосылады.
- Өзгертілген ұяшыққа сілтеме әрқашан айнымалыда болады нысана.
- Біріншіден, пайдаланушының ұяшықты (A4) шартымен дәл өзгерткенін тексереміз – мұны оператор жасайды. if.
- Содан кейін цикл басталады Әрқайсысы үшін… әр баған үшін TRUE/FALSE индикатор мәндері бар сұр ұяшықтарды (D2:O2) қайталау үшін.
- Келесі сұр ұяшықтың мәні TRUE (шын) болса, онда баған жасырылмайды, әйтпесе оны жасырамыз (қасиет жасырын).
- Office 365 жүйесіндегі динамикалық жиым функциялары: СҮЗГІ, СҰРЫПТАУ және UNIC
- Power Query көмегімен көп жолды тақырыбы бар жиынтық кесте
- Макростар дегеніміз не, оларды құру және пайдалану жолы