Turinys
Pivot lentelės tinka visiems – jos greitai skaičiuoja, yra lanksčiai konfigūruojamos, o esant poreikiui į jas galima elegantiškai suvynioti dizainą. Tačiau taip pat yra keletas gudrybių, ypač nesugebėjimas sukurti suvestinės, kur vertės srityje turėtų būti ne skaičiai, o tekstas.
Pabandykime apeiti šį apribojimą ir panašioje situacijoje sugalvokime „porą ramentų“.
Tarkime, mūsų įmonė veža savo produkciją konteineriuose į kelis mūsų šalies ir Kazachstano miestus. Konteineriai siunčiami ne dažniau kaip kartą per mėnesį. Kiekvienas konteineris turi raidinį ir skaitmeninį numerį. Kaip pirminiai duomenys yra standartinė pristatymų lentelė, iš kurios reikia padaryti kažkokią suvestinę, kad aiškiai matytumėte į kiekvieną miestą ir kiekvieną mėnesį išsiųstų konteinerių skaičių:
Patogumui lentelę su pradiniais duomenimis iš anksto paverskime „protinga“ naudodami komandą Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę) ir duok jai vardą Pristatymas kortelė Konstruktorius (Dizainas). Ateityje tai supaprastins gyvenimą, nes. lentelėse ir jos stulpelių pavadinimą bus galima naudoti tiesiogiai formulėse.
1 būdas. Paprasčiausias – naudoti Power Query
„Power Query“ yra ypač galingas įrankis duomenims įkelti ir transformuoti programoje „Excel“. Šis priedas buvo įtrauktas į „Excel“ pagal numatytuosius nustatymus nuo 2016 m. Jei turite „Excel 2010“ arba „Excel 2013“, galite jį atsisiųsti ir įdiegti atskirai (visiškai nemokamai).
Visą procesą, siekiant aiškumo, žingsnis po žingsnio išanalizavau šiame vaizdo įraše:
Jei neįmanoma naudoti „Power Query“, galite eiti kitais būdais – per suvestinę lentelę arba formules.
2 metodas. Pagalbinė santrauka
Į pradinę lentelę įtraukime dar vieną stulpelį, kuriame pagal paprastą formulę apskaičiuojame kiekvienos lentelės eilutės skaičių:
Akivaizdu, kad reikia -1, nes mūsų lentelėje yra vienos eilutės antraštė. Jei jūsų lentelė nėra lapo pradžioje, galite naudoti šiek tiek sudėtingesnę, bet universalią formulę, kuri apskaičiuoja esamos eilutės ir lentelės antraštės skaičių skirtumą:
Dabar pagal savo duomenis standartiniu būdu sukursime norimo tipo suvestinę lentelę, tačiau vertės lauke lauką atmessime Linijos numeris vietoj to, ko norime konteineris:
Kadangi tą patį mėnesį tame pačiame mieste neturime kelių konteinerių, mūsų suvestinėje iš tikrųjų bus nurodytas ne kiekis, o mums reikalingų konteinerių eilučių numeriai.
Be to, skirtuke galite išjungti didžiąsias ir tarpines sumas Konstruktorius – bendrosios sumos и Tarpinės sumos (Dizainas – bendrosios sumos, tarpinės sumos) ir toje pačioje vietoje mygtuku perjungti suvestinę į patogesnį lentelės išdėstymą Pranešimo maketas (Ataskaitos išdėstymas).
Taigi, mes jau esame pusiaukelėje iki rezultato: turime lentelę, kurioje miesto ir mėnesio sankirtoje šaltinio lentelėje yra eilutės numeris, kuriame yra mums reikalingas konteinerio kodas.
Dabar nukopijuokime suvestinę (į tą patį lapą ar kitą) ir įklijuokime ją kaip reikšmes, o tada įveskite formulę į reikšmių sritį, kuri ištrauks konteinerio kodą pagal suvestinėje rastą eilutės numerį:
Funkcija IF (JEI), šiuo atveju patikrina, ar kitas suvestinės langelis nėra tuščias. Jei tuščia, tada išveskite tuščią teksto eilutę „“, ty palikite langelį tuščią. Jei ne tuščias, tada ištraukite iš stulpelio Konteineris šaltinio lentelė Pristatymas langelio turinys pagal eilutės numerį naudojant funkciją RODYKLĖ (INDEKSAS).
Galbūt vienintelis nelabai akivaizdus dalykas čia yra dvigubas žodis Konteineris formulėje. Tokia keista rašymo forma:
reikmenys[[Konteineris]:[Konteineris]]
… reikalingas tik nuorodai į stulpelį Konteineris buvo absoliutus (kaip nuoroda su $ ženklais įprastoms „neprotingoms“ lentelėms) ir nenuslydo į gretimus stulpelius kopijuojant formulę į dešinę.
Ateityje keičiant duomenis šaltinio lentelėje Pristatymas, turime nepamiršti atnaujinti savo pagalbinės santraukos eilučių numeriais, spustelėdami ją dešiniuoju pelės klavišu ir pasirinkdami komandą Atnaujinkite ir išsaugokite (Atnaujinti).
3 metodas. Formulės
Šis metodas nereikalauja tarpinės suvestinės lentelės kūrimo ir rankinio atnaujinimo, bet naudoja „Excel“ „sunkųjį ginklą“ – funkciją SUMMESLIMN (SUMIFS). Užuot ieškoję eilučių numerių suvestinėje, galite juos apskaičiuoti naudodami šią formulę:
Iš tikrųjų tai yra įprastas selektyvaus sumavimo funkcijos naudojimo atvejis dėl tam tikro išorinio dydžio SUMMESLIMNA, kuris susumuoja nurodyto miesto ir mėnesio eilučių numerius. Vėlgi, kadangi mes neturime kelių konteinerių tame pačiame mieste tą patį mėnesį, mūsų funkcija iš tikrųjų nurodys ne sumą, o patį eilutės numerį. Ir tada funkcija, jau pažįstama iš ankstesnio metodo RODYKLĖ Taip pat galite išgauti konteinerio kodus:
Žinoma, tokiu atveju nebereikia galvoti apie santraukos atnaujinimą, bet didelėse lentelėse funkcija SUMMESLI gali būti pastebimai lėtas. Tada turėsite išjungti automatinį formulių atnaujinimą arba naudoti pirmąjį metodą – pivot lentelę.
Jei suvestinės išvaizda nėra labai tinkama jūsų ataskaitai, galite iš jos išgauti eilučių numerius į galutinę lentelę ne tiesiogiai, kaip tai padarėme, bet naudodami funkciją GET.PIVOT.TABLE.DATA (GET.PIVOT.DUOMENYS). Kaip tai padaryti, rasite čia.
- Kaip sukurti ataskaitą naudojant suvestinę lentelę
- Kaip nustatyti skaičiavimus suvestinėse lentelėse
- Atrankinis skaičiavimas naudojant SUMIFS, COUNTIFS ir kt.