Tässä artikkelissa opimme kuinka saada viimeinen merkintä kuukausittain Excelissä.
Skenaario:
Kun käsittelemme tietojoukkoja, meidän on joskus otettava joitakin tuloksia eri kriteerien perusteella. Tässä ongelmakriteeri on, että arvon on oltava tiettyä kuukautta vastaava viimeinen merkintä. Ongelman voi tulkita kahdella tavalla.
- Sarakkeen viimeinen merkintä kuukausittain
- Kuukauden viimeinen päivämäärä sarakkeessa
Sinun on varmaan mietittävä, mikä on ero näiden kahden välillä. Ero on siinä, että ensimmäinen tehtävä poimii viimeisen merkinnän sarakkeesta, joka vastaa kuukauden nimeä, kun taas toinen ongelma poimii lähimmän päivämäärän arvon kuukauden lopun päivämäärän arvosta. Ymmärrämme molemmat tilanteet yksi kerrallaan. Ensinnäkin harkitsemme, miten saamme viimeisen merkinnän sarakkeeseen kuukausittain.
Kuinka saada viimeinen merkintä sarakkeittain kuukausittain Excelissä?
Käytämme tätä varten TEKSTI- ja LOOKUP -toimintoa. Tässä käytetään viimeisen arvon LOOKUP -funktionhaun attribuuttia. LOOKUP -funktio ottaa 3 argumentin hakuarvon, hakutaulukon ja tulosmatriisin. Käytämme siis hakutoiminto argumenttia 1/hautaulukkona. Katso lisätietoja alla olevasta kaavan syntaksista:
kaavan syntaksi:
| = HAKU (2,1/(TEKSTI (päivämäärät, "mmyyyy") = TEXT (kuukausi, "mmyyyyyy")), arvot) |
päivämäärät: päivämäärät taulukko datassa
arvot: tulostaulukko tiedoissa
kuukausi: kuukausi kriteerit
Esimerkki:
Kaikkien näiden ymmärtäminen voi olla hämmentävää. Ymmärrämme, miten tätä kaavaa käytetään esimerkissä. Tässä on tietoja päivämäärä- ja hinta -arvoista. Tätä varten tarvitsemme tammikuun 2019 kuukauden viimeisimmän merkinnän, joka on viimeinen merkintä tammikuun 2019 päivämäärän kanssa.
Käytä kaavaa:
| = HAKU (2,1/(TEKSTI (päivämäärät, "mmyyyy") = TEXT (F9, "mmyyyy")), Hinta) |
Selitys:
- TEXT (päivämäärät, "mmyyyy") palauttaa arvotaulukon "mmyyyy" -muodossa sen jälkeen, kun se on muutettu tekstiarvoiksi.
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEKSTI (F9, "mmyyyy") palauttaa päivämääräarvon (F9 -solussa) muodossa "mmyyyy" sen jälkeen, kun on muunnettu hakupäivän arvo, joka on "012019", ja tämä arvo sovitetaan yhteen edellä mainitun taulukon kanssa.
- Jos jako otetaan 1: llä, True -arvojen taulukko muutetaan 1s: ksi ja False -arvot #DIV/0 -virheeksi. Joten saamme palautetun taulukon muodossa.
{1; 1; 1; 1; #DIV/0!; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- LOOKUP -toiminto löytää nyt taulukosta arvon 2, jota ei löydy. Joten se palauttaa viimeistä 1 arvoa vastaavan merkinnän.
Käytetty nimettyjä alueita
päivämäärät: C3: C15
Hinta: D3: D15
Viimeinen hinta on 78.48, joka vastaa 31.01.2019. Kopioi kaava muihin soluihin painamalla Ctrl + D tai vetämällä alaspäin solun oikeasta alakulmasta.
Kuten näet, kaava palauttaa kaikki vaaditut arvot. Funktio palauttaa virheen #N/A, jos hakukuun arvo ei vastaa mitään päivämäärämerkintää. Yllä olevassa esimerkissä päivämäärät on lajiteltu. Joten kuukauden viimeinen merkintä vastaa kuukauden viimeistä päivämäärää. Jos päivämäärämerkintöjä ei ole lajiteltu, lajittelemme ensin päivämääräarvot ja käytämme yllä olevaa kaavaa.
Kuinka saada viimeisen päivämäärän merkintä kuukausittain Excelin sarakkeeseen?
Käytämme tähän VLOOKUP- ja EOMONTH -toimintoa. EOMONTH -funktio ottaa kuukauden päivämäärän arvon ja palauttaa vaaditun kuukauden viimeisen päivämäärän. VLOOKUP -toiminto etsii kuukauden viimeisen päivämäärän tai lähimmän edellisen päivämäärän ja palauttaa kyseistä arvoa vastaavan arvon.
Käytä kaavaa:
| = VLOOKUP (EOMONTH (F3,0), taulukko, 2) |
Selitys :
- EOMONTH (F3,0) palauttaa saman kuukauden viimeisen päivämäärän. 0 -argumenttia käytetään päivämäärän palauttamiseen samasta kuukaudesta.
- Nyt hakuarvosta tulee tietyn kuukauden viimeinen päivämäärä.
- Nyt arvoa haetaan taulukon ensimmäisestä sarakkeesta ja etsitään viimeistä päivämäärää, jos löydetty palauttaa arvoa vastaavan arvon ja jos ei löydy, palauttaa viimeisen vastaavan tuloksen, joka on lähimpänä hakuarvoa, ja palauttaa vastaavan tuloksen.
- Funktio palauttaa taulukon toisen sarakkeen arvon, koska kolmas argumentti on 2.
Tässä kaava palauttaa tuloksen 78.48. Kopioi nyt kaava muihin soluihin käyttämällä pikanäppäintä Ctrl + D tai vetämällä alaspäin solun oikeasta alakulmasta.
Kuten näet, kaava toimii hyvin. On vain yksi ongelma. Kun etsimme huhtikuun viimeistä päivämäärää, funktio palauttaa maaliskuun vastaavan arvon, koska taulukkoon ei ole merkitty huhtikuun päivämäärää. Varmista, että saat nämä tulokset.
Tässä on kaikki huomautukset kaavan käytöstä.
Huomautuksia:
- Käytä kaavaa, joka vastaa artikkelissa mainittua ongelmaa.
- VLOOKUP ja LOOKUP -toiminnot ovat toimintoja, kun hakuarvo on numero eikä sitä ole hakutoiminnossa, palauttaa arvon, joka vastaa vain lähimpiä lukuarvoa, joka on pienempi kuin hakuarvo.
- Excel tallentaa päivämääräarvot numeroina.
- Edellä mainitut kaksi funktiota palauttavat vain yhden arvon.
- Käytä VLOOKUP -funktion neljättä argumenttia 0: na saadaksesi taulukon hakuarvon täsmällisen vastaavuuden.
Toivottavasti tämä artikkeli siitä, miten Excelin viimeinen merkintä saadaan kuukausittain, on selittävä. Lisää artikkeleita arvojen poimimisesta taulukosta käyttämällä kaavoja täältä. 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
Etsi viimeinen rivi, jossa on sekoitettuja tietoja Excelissä : työskentely numeroiden, tekstin tai tyhjän solun kanssa samassa taulukossa. Pura viimeinen rivi ei -tyhjällä solulla käyttämällä MATCH -toimintoa Excelissä.
Tietyn kuukauden viimeisen päivän löytäminen : EOMONTH -funktio palauttaa annetun päivämäärän kuukauden viimeisen päivämäärän.
Kuinka saada viimeinen arvo sarakkeeseen : Viimeinen merkintä suuressa datassa. CELL- ja INDEX -funktion yhdistelmä palauttaa sarakkeen tai tietoluettelon viimeisen arvon.
Ero viimeiseen ei -tyhjään soluun : ottaa vaihtoehtoisen eron luettelon viimeisestä arvosta käyttämällä numeroita käyttämällä IF- ja LOOKUP -toimintoa Excelissä.
Etsi viimeinen tietorivi tekstiarvoineen Excelistä : Palauta tekstiarvojen ja tyhjien solujen taulukossa taulukon viimeinen arvo käyttämällä Excelin MATCH- ja REPT -funktiota.
SUMPRODUCT -toiminnon käyttäminen Excelissä: Palauttaa summan, kun se on ottanut Excelissä useiden matriisien vastaavien arvojen tulon.
Kuinka käyttää jokerimerkkejä Excelissä : Haku, lasku, summa, keskiarvo ja enemmän matemaattista toimintaa soluissa, jotka vastaavat lauseita käyttämällä excelin yleismerkkejä.
Suosittuja artikkeleita:
IF -toiminnon käyttäminen Excelissä : Excelin IF -käsky tarkistaa ehdon ja palauttaa tietyn arvon, jos ehto on TOSI, tai palauttaa toisen arvon, jos EPÄTOSI.
VLOOKUP -toiminnon käyttäminen 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.