Kako indeksirati i sortirati vrijednosti pomoću stupca pomoćnika u Excelu.

Anonim

U ovom ćemo članku naučiti kako indeksirati i sortirati vrijednosti pomoću stupca pomoćnika u Excelu.

Scenarij:

Ponekad rad s bezobzirnim podacima. Moramo dobiti kraću verziju kao potrebne podatke iz tabličnih podataka na temelju pomoćnog stupca. Ovdje ćemo prvo razumjeti kako doći do pomoćnog stupca, a zatim ćemo dobiti sortirane male potrebne podatke na temelju podataka tablice.

Kako indeksirati vrijednosti u rasponu?

Za ovaj ćemo članak morati koristiti funkciju COUNTIF. Sada ćemo od funkcije napraviti formulu. Ovdje su dati tekst i broj pomiješane vrijednosti u rasponu. Moramo ih indeksirati uzlazno, prvo numerički, a zatim abecedno.

Opća formula:-

= COUNTIF (popis, "<=" & f_value) + (COUNT (popis) * ISTEXT (f_value))

popis: popis brojeva i teksta

f_value: prva vrijednost raspona

Obrazloženje:

  1. Funkcija COUNTIF broji broj vrijednosti u vraćenom popisu
  2. Funkcija COUNT dobiva broj brojeva u rasponu.
  3. ISTEXT funkcija provjerava je li vrijednost u odabranoj ćeliji tekst ili ne. Vraća TRUE za tekst i FALSE za brojeve.

Primjer:

Sve ovo može biti zbunjujuće za razumijevanje. Shvatimo kako doći do stupca pomoćnika pomoću objašnjene formule. Ovdje se pomoćni stupac temelji na redoslijedu sortiranja vrijednosti (prvo brojevi, a zatim abecedno). Za to ćemo koristiti formulu za dobivanje stupca Indeks ili rang kao pomoćnog stupca za dolje prikazane podatke o troškovima.

Ovdje ćete pronaći vrijednosti u rasponu. Za potrebe izračuna koristimo imenovani raspon za fiksni niz D5: D12 kao trošak.

Koristite formulu:

= COUNTIF (trošak, "<=" & D5) + (COUNT (trošak) * ISTEXT (D5))


Formula izgleda kao što je prikazano na gornjoj snimci. Vrijednost i niz su dati kao imenovani raspon i referenca ćelije u formuli.


Kao što vidite, indeks prve ćelije u rasponu iznosi 5, što znači da ako rashode rasporedimo prema redoslijedu sortiranja, Struja bit će postavljeni na peto mjesto. Sada kopirajte formulu u druge ćelije pomoću opcije povlačenja prema dolje ili pomoću tipke prečaca Ctrl + D kako je prikazano u nastavku kako biste dobili indeks ili rang za ostale vrijednosti.

Kao što vidite, sada imamo popis indeksa ili ranga koji je način razvrstavanja troškova. Sada ćemo ovaj stupac Ranga koristiti kao pomoćni stupac kako bismo dobili kraću verziju tablice s istim redoslijedom na novom popisu ili tablici.

Kako sortirati vrijednosti pomoću pomoćnog stupca u Excelu?

Broj za sortiranje u Excelu pomoću pomoćnog stupca mora imati indeksne brojeve i potreban raspon za sortiranje. Stupac pomoćnik može biti bilo koja potrebna narudžba. Na primjer, ako trebate popis staviti u traženi slučajni redoslijed, samo postavite rang koji odgovara vrijednosti u indeks ili stupac ranga koji će raditi kao pomoćni stupac.

  1. INDEX funkcija
  2. MATCH funkcija
  3. ROWS funkcija

Sada ćemo napraviti formulu koristeći gornje funkcije. MATCH funkcija će vratiti indeks najnižeg podudaranja iz raspona. Funkcija INDEX uzima indeks retka kao argument i vraća odgovarajuće potrebne rezultate. Ova funkcija pronalazi

Opća formula:

= INDEX (return_array, MATCH (ROWS (relativna_referencija), Index, 0))

return_array: niz iz kojeg se vraća vrijednost

Relativna referenca: generira rastući redoslijed u formuli. Može se koristiti s bilo kojim nizom

Indeks: Indeksni niz tablice

0: točno podudaranje

Obrazloženje:

  1. ROWS (relative_reference) vraća vrijednost prema duljini proširene formule. Ako se primijeni u prvoj ćeliji, bit će jedna, zatim druga i nastavi dok se ne proširi.
  2. Funkcija MATCH podudara se s indeksom s vrijednošću retka kako bi ih se postavilo uzlaznim redoslijedom pomoću funkcije ROWS.
  3. INDEX funkcija vraća usklađeni Indeks s odgovarajućom vrijednošću.

Primjer:

Sve ovo može biti zbunjujuće za razumijevanje. Dakle, shvatimo ovu formulu tako što ćemo je pokrenuti na dolje prikazanom primjeru. Ovdje smo rangirali podatke nasumičnim redoslijedom kako bismo dobili prve tri vrijednosti na temelju stupca Indeks. Koristite formulu da biste dobili prvu vrijednost potrebne kratke tablice.

Koristite formulu:

= INDEKS (B5: B13, MATCH (REDOVI (D5: D5), D5: D13,0))

Formula izgleda kao što je prikazano na gornjoj snimci. Niz vrijednosti dan je kao imenovani raspon i referenca ćelije.

Kao što vidite, prva vrijednost je broj iz raspona za koji izlazi da je "Mlijeko" odgovarajući indeks je 1. Sada kopirajte formulu u druge ćelije pomoću opcije povlačenja prema dolje ili pomoću tipke prečaca Ctrl + D kako je prikazano u nastavku da biste dobili ostale vrijednosti.

Kao što vidite, vrijednosti su indeksirane uzlaznim redoslijedom. Dobili smo sve vrijednosti jer također dokazuje da formula radi dobro. Možemo izdvojiti vrijednosti brojeva koristeći formulu po istoj formuli.

Kao što vidite, imamo kraće i razvrstane vrijednosti iz tablice pomoću stupca pomoćnika koji je prema tome indeksiran.

Evo nekoliko opservacijskih napomena prikazanih u nastavku.

Bilješke:

  1. Formula radi samo s brojevima i s tekstom.
  2. Formula zanemaruje tekstualnu vrijednost tijekom usporedbe brojeva i zanemaruje brojeve kada se podudaraju s tekstualnim vrijednostima.
  3. Stupac pomoćnik može biti bilo koja vrsta potrebnog redoslijeda stupaca.

Nadamo se da je ovaj članak o tome kako indeksirati i sortirati vrijednosti pomoću stupca pomoćnika u Excelu objašnjen. Ovdje istražite više članaka o formulama za pretraživanje u Excelu. Ako vam se sviđaju naši blogovi, podijelite ih sa svojim prijateljima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku. Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte

Kako koristiti funkciju SUMPRODUCT u Excelu: Vraća SUM nakon množenja vrijednosti u više niza u Excelu.

ZBIRAJTE ako je datum između : Vraća ZBOR vrijednosti između zadanih datuma ili razdoblja u Excelu.

Zbroj ako je datum veći od navedenog datuma: Vraća ZBOR vrijednosti nakon zadanog datuma ili razdoblja u Excelu.

2 načina zbrajanja po mjesecima u Excelu: Vraća ZBOR vrijednosti unutar određenog određenog mjeseca u Excelu.

Kako zbrojiti više stupaca s uvjetom: Vraća ZBOR vrijednosti u više stupaca sa uvjetom u excelu

Kako koristiti zamjenske znakove u Excelu : Brojanje ćelija koje se podudaraju s izrazima pomoću zamjenskih znakova u Excelu

Popularni članci

50 Excel prečac za povećanje vaše produktivnosti : Brže izvršite svoj zadatak. Ovih 50 prečaca učinit će da radite još brže na Excelu.

Kako koristiti tFunkcija VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.

Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.

Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.