Använd IFNA runt en spillande sökning för att fylla dess #N/A-resultat från en andra referenstabell. Det här exemplet för Excel för Mac matchar etiketter för konsttillgångar, bevarar antal som redan hittats i den primära tabellen och markerar tydligt etiketter som saknas i båda tabellerna.

Vilka versioner av Excel för Mac stöder den här metoden?

Använd Excel för Microsoft 365 för Mac, Excel 2024 för Mac eller Excel 2021 för Mac. Microsoft listar dessa utgåvor för både XLOOKUP och IFNA. Dokumentationen för XLOOKUP undantar uttryckligen Excel 2016 och Excel 2019, trots att dessa utgåvor finns med i sidans bredare lista över tillämpliga versioner. Formlerna nedan är avsedda för Mac-versionerna som stöds. XLOOKUP-funktionen, IFNA-funktionen

Uppgiften är specifik: du har en kolumn med efterfrågade tillgångsetiketter och vill ha ett antal per etikett. Den primära referensen har företräde; den andra referensen tillhandahåller antal endast när den primära sökningen ger #N/A. Alla etiketter och antal nedan är syntetiska.

Konfigurera två referenstabeller och en lista över efterfrågade tillgångar

Börja på ett tomt kalkylblad i en arbetsbok. Använd celladresserna nedan så fungerar formlerna utan att du behöver ändra deras referenser. De här små referenstabellerna är vanliga cellområden i kalkylbladet; du behöver inte konvertera dem till Excel-tabellobjekt.

Ange den primära referensen i D1:E4:

Cell i kolumn D Tillgångsetikett Cell i kolumn E Antal
D1 Tillgång E1 Antal
D2 Bladkontur E2 8
D3 Pricktextur E3 0
D4 Plattmönster E4 12

Ange den andra referensen i G1:H5:

Cell i kolumn G Tillgångsetikett Cell i kolumn H Antal
G1 Tillgång H1 Antal
G2 Pricktextur H2 9
G3 Kurvskiss H3 5
G4 Korntextur H4 3
G5 Bladkontur H5 10

Ange Efterfrågad tillgång i J1 och placera sedan dessa fem etiketter i J2:J6, en per cell och i följande ordning:

  1. Bladkontur
  2. Pricktextur
  3. Kurvskiss
  4. Korntextur
  5. Bågmotiv

Behåll antal som numeriska värden och fyll i alla antalsceller i de två referenserna. I det här exemplet betyder noll ett registrerat antal på noll. Det betyder inte att tillgången saknas.

Skapa den spillande listan med etiketter

Ange rubrikerna Tillgång, Primärt antal och Slutfört antal i A1, B1 respektive C1. Lämna A2:C20 tomt och ange sedan den här formeln i A2:

=FILTER(J2:J20,J2:J20<>"","")

Formeln filtrerar bort tomma celler i listan över efterfrågade tillgångar. Med de angivna uppgifterna spiller de fem tillgångsetiketterna ut i A2:A6. Microsoft dokumenterar FILTER för de tre Mac-utgåvorna som listas ovan och förklarar att det sista argumentet anger ett resultat när inga rader uppfyller villkoret. Här är resultatet en tom sträng. FILTER-funktionen

De senare formlerna använder A2# för att referera till hela det här spillområdet. Operatorn # följer spillområdets storlek, så du slipper hårdkoda den aktuella slutpunkten som A6. Microsoft beskriver beteendet i sin dokumentation om spillområdesoperatorn.

Fyll i saknade antal från den andra referensen

Skapa först den primära sökningen i B2:

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

Den söker i den primära tillgångskolumnen och returnerar motsvarande antal. XLOOKUP använder exakt matchning som standard och returnerar #N/A när ingen matchning finns och inget alternativt resultat har angetts. XLOOKUP-funktionen

Med exempeldata ska B2:B6 innehålla 8, 0, #N/A, #N/A och #N/A. Låt dessa fel för saknade matchningar vara kvar till nästa steg.

Ange nu den kompletterande formeln i C2:

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA undersöker det primära spillområdet. På positioner som innehåller #N/A använder formeln motsvarande resultat från den andra sökningen. Övriga primära resultat lämnas oförändrade. Microsoft dokumenterar uttryckligen att IFNA returnerar en matris med resultat när det första argumentet är en matrisformel. IFNA-funktionen

Det förväntade resultatet i kalkylbladet är:

Tillgång i kolumn A Primärt antal i kolumn B Slutfört antal i kolumn C
Bladkontur 8 8
Pricktextur 0 0
Kurvskiss #N/A 5
Korntextur #N/A 3
Bågmotiv #N/A Finns inte i listan

Läs raderna som ett test av prioritetsordningen. Bladkontur förblir 8 trots att den andra referensen innehåller 10. Pricktextur förblir 0 trots att den andra referensen innehåller 9. Kurvskiss och Korntextur får sina sekundära antal. Bågmotiv förblir olöst och synligt eftersom det inte finns i någon av referenserna.

De två matriserna måste beskriva samma efterfrågade tillgångar i samma ordning. I den här konfigurationen kommer båda från A2#. Om du anpassar en befintlig arbetsbok ska du inte kombinera ett primärt resultat som sorterats på ett sätt med en reservsökning som bygger på en etikettlista i en annan ordning.

Använd en formel om du inte behöver mellankolumnen

Om du vill samla den primära sökningen och kompletteringen i en formel ersätter du formeln i C2 med det här fullständiga alternativet:

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

Formeln använder samma exempeldata och bör ge samma fem färdiga antal. Kolumn B behövs inte längre i beräkningen. Om du behåller tvåstegsversionen när du konfigurerar arbetsboken blir det lättare att se vilken referens som tillhandahåller varje resultat.

Avgör om ett resultat faktiskt saknas

Innan du anpassar formeln bör du kontrollera vad den första sökningen returnerar. En saknad etikett, ett resultat som ser tomt ut och ett antal på noll kräver olika beslut.

Primärt resultat Vad den här kompletteringsformeln gör Vad du bör kontrollera
Numeriskt antal, inklusive 0 Bevarar det Kontrollera att det är det avsedda primära antalet.
#N/A Använder den sekundära sökningen Kontrollera att felet betyder att etiketten saknas.
Tom sträng, \"\" Bevarar den Kontrollera om den primära formeln döljer saknade matchningar.
Ett annat fel Låter det visas Åtgärda det underliggande felet i formeln eller referensen.

Om den befintliga primära sökningen till exempel använder \"\" som argument för if_not_found, kan IFNA inte identifiera den tomma strängen som #N/A. Ta bort det argumentet från den primära sökningen, så som visas i B2, eller använd den kombinerade formeln ovan. Kompletteringslogiken förutsätter att resultatet för en saknad matchning är igenkännbart. XLOOKUP-funktionen, IFNA-funktionen

Kontrollera även själva referensernas antal. IFNA reagerar på ett felvärde; den testar inte om en antalscell har fyllts i. Om en matchad post i den primära referensen saknar ett färdigt antal bör du komplettera posten eller definiera en separat regel för ofärdiga antal innan du använder det här arbetsflödet.

Det finns ytterligare en skillnad: ett befintligt #N/A-fel i den primära antalsdatan utlöser också den här reservsökningen. För det här exemplet är förutsättningen rena numeriska antalskolumner, där #N/A bara uppstår vid en misslyckad sökning. Kontrollera att det stämmer innan du behandlar varje ersättning som en saknad tillgångsmatchning.

Kontrollera prioritetsordningen och se till att spillområdet fungerar

Använd de fem raderna i exemplet som en liten kontroll innan du byter ut dem mot dina egna tillgångsetiketter. Tillsammans testar de en primär matchning, ett giltigt nollvärde, två sekundära matchningar och en etikett som saknas i båda referenserna.

Gör en extra kontroll genom att tillfälligt ändra det sekundära antalet för Bladkontur från 10 till 14. Det färdiga antalet ska förbli 8. Ändra sedan det sekundära antalet för Kurvskiss från 5 till 6; det färdiga antalet ska bli 6. Återställ originalvärdena efteråt. Det här är exempel på kontroller för din arbetsbok, inte rapporterade testresultat från Excel.

Ha en avsedd post per tillgångsetikett i varje referens. XLOOKUP söker normalt från den första posten och returnerar den första matchningen, så du bör inte förvänta dig att upprepade etiketter kombinerar antal. Klargör vad dubbletter betyder innan du förlitar dig på den här sökdesignen. XLOOKUP-funktionen

Frigör ett blockerat resultatområde

Ange varje spillande formel endast i dess startcell. Excel fyller automatiskt i de intilliggande cellerna, och endast den översta vänstra cellen i ett spillområde kan redigeras. Placera formlerna utanför Excel-tabellobjekt; Microsoft stöder tabeller som källdata men stöder inte spillande formler inuti dem. Dynamiska matrisformler och spillmatriser

Om du ser #SPILL! markerar du formelcellen för att visa den avsedda resultatgränsen. Använd alternativet Select Obstructing Cells i felaviseringen om det finns tillgängligt, och flytta poster som blockerar resultatet. Sammanfogade celler kan också förhindra spillning; flytta resultatet eller dela upp de berörda cellerna. Så här åtgärdar du felet #SPILL!

Utöka indata medvetet

Formeln för listan över efterfrågade tillgångar undersöker J2:J20. Etiketter som läggs till inom det området inkluderas automatiskt; om du anger etiketter längre ned måste du utöka formelns område. På samma sätt använder referenssökningarna fasta områden som slutar vid raderna 4 och 5. Utöka varje tillgångsområde och dess motsvarande antalsområde tillsammans när du lägger till poster.

För större kalkylblad kan Excel-tabeller med strukturerade referenser göra att källområdena justeras när rader läggs till eller tas bort. Behåll resultatformlerna i kalkylbladets rutnät utanför tabellerna. Dynamiska matrisformler och spillmatriser

Behåll indata i det här exemplet i samma arbetsbok. Microsoft dokumenterar begränsat stöd för dynamiska matriser mellan arbetsböcker: länkade dynamiska matriser kräver att båda arbetsböckerna är öppna, och en uppdatering efter att källarbetsboken har stängts kan ge #REF!. Dynamiska matrisformler och spillmatriser

När de förväntade raderna stämmer kan du använda den färdiga kolumnen som resultat för tillgångsantal. Behåll regeln att den primära referensen har företräde, bevara meningsfulla nollvärden och markera etiketter som saknas i båda referenserna med Finns inte i listan så att du medvetet kan komplettera referensdata.

Källor