Antalet tillgängliga objekt för filtrering är begränsat. Excel kan inte filtrera kolumner där antalet objekt överstiger 999 (inte antalet rader).
Använd avancerat filter för att filtrera när det finns mer än 999 objekt.
För att skapa ett avancerat filter använder vi funktionerna "OFFSET" och "COUNTA" i Microsoft Excel.
RIKT: Det returnerar antalet celler som innehåller värden.
Syntax för funktionen "COUNTA": = COUNTA (värde1, värde2, värde3 ….)
Exempel: I intervall A1: A5 innehåller cellerna A2, A3 och A5 värdena och cellerna A1 och A4 är tomma. Välj cell A6 och skriv formeln-
= COUNTA (A1: A5) funktionen återkommer 3
OFFSET: Det returnerar en referens till ett intervall som förskjutits ett antal rader och kolumner från ett annat område eller en cell.
Syntax för OFFSET -funktion: = OFFSET (referens, rader, kolumner, höjd, bredd)
Referens:- Detta är cellen eller intervallet från vilket du vill kompensera.
Rader och kolumner att flytta: - Antalet rader du vill flytta från startpunkten och båda dessa kan vara positiva, negativa eller noll.
Höjd och bredd: - Detta är storleken på intervallet du vill returnera. Detta är ett valfritt fält.
Låt oss ta ett exempel för att förstå funktionen Offset i Excel.
Vi har data i intervallet A1: D10. Kolumn A innehåller produktkod, kolumn B innehåller kvantitet, kolumn C innehåller per produktkostnad och kolumn D innehåller total kostnad. Vi måste returnera värdet av cell C5 i cell E2.
För att få det önskade resultatet måste vi följa nedanstående steg.
- Markera cellen E2 och skriv formeln.
- = OFFSET (A1,4,2,1,1)och tryck på Enter på tangentbordet.
- Funktionen returnerar värdet för cell C5.
I det här exemplet måste vi få värdet från cellen C5 till E2. Vår referenscell är den första cellen i intervallet som är A1 och C5 är 4 rader nedan och 2 kolumner till höger från A1. Därför är formeln = OFFSET (A1,4,2,1,1) eller = OFFSET (A1,4,2) (eftersom 1,1 är valfritt).
Låt oss nu ta ett exempel för att hämta det sista värdet i en dynamisk lista.
Vi har landnamn i en rad. Om vi nu lägger till fler länder i den här listan bör den vara tillgänglig i rullgardinsmenyn automatiskt.
För att förbereda avancerat filter, följ nedanstående steg:-
- Markera cellen B2.
- Gå till fliken Data, välj Datavalidering i gruppen Dataverktyg.
- Dialogrutan "Datavalidering" visas. På fliken "Inställningar" väljer du "Anpassad" från rullgardinsmenyn Tillåt.
- Formelrutan aktiveras.
- Skriv formeln i den här rutan.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Klicka på OK.
- I detta skede är den senaste uppdaterade cellen A11.
- För att kontrollera om datavalideringen fungerar korrekt, lägg till ett stadsnamn i cell A12.
Så snart du lägger till en post i A12 läggs den till i rullgardinsmenyn.
Det är så vi kan lägga till fler poster än 999 objekt i Microsoft Excel.