See artikkel räägib sellest, kuidas tabelist kõik väärtused sobitada ja need erinevatesse lahtritesse hankida. See on sarnane mitme väärtuse otsimisega.
Üldine valem
{= INDEX (nimed, VÄIKESED (IF (rühmad = grupi_nimi, RIDA (nimed) -MIN (RIDA (nimed))+1), VEERGAD (laiendavad vahemikud))), „--Listade lõpp-”)}
Liiga palju funktsioone ja muutujaid !!!. Vaatame, mis need muutujad on.
Nimed: See on nimede loend.
Grupid: Nende rühmade loend, kuhu need nimed kuuluvad.
Grupi nimi: rühma nime viide.
Vahemike laiendamine: see on laienev vahemik, mida kasutatakse paremale kopeerimisel suureneva arvu saamiseks.
Näide: eraldage töötajate nimed erinevatesse veergudesse vastavalt ettevõttele.
Oletame, et teil on töötajate tabel, mis on rühmitatud nende ettevõtte järgi. Esimene veerg sisaldab töötajate nimesid ja teine veerg ettevõtte nime.
Nüüd on meil vaja saada iga töötaja nimi erinevatesse veergudesse vastavalt ettevõttele. Teisisõnu, me peame need rühmitama.
Siin olen nimetanud A2: A10 töötajaks ja B2: B10 ettevõtteks, nii et valemit on lihtne lugeda.
Kirjutage see massiivivalem lahtrisse F2. Selle valemi sisestamiseks kasutage klahve CTRL+SHIFT+ENTER.
{= INDEX (töötaja, väike) )}
Kopeerige see valem kõikidesse lahtritesse. See eraldab iga üksiku nime erinevatest veergudest vastavalt nende rühmale.
Nagu ülaltoodud pildilt näha, on iga töötaja erinevates lahtrites eraldatud.
Niisiis, kuidas see valem töötab?
Valemi mõistmiseks vaatame G2 valemit
Mis on = IFERROR (INDEX (Töötaja, VÄIKE (KUI (Ettevõte = $ E3, ROW (Töötaja) -MIN (ROW (Töötaja))+1), VEERGAD ($ E $ 1: F2))), "-Loendi lõpp-")
Mehaanika on lihtne ja peaaegu sama mis mitu VLOOKUP valemit. Trikk on see, et saada iga töötaja indeksinumber erinevatest rühmadest ja edastada see valemile INDEX. Seda tehakse valemi selle osa abil.
IF (Ettevõte=E3 dollarit, ROW (Töötaja) -MIN (ROW (Töötaja))+1):
See osa tagastab massiivi indekseid ja vale ettevõtte nime $ E3, mis sisaldab "Rankwatch".
{FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Kuidas? Lammutame selle seestpoolt.
Siin sobitame ettevõtte nime $ E3 iga väärtusega Ettevõtte vahemik (ettevõte = $ E3).
See tagastab hulga tõeseid ja valesid. {FALSE;TÕSI; VALE;TÕSI; FALSE; FALSE;TÕSI; VALE;TÕSI}.
Nüüd käivitab funktsioon IF selle TRUE avaldiste jaoks TRUE, mis on ROW (töötaja) -MIN (ROW (Töötaja))+1. See osa tagastab selle osa tagastab indeksite massiivi alates 1 kuni arvuni töötajad {1; 2; 3; 4; 5; 6; 7; 8; 9}. Funktsioon if näitab ainult TRUE väärtusi, mis omakorda annab meile väärtuse {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Praegune valem on lihtsustatud
= IFERROR (INDEX (Töötaja, VÄIKE ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},VEERGAD ($ E $ 1: F2))), "-loendi lõpp-"). Nagu me teame, tagastab väike funktsioon massiivist n. Väikseima väärtuse. VEERGUD ($ 1 $: F2) see tagastab 2. Funktsioon SMALL tagastab ülaltoodud massiivi teise väikseima väärtuse, mis on 4.
Nüüd on valem lihtsustatud = IFERROR (INDEX (töötaja, 4), "-loendi lõpp-"). Nüüd tagastab funktsioon INDEX lihtsalt neljanda nime töötaja massiiv, mis annab meile "Sam”.
Nii et jah, see on see, kuidas eraldada nimed rühmadest erinevates veergudes, kasutades funktsiooni INDEX, SMALL, ROW, COLUMNS ja IF. Kui teil on selle funktsiooni suhtes kahtlusi või kui see teie jaoks ei tööta, andke mulle teada allpool olevast kommentaaride jaotisest.
Faili allalaadimine:
Seotud artiklid:
VLOOKUP Mitu väärtust
Väärtuse otsimiseks kasutage INDEX ja MATCH
Otsinguväärtus mitme kriteeriumiga
Populaarsed artiklid:
Funktsioon VLOOKUP Excelis
COUNTIF Excelis 2016
Funktsiooni SUMIF kasutamine Excelis