Šajā rakstā mēs uzzināsim, kā izgūt cenu no saraksta, kas atbilst gan kategorijas, gan vienuma kritērijiem programmā Excel.
Scenārijs:
Tabulā ir viegli atrast vērtību ar vienu kritēriju, tāpēc mēs varētu vienkārši izmantot VLOOKUP. Bet, ja jūsu datos nav vienas slejas kritēriju un jums ir jāatrod vairākās slejās kritēriji, lai tie atbilstu vērtībai, VLOOKUP nepalīdz.
Vispārīga formula gan kategorijai, gan vienumam
| = INDEKSS (uzmeklēšanas diapazons, MATCH (1, INDEKSS ((vienums1 = vienuma_diapazons) * (2. kategorija = kategorijas_diapazons), 0,1), 0)) |
lookup_range: tas ir diapazons, no kura vēlaties iegūt vērtību.
Kritēriji1, Kritēriji2, Kritēriji N: Šie ir kritēriji, kuriem vēlaties atbilst diapazonā1, diapazonā2 un diapazonā N. Jums var būt līdz 270 kritērijiem - diapazona pāri.
Diapazons1, diapazons2, diapazonsN: šie ir diapazoni, kuros jūs atbilstat saviem attiecīgajiem kritērijiem
Piemērs :
To visu var būt mulsinoši saprast. Sapratīsim, kā izmantot funkciju, izmantojot piemēru. Šeit mums ir datu tabula. Es vēlos izvilkt klienta vārdu, izmantojot rezervācijas datumu, celtnieku un apgabalu. Tātad šeit man ir trīs kritēriji un viens meklēšanas diapazons.
Ierakstiet šo formulu šūnā I4, nospiediet taustiņu Enter.
| = INDEKSS (E2: E16, MATCH (1, INDEKSS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Mēs jau zinām, kā EXCEL darbojas INDEX un MATCH funkcija, tāpēc šeit to neskaidrosim. Mēs šeit runāsim par triku, ko izmantojām.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Galvenā daļa ir šī. Katra šī paziņojuma daļa atgriež patiesu nepatiesu masīvu.
Reizinot Būla vērtības, tās atgriež masīvu no 0 un 1. Reizināšana darbojas kā AND operators. Smagi, ja visas vērtības ir patiesas, tikai tad atgriež 1 citu 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Tas viss atgriezīsies
Kas tiks tulkots
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEKSS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkcija INDEX atgriezīs to pašu masīvu ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) līdz MATCH funkcijai kā uzmeklēšanas masīvs.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): funkcija MATCH meklēs 1 masīvā {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Un atgriezīs masīvā atrastā pirmā indeksa numuru. Šeit ir 8.
INDEKSS (E2: E16, MATCH (1, INDEKSS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Visbeidzot, INDEX atgriezīsies vērtība no dotā diapazona (E2: E16) pie atrastā indeksa (8).
Ja jūs attiecīgi varat nospiest taustiņu kombināciju CTRL + SHIFT + ENTER, varat izslēgt iekšējo INDEX funkciju. Vienkārši uzrakstiet šo formulu un nospiediet CTRL + SHIFT + ENTER.
Formula
| = INDEKSS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Vispārīga masīva formula vairāku kritēriju meklēšanai
| = INDEKSS (uzmeklēšanas_diapazons, MATCH (1, (kritērijs1 = diapazons1)*(kritērijs2 = diapazons2)*(kritērijsN = diapazonsN), 0)) |
Šeit ir visas novērojumu piezīmes, izmantojot formulu programmā Excel
Piezīmes:
- Izmantojiet Vlookup, ja jums ir jāatbilst vienam kritērijam, tā ir izplatīta prakse.
- Uzmeklēšanas masīvā varat pievienot vairāk rindu un kolonnu.
- Teksta argumenti jāsniedz pēdiņās ("").
- Tabulai VLOOKUP kreisajā malā vai pirmajā kolonnā jābūt lookup_array.
- Kolu indekss nevar būt 1, jo tas ir uzmeklēšanas masīvs
- Precīzas atbilstības vērtībai tiek izmantots 0 arguments. Izmantojiet 1 aptuvenai atbilstības vērtībai.
- Funkcija atgriež kļūdu #N/A, ja uzmeklēšanas masīvā nav atrodama uzmeklēšanas vērtība. Tāpēc, ja nepieciešams, pamaniet kļūdu.
Ceru, ka šis raksts par to, kā iegūt cenu no saraksta, kas atbilst gan kategorijas, gan vienuma kritērijiem programmā Excel, ir paskaidrojošs. Vairāk rakstu par vērtību un saistīto Excel formulu aprēķināšanu meklējiet šeit. Ja jums patika mūsu emuāri, kopīgojiet tos ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē.
Kā iegūt jaunāko cenu programmā Excel : Parasti jebkurā biznesā tiek atjauninātas cenas, un pirkumiem vai pārdošanai obligāti jāizmanto jaunākās cenas. Lai iegūtu jaunāko cenu no Excel saraksta, mēs izmantojam funkciju LOOKUP. Funkcija LOOKUP iegūst jaunāko cenu.
Funkcija VLOOKUP, lai aprēķinātu atzīmi programmā Excel : Lai aprēķinātu pakāpes, IF un IFS nav vienīgās funkcijas, kuras varat izmantot. VLOOKUP ir efektīvāks un dinamisks šādiem nosacītiem aprēķiniem. Lai aprēķinātu pakāpes, izmantojot VLOOKUP, mēs varam izmantot šo formulu.
17 lietas par Excel VLOOKUP : VLOOKUP visbiežāk tiek izmantots, lai izgūtu atbilstošas vērtības, taču VLOOKUP var paveikt daudz vairāk. Šeit ir 17 lietas par VLOOKUP, kas jums jāzina, lai efektīvi izmantotu.
MEKLĒT pirmo tekstu no saraksta programmā Excel : Funkcija VLOOKUP lieliski darbojas ar aizstājējzīmēm. Mēs to varam izmantot, lai izvilktu pirmo teksta vērtību no dotā saraksta programmā Excel. Šeit ir vispārīgā formula.
MEKLĒŠANAS datums ar pēdējo vērtību sarakstā : Lai izgūtu datumu, kurā ir pēdējā vērtība, mēs izmantojam funkciju LOOKUP. Šī funkcija pārbauda šūnu, kurā ir pēdējā vektora vērtība, un pēc tam izmanto šo atsauci, lai atgrieztu datumu.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti : Ātrāk izpildiet savus uzdevumus programmā Excel. Šie īsceļi palīdzēs jums palielināt darba efektivitāti programmā Excel.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.