Kana usiri mushandisi wemanovice, saka unofanirwa kunge watoona kuti 99% yezvese zviri muExcel yakagadzirirwa kushanda nematafura akatwasuka, uko paramita kana hunhu (minda) inopfuura nepakati pemakoramu, uye ruzivo rwezvinhu kana zviitiko zviripo. mumitsetse . Pivot matafura, subtotals, kukopa mafomula nekudzvanya kaviri - zvese zvakagadzirirwa zvakanangana neiyo data fomati.
Nekudaro, hapana mitemo isina kusarudzika uye neyakajairwa frequency ndinobvunzwa zvekuita kana tafura ine yakachinjika semantic orientation, kana tafura ine mitsara nemakoramu ane huremu hwakafanana mukureva, yakasangana mubasa:
Uye kana Excel ichiri kuziva kugadzirisa zvakachinjika (nemurairo Data - Ronga - Sarudzo - Ronga makoramu), ipapo mamiriro ekusefa akashata - hapangori maturusi akavakirwa-mukati ekusefa makoramu, kwete mitsara muExcel. Saka, kana iwe wakatarisana nebasa rakadaro, iwe uchafanirwa kuuya nemaworkaround eakasiyana madhigirii ekuoma.
Nzira 1. Basa idzva reFILTER
Kana iwe uri paiyo vhezheni itsva yeExcel 2021 kana kunyoreswa kweExcel 365, unogona kutora mukana weiyo ichangobva kuunzwa chimiro. FILTER (SEFA), iyo inogona kusefa iyo source data kwete chete nemitsara, asiwo nemakoramu. Kuti ushande, basa iri rinoda yekubatsira yakachinjika imwe-dimensional array-row, apo kukosha kwega kwega (CHOKWADI kana FALSE) kunotarisisa kana isu tichiratidza kana, ukuwo, kuviga koramu inotevera mutafura.
Ngatiwedzerei mutsara unotevera pamusoro petafura yedu uye tinyore mamiriro ekoramu yega yega mairi:
- Ngatitii tinogara tichida kuratidza makoramu ekutanga uye ekupedzisira (misoro uye matomu), saka kwavari mumasero ekutanga uye ekupedzisira ehurongwa tinoisa kukosha = TRUE.
- Pamakoramu asara, zviri mukati memasero anoenderana zvichave fomula inoongorora mamiriro atinoda tichishandisa mabasa И (UYE) or OR (KANA). Semuenzaniso, iyo yakazara iri muhuwandu kubva pa300 kusvika ku500.
Mushure meizvozvo, zvinongosara chete kushandisa basa FILTER kuti usarudze makoramu ari pamusoro ayo ratidzo yedu yebetsero ine TRUE kukosha:
Saizvozvo, unogona kusefa makoramu nerunyoro rwakapihwa. Muchiitiko ichi, basa richabatsira COUNTIF (COUNTIF), iyo inotarisa nhamba yezvinoitika zvezita rinotevera rekoramu kubva kumusoro wetafura mune yakabvumidzwa runyorwa:
Nzira 2. Pivot tafura panzvimbo yenguva dzose
Parizvino, Excel yakavaka-mukati yakachinjika kusefa nemakoramu chete mumatafura epivot, saka kana tikakwanisa kushandura tafura yedu yepakutanga kuita tafura yepivot, tinogona kushandisa iyi yakavakirwa-mukati. Kuti tiite izvi, yedu sosi tafura inofanirwa kuzadzisa zvinotevera mamiriro:
- iva ne "chaiyo" mutsara wemusoro wemutsara usina masero asina chinhu uye akabatanidzwa - zvikasadaro hazvishande kuvaka tafura yepivot;
- usave neduplicates mumalabel emitsara nemakoramu - iwo "achapunzika" muchidimbu kuita runyorwa rwezvakasarudzika maitiro;
- ine nhamba chete muhuwandu hwehuwandu (pamharadzano yemitsara nemakoramu), nekuti tafura yepivot ichanyatso shandisa imwe mhando yekuunganidza basa kwavari (sum, avhareji, nezvimwewo) uye izvi hazvishande nemavara.
Kana ese aya mamiriro akazadzikiswa, saka kuitira kuvaka tafura yepivot inoratidzika setafura yedu yepakutanga, iyo (iyo yekutanga) inoda kuwedzerwa kubva pamuchinjiko kuenda kune yakati sandara (yakajairwa). Uye nzira iri nyore yekuita izvi ndeye Power Query yekuwedzera-mukati, ine simba data shanduko chishandiso chakavakirwa muExcel kubvira 2016.
Izvi ndezvi:
- Ngatishandure tafura kuita "smart" dynamic command Kumba – Fomati setafura (Kumba - Fomati seTafura).
- Kurodha muPower Query nemurairo Dhata - Kubva Tafura / Range (Data - Kubva Tafura / Range).
- Isu tinosefa mutsara nehwerengedzo (iyo pfupiso ichava neyayo yakazara).
- Tinya kurudyi pane yekutanga koramu musoro wosarudza Bvisa mamwe makoramu (Upivot Mamwe Makoramu). Makoramu ese asina kusarudzwa anoshandurwa kuita maviri - zita remushandi uye kukosha kwechiratidzo chake.
- Kusefa koramu nehwerengedzo dzakapinda mukoramu Paunhu.
- Isu tinovaka tafura yepivot zvinoenderana neinokonzeresa flat (yakajairika) tafura ine rairo Imba - Vhara uye Rodha - Vhara uye Rodha mukati... (Kumba - Vhara & Rodha - Vhara & Rodha ku…).
Iye zvino unogona kushandisa kugona kusefa makoramu anowanikwa mumatafura epivot - akajairika macheki pamberi pemazita uye zvinhu. Siginicha Mafirita (Label Masefa) or Sefa nemutengo (Value Filters):
Uye zvechokwadi, kana uchichinja iyo data, iwe unozofanirwa kugadzirisa mubvunzo wedu uye pfupiso nekapfupi keyboard. Ctrl+alt+F5 kana timu Dhata - Zorora Zvese (Data - Zorora Zvese).
Nzira 3. Macro muVBA
Nzira dzese dzakapfuura, sezvaunogona kuona zviri nyore, hadzisi kunyatsosefa - hativanze makoramu mune yekutanga runyorwa, asi gadzira tafura nyowani ine yakapihwa seti yemakoramu kubva kune yekutanga. Kana zvichidikanwa kusefa (kuvanza) makoramu mune sosi data, saka nzira yakasiyana inodiwa, kureva, macro.
Ngatitii tinoda kusefa makoramu panhunzi apo zita remaneja mumusoro wetafura rinogutsa mask inotsanangurwa muyero sero A4, semuenzaniso, inotanga nebhii "A" (kureva, tora "Anna" uye "Arthur. " semagumo).
Senzira yekutanga, isu tinotanga tashandisa mutsara wekubatsira, apo musero rega rega chiyero chedu chinotariswa neformula uye hunhu hunonzwisisika TRUE kana FALSE hucharatidzwa makoramu anooneka uye akavanzika, zvichiteerana:
Zvadaro ngatiwedzerei macro iri nyore. Tinya-kurudyi pane pepa tab uye sarudza kuraira mabviro (Source code). Kopa uye unamate inotevera VBA kodhi muhwindo rinovhura:
Private Sub Worksheet_Change(ByVal Target As Range) Kana Target.Address = "$A$4" Then Yesero yega yega In Range("D2:O2") Kana sero = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Chokwadi Kupera Kana Inotevera sero Kupera Kana Kupera Sub logic yayo ndeiyi:
- Kazhinji, uyu mugadziri wezviitiko Shanduko_Yekushanda, kureva kuti iyi macro inongomhanya pane chero shanduko kune chero sero pane shizha razvino.
- Chirevo chesero chakashandurwa chichagara chiri mushanduko chipfuro.
- Kutanga, tinotarisa kuti mushandisi achinja chaizvo sero ine chirevo (A4) - izvi zvinoitwa nemushandisi. if.
- Ipapo kutenderera kunotanga Kune Imwe neimwe… kudzokorodza pamusoro pegrey maseru (D2: O2) ine TRUE / FALSE chiratidzo chemhando dzekoramu yega yega.
- Kana kukosha kweiyo grey sero inotevera TRUE (yechokwadi), saka iyo column haina kuvanzwa, kana zvisina kudaro tinoiviga (pfuma yakavanzwa).
- Dynamic array inoshanda kubva kuHofisi 365: FILTER, SORT, uye UNIC
- Pivot tafura ine multiline musoro uchishandisa Power Query
- Chii chinonzi macros, kugadzira uye kuishandisa