În acest articol, vom afla cum să indexați și să sortați valorile folosind coloana de asistență din Excel.
Scenariu:
Lucrând uneori cu date necunoscute. Trebuie să obținem versiunea mai scurtă, după cum este necesar, din datele din tabel, pe baza coloanei de ajutor. Mai întâi aici vom înțelege cum să obținem coloana de ajutor și apoi să obținem datele necesare mici sortate pe baza datelor din tabel.
Cum să indexați valorile în interval?
Pentru acest articol ni se va cere să folosim funcția COUNTIF. Acum vom face o formulă din funcție. Aici sunt date valori mixte de text și număr într-un interval. Trebuie să le indexăm în ordine crescătoare mai întâi numeric, apoi alfabetic.
Formula generică: -
| = COUNTIF (listă, "<=" & f_value) + (COUNT (listă) * ISTEXT (f_value)) |
listă: listă de numere și text
f_value: prima valoare a intervalului
Explicaţie:
- Funcția COUNTIF numără numărul de valori din lista returnată
- Funcția COUNT obține numărul de numere din interval.
- Funcția ISTEXT verifică dacă valoarea din celula selectată este text sau nu. Returnează TRUE pentru text și FALSE pentru numere.
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Să înțelegem cum să obținem coloana de ajutor folosind formula explicată. Aici coloana de ajutor trebuie să se bazeze pe valorile de sortare în ordine (primele numere și apoi alfabatic). Pentru aceasta vom folosi formula pentru a obține coloana Index sau rang ca coloană de ajutor pentru datele de cheltuieli prezentate mai jos.
Aici pentru a găsi valorile în interval. În scopuri de calcul, folosim domeniul numit pentru matricea fixă D5: D12 ca cheltuială.
Folosiți formula:
| = COUNTIF (cheltuială, "<=" & D5) + (COUNT (cheltuială) * ISTEXT (D5)) |
Formula arată așa cum se arată în instantaneul de mai sus. Valoarea și matricea sunt date ca referință de gamă și referință de celulă în formulă.
După cum puteți vedea, Indexul primei celule din interval se dovedește a fi 5, ceea ce înseamnă că dacă plasăm cheltuielile în ordine de sortare, Electricitate va fi plasat pe poziția a 5-a. Acum copiați formula în alte celule folosind opțiunea de tragere în jos sau folosind tasta de comandă rapidă Ctrl + D așa cum se arată mai jos pentru a obține Indexul sau rangul pentru restul valorilor.
După cum puteți vedea, acum avem o listă a indexului sau a rangului care este modul de sortare a cheltuielilor. Acum vom folosi această coloană Rank ca coloană de ajutor pentru a obține versiunea mai scurtă a tabelului având aceeași ordine în noua listă sau tabel.
Cum se sortează valorile folosind coloana de ajutor în Excel?
Numărul de sortare în Excel utilizând coloana helper trebuie să aibă numere de index și intervalul necesar pentru sortare. Coloana de ajutor poate fi orice comandă necesară. De exemplu, dacă trebuie să obțineți lista în ordine aleatorie necesară, plasați rangul corespunzător valorii în coloana index sau rang care va funcționa ca coloană de ajutor.
- Funcția INDEX
- Funcția MATCH
- Funcția ROWS
Acum vom face o formulă folosind funcțiile de mai sus. Funcția MATCH va returna indexul celui mai mic meci din interval. Funcția INDEX ia indexul rândului ca argument și returnează rezultatele necesare corespunzătoare. Această funcție găsește
Formula generică:
| = INDEX (return_array, MATCH (ROWS (relative_reference), Index, 0)) |
return_array: matrice din care se returnează valoarea
Referință relativă: generează o ordine crescătoare în formulă. Poate fi folosit cu orice matrice
Index: matrice de indexuri a tabelului
0: potrivire exactă
Explicaţie:
- ROWS (relative_reference) returnează o valoare conform lungimii formulei extinse. Dacă se aplică în prima celulă, atunci va fi una, apoi a doua și continuați până se extinde.
- Funcția MATCH se potrivește indexului cu valoarea rândului pentru a le obține în ordine crescătoare utilizând funcția ROWS.
- Funcția INDEX returnează indexul potrivit cu valoarea corespunzătoare.
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Deci, să înțelegem această formulă prin rularea ei pe exemplul prezentat mai jos. Aici am clasat datele în ordine aleatorie pentru a obține primele trei valori pe baza coloanei Index. Utilizați formula pentru a obține prima valoare a tabelului scurt necesar.
Folosiți formula:
| = INDEX (B5: B13, MATCH (RUNURI (D5: D5), D5: D13,0)) |
Formula arată așa cum se arată în instantaneul de mai sus. Matricea de valori este dată ca interval denumit și referință de celulă.
După cum puteți vedea, prima valoare este că numărul din interval apare ca „Lapte” indexul corespunzător este 1. Acum copiați formula în alte celule folosind opțiunea de tragere în jos sau folosind tasta de comandă rapidă Ctrl + D așa cum se arată mai jos pentru a obține restul valorilor.
După cum puteți vedea, valorile sunt indexate în ordine crescătoare. Am obținut toate valorile, deoarece demonstrează și că formula funcționează bine. Putem extrage valorile numerice folosind formula aceeași formulă.
După cum puteți vedea, avem valorile mai scurte și sortate din tabel folosind coloana helper care este indexată corespunzător.
Iată câteva note observaționale prezentate mai jos.
Note:
- Formula funcționează numai cu numere și text ambele.
- Formula ignoră valoarea textului în timp ce compară numerele și ignoră numerele atunci când se potrivesc valorile textului.
- Coloana de ajutor poate fi orice tip de ordine necesară a coloanei.
Sper că acest articol despre Cum să indexați și să sortați valorile cu coloana de ajutor în Excel este explicativ. Explorați aici mai multe articole despre formule de căutare în Excel. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail
Cum se utilizează funcția SUMPRODUCT în Excel: Returnează SUMA după multiplicarea valorilor în mai multe tablouri în excel.
SUMA dacă data este între : Returnează SUMA valorilor între date sau perioade date în Excel.
Suma dacă data este mai mare decât data dată: Returnează SUMA valorilor după data sau perioada dată în Excel.
2 moduri de a suma prin lună în Excel: Returnează SUMA valorilor într-o anumită lună specifică în Excel.
Cum să însumați mai multe coloane cu condiție: Returnează SUMA valorilor pe mai multe coloane cu condiție în excel
Cum se utilizează metacaracterele în Excel : Numărați celule care se potrivesc cu fraze folosind metacaracterele în excel
Articole populare
50 Comandă rapidă Excel pentru a vă crește productivitatea : Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se folosește tFuncția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.