În acest articol, vom afla cum să obțineți ultima intrare lunară în Excel.
Scenariu:
Când lucrăm cu seturi de date, uneori trebuie să extragem unele rezultate pe baza unor criterii diferite. Aici criteriul problemei este valoarea trebuie să fie ultima intrare corespunzătoare unei luni date. Problema poate fi interpretată în două moduri.
- Ultima intrare în coloană după lună
- Valoarea ultimei date a lunii din coloană
Trebuie să vă gândiți care este diferența dintre cele două. Diferența este că prima problemă extrage ultima intrare din coloană care se potrivește cu numele lunii, în timp ce a doua problemă extrage cea mai apropiată valoare a datei de la data la sfârșitul lunii. Vom înțelege ambele situații pe rând. În primul rând, vom lua în considerare Cum să obținem ultima intrare din coloană în fiecare lună.
Cum se obține ultima intrare din coloană pe lună în Excel?
Pentru aceasta vom folosi funcția TEXT și funcția de căutare. Aici folosim un atribut de căutare a funcției LOOKUP pentru ultima valoare. Funcția de căutare are 3 valori de căutare a argumentelor, matrice de căutare și matrice de rezultate. Deci, folosim argumentul matricei de căutare ca 1 / matrice de căutare. Pentru mai multe detalii, consultați sintaxa formulei de mai jos:
sintaxă formulă:
| = LOOKUP (2,1 / (TEXT (date, "mmyyyy") = TEXT (lună, "mmyyyy")), valori) |
date: matrice de date în date
valori: matrice de rezultate în date
luna: criteriile lunii
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Să înțelegem cum să folosim această formulă într-un exemplu. Aici avem date cu date și valori ale prețului. Pentru aceasta avem nevoie de cea mai recentă intrare din luna ianuarie 2019, care va fi ultima intrare cu data de ianuarie 2019. Aici pentru matricea de date și matricea de prețuri am folosit intervale denumite.
Folosiți formula:
| = LOOKUP (2,1 / (TEXT (date, "mmyyyy") = TEXT (F9, "mmyyyy")), Preț) |
Explicaţie:
- TEXT (date, „mmyyyy”) va returna matricea de valori în format „mmyyyy” după convertirea acestuia în valori text care este
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEXT (F9, „mmyyyy”) va returna valoarea datei (în celula F9) în format „mmyyyy” după convertirea valorii datei de căutare care este „012019” și această valoare va fi potrivită cu matricea menționată mai sus.
- Luarea diviziunii cu 1 va converti matricea de valori adevărate în 1s și valori false în eroare # DIV / 0. Deci vom obține matricea returnată ca.
{1; 1; 1; 1; # DIV / 0!; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! ; # DIV / 0! }
- Acum funcția LOOKUP găsește valoarea 2 în matrice care nu va fi găsită. Deci returnează intrarea corespunzătoare ultimei valori 1.
Intervalele denumite utilizate
date: C3: C15
Preț: D3: D15
Ultimul preț este 78,48 corespunzător la 31-01-2019. Copiați formula în alte celule folosind Ctrl + D sau glisând în jos din colțul din dreapta jos al celulei.
După cum puteți vedea, formula returnează toate valorile necesare. Funcția returnează o eroare # N / A, dacă valoarea lunii de căutare nu se potrivește cu nicio intrare de dată. În exemplul de mai sus, intrările de dată sunt ordonate. Deci, ultima intrare a lunii se potrivește cu ultima intrare a datei din lună. Acum, dacă intrările de date nu sunt sortate, atunci vom sorta mai întâi valorile datei și vom folosi formula de mai sus.
Cum se obține ultima dată de intrare pe lună în coloana din Excel?
Pentru aceasta vom folosi funcția VLOOKUP și EOMONTH. Funcția EOMONTH ia valoarea datei lunii și returnează ultima dată a lunii solicitate. Funcția VLOOKUP găsește ultima dată a lunii sau cea mai apropiată dată anterioară și returnează o valoare corespunzătoare acelei valori.
Folosiți formula:
| = VLOOKUP (EOMONTH (F3,0), tabel, 2) |
Explicație:
- EOMONTH (F3,0) returnează ultima dată a aceleiași luni. 0 argument folosit pentru a returna data din aceeași lună.
- Acum, valoarea de căutare devine ultima dată a lunii date.
- Acum valoarea va fi căutată în prima coloană a tabelului și va căuta ultima dată, dacă este găsită returnează valoarea corespunzătoare valorii și dacă nu a fost găsită, atunci returnează ultimul rezultat de potrivire, cel mai apropiat de valoarea de căutare și returnează rezultatul corespunzător.
- Funcția returnează valoarea din a doua coloană a tabelului, deoarece al treilea argument este 2.
Aici formula returnează 78.48 ca rezultat. Acum copiați formula în alte celule folosind comanda rapidă Ctrl + D sau glisând în jos din colțul din dreapta jos al celulei.
După cum puteți vedea, formula funcționează bine. Există doar o problemă. Pe măsură ce căutăm ultima dată corespunzătoare lunii aprilie, funcția returnează valoarea corespunzătoare din luna martie, deoarece nu există nicio intrare a datei de aprilie în tabel. Asigurați-vă că obțineți aceste rezultate.
Iată toate notele observaționale privind utilizarea formulei.
Note:
- Folosiți formula corespunzătoare problemei enunțate în articol.
- Funcția VLOOKUP și LOOKUP sunt funcții atunci când valoarea de căutare este număr și nu este prezentă în matricea de căutare, returnează valoarea corespunzătoare doar celui mai apropiat număr mai mic decât valoarea de căutare.
- Excel stochează valorile datei ca numere.
- Cele două funcții menționate mai sus returnează o singură valoare.
- Utilizați al patrulea argument din funcția VLOOKUP ca 0 pentru a obține potrivirea exactă a valorii de căutare din tabel.
Sper că acest articol despre Cum să obțineți ultima intrare lunară în Excel este explicativ. Găsiți mai multe articole despre extragerea valorilor din tabel folosind formule aici. 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
Găsiți ultimul rând cu date mixte în Excel : lucrul cu numere, text sau celulă necompletată în aceeași matrice. Extrageți ultimul rând cu celulă necompletată utilizând funcția MATCH în Excel.
Găsirea ultimei zile a unei luni date : Funcția EOMONTH returnează ultima dată a lunii, a valorii datei date.
Cum să obțineți ultima valoare în coloană : Ultima intrare într-un volum mare de date, o combinație de funcții CELL și INDEX returnează ultima valoare dintr-o coloană sau listă de date.
Diferență cu ultima celulă necompletată : ia diferența alternativă față de ultima valoare din listă cu numere folosind funcția IF și LOOKUP în Excel.
Găsiți ultimul rând de date cu valori text în Excel : Într-o matrice de valori de text și celule goale, returnați ultima valoare din matrice utilizând funcția MATCH și REPT în excel.
Cum se utilizează funcția SUMPRODUCT în Excel: Returnează SUMA după ce a luat produsul valorilor corespunzătoare în mai multe tablouri în excel.
Cum se utilizează metacaracterele în Excel : Căutare, numărare, sumă, medie și mai multe operații matematice pe celule care se potrivesc cu fraze folosind metacaracterele în excel.
Articole populare:
Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.
Cum se utilizează funcț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.