Mogelijk weet u niet hoe u de OFFSET-functie in Excel moet gebruiken

Wist u dat de OFFSET-functie in Microsoft Excel u kan helpen bij veel andere dingen, niet alleen bij celverwijzingen? Hieronder vindt u unieke manieren om OFFSET in Excel te gebruiken die u misschien niet kent.

De OFFSET-functie van Microsoft Excel is een flexibel en krachtig hulpmiddel. Vanwege de complexiteit en het aantal vergelijkbare functies wordt deze Excel-functie helaas onderbenut.

Deze relatief eenvoudige functie kan gebruikers helpen tijd en moeite te besparen. Het vereenvoudigt het verkrijgen van nauwkeurige gegevenspunten uit een goed gesorteerde masterdataset. Het maakt ook eenvoudig gedetailleerde rapporten, interactieve visualisaties en complexe dashboards mogelijk.

Maak een gegevensset op maat

Een van de meest voorkomende toepassingen van de OFFSET-functie in Excel is het verzamelen van aanpasbare gegevenssets. Bij een grote dataset kan het zijn dat men slechts een klein deel van de data tegelijk in beschouwing wil nemen.

Als u bijvoorbeeld een geautomatiseerd rapport van de verkooptotalen van medewerkers wilt verstrekken, moet u het totaal voor een bepaalde week of weken verkrijgen.

Het schrijven van een andere SOM-functie voor elk totaal kan vrij eenvoudig zijn. Het gebruik van de OFFSET-functie zal echter gemakkelijker te gebruiken en sneller te hergebruiken zijn.

Voer eerst de datum in waarmee u wilt beginnen in cel B6. Voer vervolgens het aantal dagen in waarop u het rapport in B7 wilt uitvoeren. Gebruik vervolgens de eenvoudige functies SOM en GEMIDDELDE om meer gegevens voor elke werknemer over de geselecteerde periode te verkrijgen:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Het aanpassen van de startdatum of -tijd is net zo eenvoudig als het bewerken van een cel, maar u kunt groepen gegevens veel gemakkelijker slepen en de grootte ervan wijzigen.

Maakt het mogelijk om aangepaste rapporten te maken

Gebruik dezelfde methode als hierboven om een ​​compleet rapport te maken door variabelen vrij eenvoudig aan te passen. U kunt ervoor zorgen dat het eerste argument in de OFFSET-functie naar een ander blad verwijst. Hierdoor kunt u gegevensinvoer en rapportage scheiden.

De bovenstaande actie maakt het ook mogelijk om geselecteerde stukjes gegevens van het ene werkblad naar het andere te slepen. Bovendien kunt u alleen informatie weergeven die relevant is voor het huidige rapport. Het opzetten van een rapport dat dagelijkse totalen toont met dezelfde gegevens van vroeger is vrij eenvoudig. U kunt dit doen zonder gegevens op te halen voor niet-gerelateerde datums.

De volgende offsetfunctie, geplaatst in cel B5, haalt het aantal dagen op dat is opgegeven in B2. Het begint op de week die is geselecteerd in B1 en toont alleen gegevens voor de relevante periode:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

Het is mogelijk om de invoer in B1 en B2 te beperken en in Excel een vervolgkeuzelijst te maken met beschikbare weken met behulp van gegevensvalidatie.

Paginering van spreadsheets

De OFFSET-functie kan ook eenvoudige paginering van spreadsheets mogelijk maken, een taak die vaak wordt geassocieerd met het gebruik van het tabblad Excel Developer. Dit kan het verwerken van grote groepen gegevens eenvoudiger en effectiever maken.

Wanneer u werkt met spreadsheets die maximaal 30.000 rijen met gegevens bevatten, kan het lastig zijn om alle gegevens in één keer door te nemen. Maak een vervolgkeuzemenu dat een getalbereik van 1 tot 300 accepteert, zodat u snel gegevens per pagina kunt scheiden met 100 resultaten per pagina.

Het ankerpunt van de OFFSET-functie is altijd in de bovenste gegevenscel geplaatst. Het tweede argument is echter de verticale offset. Deze waarde is gelijk aan het geselecteerde paginanummer min 100. Offset is horizontaal, het derde argument kan op 0 worden gezet.

Het 4e argument is ingesteld op 100, het 5e argument is het aantal kolommen in de dataset. Deze methode maakt het splitsen en beheren van gegevenstabellen uiterst eenvoudig.

Hieronder ziet u een voorbeeld van gepagineerde gegevens. Het bevat 500 stukjes informatie opgenomen door een sensor op het ene blad en een gepagineerd rapport op het andere blad.

Het rapport maakt gebruik van gegevensvalidatie in cel B1 om een ​​vervolgkeuzemenu met pagina's erin te maken. Met de volgende uitdrukking in cel A3 wordt de hoofdtekst van het rapport gemaakt:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Hierboven vindt u enkele manieren om de offsetfunctie in Excel te gebruiken die maar weinig mensen kennen, maar die erg handig zijn, vooral als u grote hoeveelheden gegevens moet verwerken.

Ik hoop dat het artikel nuttig voor je is.