Tässä artikkelissa opimme kuinka indeksoida ja lajitella arvoja Excelin apuohjelma -sarakkeen avulla.
Skenaario:
Joskus käsitellään epämuodollisia tietoja. Meidän on saatava lyhyempi versio vaadittavina tietoina avustajan sarakkeeseen perustuvista taulukkotiedoista. Tässä ensin ymmärrämme, miten saamme auttajasarakkeen ja sitten lajitellaan pienet vaaditut tiedot taulukon tietojen perusteella.
Kuinka indeksoida alueen arvot?
Tässä artikkelissa meidän on käytettävä COUNTIF -toimintoa. Nyt teemme funktiosta kaavan. Tässä annetut tekstin ja numeroiden sekoitetut arvot alueella. Meidän on indeksoitava ne nousevaan järjestykseen ensin numeerisesti ja sitten aakkosjärjestykseen.
Yleinen kaava:-
| = COUNTIF (lista, "<=" & f_arvo) + (COUNT (lista) * ISTEXT (f_arvo)) |
luettelo: luettelo numeroista ja tekstistä
f_value: alueen ensimmäinen arvo
Selitys:
- COUNTIF -toiminto laskee palautettujen luettelon arvojen määrän
- COUNT -funktio hakee alueen numeroiden määrän.
- ISTEXT -toiminto tarkistaa, onko valitun solun arvo teksti vai ei. Palauttaa TOSI tekstille ja EPÄTOSI numeroille.
Esimerkki:
Kaikkien näiden ymmärtäminen voi olla hämmentävää. Ymmärrämme, miten saamme auttajasarakkeen käyttämällä selitettyä kaavaa. Tässä auttaja -sarake perustuu lajitteluarvoihin järjestyksessä (ensin numerot ja sitten aakkosjärjestyksessä). Tätä varten käytämme kaavaa saadaksemme indeksi- tai sijoitussarakkeen avustavaksi sarakkeeksi alla näkyville kulutiedoille.
Täältä löydät alueen arvot. Laskentatarkoituksiin käytämme kuluna kiinteän matriisin D5: D12 nimettyä aluetta.
Käytä kaavaa:
| = COUNTIF (kulu, "<=" & D5) + (COUNT (kulu) * ISTEXT (D5)) |
Kaava näyttää yllä olevan tilannekuvan mukaiselta. Arvo ja taulukko annetaan kaavassa nimetty alue ja soluviittaus.
Kuten näet, alueen ensimmäisen solun indeksi on 5, mikä tarkoittaa, että jos asetamme kulut lajittelujärjestykseen, Sähkö sijoitetaan viidenneksi. Kopioi nyt kaava muihin soluihin vetämällä alas -vaihtoehtoa tai käyttämällä pikanäppäintä Ctrl + D alla esitetyllä tavalla saadaksesi indeksit tai sijoitukset muille arvoille.
Kuten näette, nyt meillä on luettelo indeksistä tai sijoituksesta, joka on kulujen lajittelutapa. Käytämme nyt tätä Rank -saraketta auttajasarakkeena saadaksemme taulukon lyhyemmän version, jolla on sama järjestys uudessa luettelossa tai taulukossa.
Kuinka lajitella arvot Excelin apusarakkeen avulla?
Lajittelunumerossa Excelissä käyttämällä apuohjelma -saraketta on oltava hakemistonumerot ja vaadittava alue lajittelua varten. Helper -sarake voi olla mikä tahansa vaadittu tilaus. Jos esimerkiksi haluat saada luettelon vaaditussa satunnaisessa järjestyksessä, aseta arvoa vastaava sijoitus indeksi- tai sijoitussarakkeeseen, joka toimii apusarakkeena.
- INDEX -toiminto
- MATCH -toiminto
- RIVIT -toiminto
Nyt teemme kaavan käyttämällä yllä olevia funktioita. MATCH -toiminto palauttaa alueen alimman haun indeksin. INDEX -funktio ottaa rivihakemiston argumentiksi ja palauttaa vastaavat vaaditut tulokset. Tämä toiminto löytää
Yleinen kaava:
| = INDEKSI (return_array, MATCH (RIVIT (suhteellinen_viite), indeksi, 0)) |
return_array: taulukko, josta arvo palautetaan
Suhteellinen viite: luo nousevan järjestyksen kaavassa. Voidaan käyttää minkä tahansa taulukon kanssa
Indeksi: taulukon hakemistojärjestelmä
0: tarkka ottelu
Selitys:
- RIVIT (suhteellinen_viite) palauttaa arvon laajennetun kaavan pituuden mukaan. Jos sitä käytetään ensimmäisessä solussa, se on yksi, sitten toinen ja jatka, kunnes se ulottuu.
- MATCH -toiminto vastaa indeksiä riviarvon kanssa, jotta ne saadaan nousevaan järjestykseen ROWS -funktion avulla.
- INDEX -funktio palauttaa vastaavan indeksin vastaavan arvon.
Esimerkki:
Kaikkien näiden ymmärtäminen voi olla hämmentävää. Joten ymmärretään tämä kaava suorittamalla se alla olevassa esimerkissä. Tässä järjestimme tiedot satunnaisessa järjestyksessä, jotta saisimme kolme ensimmäistä arvoa Indeksi -sarakkeen perusteella. Kaavan avulla saat vaaditun lyhyen taulukon ensimmäisen arvon.
Käytä kaavaa:
| = INDEKSI (B5: B13, MATCH (RIVIT (D5: D5), D5: D13,0)) |
Kaava näyttää yllä olevan tilannekuvan mukaiselta. Arvotaulukko annetaan nimettyä aluetta ja soluviittausta.
Kuten näet, ensimmäinen arvo on alueelta tuleva luku "Maitoa" vastaava indeksi on 1. Kopioi nyt kaava muihin soluihin käyttämällä alasvetovaihtoehtoa tai käyttämällä pikanäppäintä Ctrl + D alla olevan kuvan mukaisesti saadaksesi loput arvot.
Kuten näette, arvot indeksoidaan nousevassa järjestyksessä. Saimme kaikki arvot, koska se osoittaa myös, että kaava toimii hyvin. Voimme poimia lukuarvot käyttämällä samaa kaavaa.
Kuten näet, meillä on taulukosta lyhyemmät ja lajitellut arvot käyttämällä auttajasaraketta, joka indeksoidaan vastaavasti.
Seuraavassa on muutamia huomautuksia alla.
Huomautuksia:
- Kaava toimii vain numeroiden ja tekstin kanssa.
- Kaava jättää tekstiarvon huomiotta vertaillessaan numeroita ja jättää huomiotta numerot, kun tekstiarvot vastaavat.
- Helper -sarake voi olla mikä tahansa vaadittu sarakejärjestys.
Toivottavasti tämä artikkeli arvojen indeksoimisesta ja lajittelemisesta Excel -apurisarakkeen kanssa on selittävä. Tutustu muihin Excel -hakukaavoja koskeviin artikkeleihin täällä. Jos pidit blogistamme, jaa se ystävillesi Facebookissa. Voit myös seurata meitä Twitterissä ja Facebookissa. Haluaisimme kuulla sinusta, kerro meille, kuinka voimme parantaa, täydentää tai innovoida työtämme ja parantaa sitä sinulle. Kirjoita meille sähköpostitse
SUMPRODUCT -toiminnon käyttäminen Excelissä: Palauttaa summan Excelin useiden matriisien arvojen kertomisen jälkeen.
SUM, jos päivämäärä on välillä : Palauttaa Excelissä annettujen päivämäärien tai ajanjaksojen välisten arvojen SUMMA.
Summa, jos päivämäärä on suurempi kuin annettu päivämäärä: Palauttaa arvojen SUMMAN annetun päivämäärän tai jakson jälkeen Excelissä.
2 tapaa laskea yhteen kuukausittain Excelissä: Palauttaa arvojen SUMMA tietyn kuukauden aikana Excelissä.
Kuinka laskea useita sarakkeita ehdolla: Palauttaa useiden sarakkeiden arvojen summan, joiden ehto on Excel
Kuinka käyttää jokerimerkkejä Excelissä : Laske ilmauksia vastaavat solut käyttämällä Excelin yleismerkkejä
Suosittuja artikkeleita
50 Excel -pikakuvake tuottavuuden lisäämiseksi : Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
Kuinka käyttää tVLOOKUP -toiminto Excelissä : Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja taulukoilta.
COUNTIF -toiminnon käyttäminen Excelissä : Laske arvot olosuhteilla tämän hämmästyttävän toiminnon avulla. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä : Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.