Toți sunt mulțumiți de mesele pivot - ele sunt considerate rapide și sunt ajustate flexibil, iar designul poate fi înșurubat în ele elegante, dacă este necesar. Dar există câteva linguri de gudron, în special, incapacitatea de a crea o consolidată, unde în valori nu ar trebui să existe text, ci numere.
Să încercăm să lucreze în jurul valorii de această limitare și de a veni cu „o pereche de cârje“, într-o astfel de situație.
Să presupunem că compania noastră își comercializează produsele în mai multe orașe din Rusia și Kazahstan. Containerele sunt trimise nu mai mult de o dată pe lună. Fiecare recipient are un număr alfanumeric. Ca date sursă există un tabel standard cu o listă de livrări, din care este necesar să se facă un fel de rezumat pentru a vedea vizual numărul containerelor trimise fiecărui oraș și în fiecare lună:
Pentru comoditate, hai să facem un tabel cu date originale "inteligente" în avans folosind comanda "Acasă - Format ca tabel" și să îi dăm numele fișierului Livrare în fila Design. În viitor, acest lucru va simplifica viața, pentru că Puteți folosi numele tabelului și coloanelor sale direct în formule.
Metoda 1. Cea mai ușoară - folosim Power Query
Dacă nu puteți utiliza interogările de putere, puteți merge în alte moduri - prin intermediul unui tabel de sinteză sau formula.
Metoda 2: Rezumat auxiliar
Adăugați în tabelul sursă o coloană suplimentară, unde folosind o formulă simplă, calculați numărul fiecărui rând din tabel:
Evident, este necesar -1, pentru că avem în tabel un antet cu o singură linie. În cazul în care masa ta nu se află la începutul foii, puteți utiliza o formulă puțin mai complicat, dar un universal, care calculează diferența în numărul rândului curent și antetul de tabel:
Acum, construim un tabel rezumat al formularului dorit pe baza datelor noastre în mod standard, dar în câmpul de valori vom umple câmpul Număr linie în loc de containerul necesar.
Deoarece nu avem mai multe containere în același oraș în aceeași lună, voința noastră consolidată nu este de fapt suma, ci numărul de containere de care avem nevoie.
În plus, puteți dezactiva partajat și subtotaluri în fila Design - totaluri generale și subtotaluri (Design - Grand Totaluri, Subtotal) și se trece la un tabel rezumat mai convenabil butonul Raport de aspect Layout (Aspect Raport).
Deci, suntem la jumătatea drumului spre rezultat: avem un tabel în cazul în care la intersecția orașului și luna în cazul în care numărul de linie din tabela sursă, ceea ce este necesar pentru a ne codul containerului.
Acum copiem rezumatul (pe aceeași foaie sau pe celălalt) și îl lipiți ca valori și apoi vom introduce formula noastră în zona de valoare, care va extrage codul containerului cu numărul de linie găsit în rezumat:
Dacă funcția IF. în acest caz, verifică dacă celula următoare din rezumat nu este goală. Dacă este gol, atunci emise un șir de text gol "", adică, părăsiți celula necompletată. Dacă nu este gol, atunci extragem conținutul celulei cu numărul liniei din coloana INDEX folosind Containerul din tabela de livrare originală.
Poate că singurul moment nu este foarte evident aici este cuvântul restante Container în formula. O astfel de formă ciudată de scriere:
În viitor, când modificați datele din tabelul de alimentare inițial. trebuie să vă amintiți să actualizați rezumatul nostru auxiliar cu numerele de linie făcând clic dreapta pe el și selectând Refresh.
Metoda 3: Formulele
Această metodă nu necesită crearea unui tabel pivot intermediar și actualizările manuale, precum și utilizarea de „arme grele Excel“ - funcția SUMIFS (SUMIFS). În loc să căutați numere de linii în rezumat, le puteți calcula utilizând următoarea formulă:
Cu unele greutăți externe, de fapt, aceasta este versiunea standard a utilizării funcției de sumare selectivă a REZUMATULUI. care rezumă numerele de linie pentru un anumit oraș și o lună. Din nou, din moment ce nu sunt mai multe recipiente în același oraș în aceeași lună, funcția noastră va fi, de fapt, nu suma, și numărul liniei. Apoi, deja cunoscut prin metoda funcției precedente INDEX poate extrage și coduri de containere:
În sine, în acest caz, nu mai trebuie să vă gândiți la actualizarea rezumatului, dar pe mesele mari, funcția SUMMER poate încetini semnificativ. Apoi, trebuie să dezactivați actualizarea automată a formulelor sau să utilizați prima metodă - un tabel rezumativ.
În cazul în care apariția rezumatului nu sunt foarte potrivite pentru înregistrare, este posibil să se retragă din numerele sale de linie la masa finală nu este în mod direct, așa cum am făcut și cu funcția GETPIVOTDATA (GET.PIVOT.DATA). Cum puteți face acest lucru puteți vedea aici.
Link-uri conexe
Articole similare
Trimiteți-le prietenilor: