Vem som är för lat eller inte har tid att läsa – se videon. Detaljer och nyanser finns i texten nedan.
Formulering av problemet
Så vi har två bord – beställa bord и prislista:
Uppgiften är att automatiskt ersätta priser från prislistan i ordertabellen, med fokus på produktens namn så att du senare kan beräkna kostnaden.
Lösning
I Excel-funktionsuppsättningen, under kategorin Referenser och arrayer (Sök och referens) det finns en funktion VPR (VISNING).Den här funktionen letar efter ett givet värde (i vårt exempel är detta ordet "Äpplen") i kolumnen längst till vänster i den angivna tabellen (prislistan) som rör sig från topp till botten och, efter att ha hittat det, visar innehållet i den intilliggande cellen (23 rubel) . Schematiskt kan driften av denna funktion representeras Så:
För att underlätta ytterligare användning av funktionen, gör en sak på en gång – ge cellintervallet i prislistan ditt eget namn. För att göra detta, välj alla celler i prislistan utom "huvudet" (G3: H19), välj från menyn Infoga – Namn – Tilldela (Infoga — Namn — Definiera) eller tryck CTRL + F3 och ange vilket namn som helst (inga mellanslag) som Pris… Nu, i framtiden, kan du använda detta namn för att länka till prislistan.
Nu använder vi funktionen VPR… Välj cellen där den ska skrivas in (D3) och öppna fliken Formler – Funktionsinförande (Formler — Infoga funktion)… I kategorin Referenser och arrayer (Sök och referens) hitta funktionen VPR (VISNING) och tryck OK… Ett fönster för att ange argument för funktionen kommer att visas:
Vi fyller dem i tur och ordning:
- Önskat värde (uppslagsvärde) – namnet på produkten som funktionen ska hitta i kolumnen längst till vänster i prislistan. I vårt fall, ordet "Äpplen" från cell B3.
- Bord (Tabell Array) – en tabell från vilken de önskade värdena uXNUMXbuXNUMXbare tagits, det vill säga vår prislista. Som referens använder vi vårt eget namn "Pris" som angivits tidigare. Om du inte angav ett namn kan du bara välja tabellen, men glöm inte att trycka på knappen F4för att fästa länken med dollartecken, för annars kommer den att glida ner när vi kopierar vår formel ner till resten av cellerna i kolumn D3:D30.
- Kolumnnummer (Kolumnindexnummer) – serienummer (inte en bokstav!) Till kolumnen i prislistan som vi tar prisvärden från. Den första kolumnen i prislistan med namnen är numrerad 1, därför behöver vi priset från kolumnen numrerad 2.
- interval_lookup (Räckviddssökning) – endast två värden kan anges i detta fält: FALSE eller TRUE:
- Om ett värde anges 0 or LIGGANDE (FALSK), då betyder det faktiskt att endast sökning är tillåten exakt matchning, dvs om funktionen inte hittar den icke-standardartikel som anges i beställningstabellen i prislistan (om till exempel "Coconut" skrivs in), genererar den #N/A (ingen data) felet.
- Om ett värde anges 1 or SANN (SANN), då betyder det att du tillåter sökningen inte efter det exakta, men ungefärlig matchning, dvs i fallet med "kokos", kommer funktionen att försöka hitta en produkt med ett namn som är så nära "kokos" som möjligt och returnera priset för detta namn. I de flesta fall kan en sådan ungefärlig ersättning spela användaren ett spratt genom att ersätta värdet på fel produkt som faktiskt fanns där! Så för de flesta verkliga affärsproblem är ungefärlig sökning bäst att inte tillåta. Undantaget är när vi letar efter siffror och inte text – till exempel vid beräkning av stegrabatter.
Allt! Det återstår att trycka på OK och kopiera den angivna funktionen till hela kolumnen.
# N / A fel och deras undertryckande
Funktion VPR (VISNING) returnerar #N/A fel (#N/A) Om en:
- Exakt sökning aktiverad (argument Intervallvy = 0) och det önskade namnet finns inte i Bord.
- Grovsökning ingår (Intervallvy = 1), men Bord, där sökningen äger rum, sorteras inte i stigande namnordning.
- Formatet på cellen där det erforderliga värdet på namnet kommer från (till exempel B3 i vårt fall) och formatet på cellerna i den första kolumnen (F3: F19) i tabellen är olika (till exempel numerisk och text ). Det här fallet är särskilt typiskt när man använder numeriska koder (kontonummer, identifierare, datum, etc.) istället för textnamn. I det här fallet kan du använda funktionerna Ч и TEXT för att konvertera dataformat. Det kommer att se ut ungefär så här:
=UPSÖK(TEXT(B3),pris,0)
Du kan läsa mer om detta här.
- Funktionen kan inte hitta det önskade värdet eftersom koden innehåller mellanslag eller osynliga icke-utskrivbara tecken (radbrytningar, etc.). I det här fallet kan du använda textfunktioner TRIM (TRIM) и TRYCK(RENA) för att ta bort dem:
=SÖKUP(TRIMSPACES(CLEAN(B3)),pris,0)
=SÖKUP(TRIMMA(RENSA(B3));pris;0)
För att undertrycka felmeddelandet # N / A (#N/A) i de fall funktionen inte kan hitta en exakt matchning kan du använda funktionen FEL (FEL)… Så, till exempel, fångar denna konstruktion upp alla fel som genereras av VLOOKUP och ersätter dem med nollor:
= IFERROR (VLOOKUP (B3, pris, 2, 0), 0)
= IFERROR (VLOOKUP (B3; pris; 2; 0); 0)
PS
Om du behöver extrahera inte ett värde, utan hela uppsättningen på en gång (om det finns flera olika), måste du shamanisera med matrisformeln. eller använd den nya XLOOKUP-funktionen från Office 365.
- En förbättrad version av VLOOKUP-funktionen (VLOOKUP 2).
- Snabb beräkning av steg (intervall) rabatter med hjälp av VLOOKUP-funktionen.
- Hur man gör en "vänster VLOOKUP" med funktionerna INDEX och MATCH
- Hur man använder funktionen SÖKUPPSÖKNING för att fylla i formulären med data från listan
- Hur man tar ut inte det första, utan alla värden från bordet på en gång
- VLOOKUP2 och VLOOKUP3 fungerar från PLEX-tillägget