Excel-life hacking voor de betrokkenen bij de rapportage en gegevensverwerking

Renat is niet de eerste keer dat een gast auteur spreekt over Layfhakere. Eerder publiceerden we een uitstekend materiaal van hem over hoe u een training plan te maken: belangrijkste boek en online bronnen, alsook incrementele algoritme het creëren van een opleidingsplan.

Dit artikel bevat een aantal eenvoudige technieken die hulp vereenvoudigen het werk in Excel. ze zijn vooral handig voor diegenen die betrokken zijn bij het beheer van de rapportage, bereidt een verscheidenheid aan analytische rapporten op basis van de aanvoer van 1C rapporten en andere vormen van deze presentaties en diagrammen voor leiderschap. Doe niet alsof een absolute noviteit zijn - in een of andere vorm, deze methoden zeker besproken in forums of als bedoeld in artikel.

Eenvoudig alternatief voor VERT.ZOEKEN en HORIZ.ZOEKEN, indien de vereiste waarden niet in de eerste kolom van de tabel: VIEW, INDEX + MATCH

VLOOKUP (VLOOKUP) En PGR (HLOOKUP) werken alleen als de streefwaarden zijn in de eerste kolom of rij van de tabel waaruit u gegevens wilt krijgen.

In andere gevallen, zijn er twee opties:

1. Gebruik VIEW-functie (ZOEKEN).
   Ze heeft de volgende syntaxis: KIJKEN (lookup_value Zoeken vector; vektor_rezultata). Maar voor haar goed te laten werken moet je bereik waarden Zoeken vector Ze werden gesorteerd in oplopende volgorde:
2. MATCH een combinatie van functies (MATCH) en een index (INDEX).
   MATCH functie geeft het serienummer van het element in de array (met de hulp die u kunt vinden, in een rij van de tabel het gewenste element) en functie INDEX retourneert een array-element met een voorafbepaald aantal (waarvan we weten te functioneren MATCH).Syntax opties:
   • MATCH (lookup_value massiv_poiska; match_type) - in ons geval, hebben we een soort vergelijking "exact matching", dat overeenkomt met het nummer 0.
   • INDEX (matrix; rij_getal; [Kolom_getal]). In dit geval is het niet noodzakelijk het aantal kolom op de array bestaat uit een enkele lijn.

Hoe snel naar de lege cellen in de lijst te vullen

De taak - om de cellen te vullen met de waarden in de kolom boven (afhankelijk staan ​​in elke rij van de tabel, niet alleen in het eerste blok rij boeken over het onderwerp):

Selecteer de kolom "Subject", druk op de band in de groep van "Thuis" button "Zoek en selecteer» → «Markeer de groep cellen» → «Blank cel "en beginnen met het invoeren van de formule (dwz zet een gelijkteken) en verwijst naar de cel van bovenaf, door eenvoudig op de pijl keyboard. Druk vervolgens op Ctrl + Enter. Daarna kunnen de gegevens worden opgeslagen als een waarde, omdat de formules zijn niet meer nodig:

Hoe kan ik fouten in de formule vinden

Berekening afzonderlijke deelformule

Het complex met formule (waarbij als functieargumenten gebruik te begrijpen functie, dat wil zeggen, sommige functies zijn ingebed in de andere), of te vinden in het een bron van fouten, vaak moeten berekenen deel ervan. Er zijn twee eenvoudige manieren:

1. Rechts van de formule in de formulebalk berekenen, selecteert het onderdeel en druk F9:
   
   In dit voorbeeld, was er een probleem met de functie SEARCH (SEARCH) - waren er gemengde up argumenten. Het is belangrijk te onthouden dat als je niet de berekening van de functie te annuleren en druk op Enter, dan zal de berekende portie nummer blijven.
2. Druk op de "Bereken de formule" in de groep "formule" op de band:
   
   
   In het venster verschijnt, kunt u de formule stap voor stap berekenen en bepalen in welk stadium en in welke functie fout (indien aanwezig):
   

Hoe om te bepalen wat bepaalt die formule of waarnaar wordt verwezen

Om te bepalen welke van de cellen is afhankelijk van de formule in de groep van de "formule" in het lint op de knop "Broncellen":

Er zijn pijlen waarvan het resultaat afhangt.

Als het symbool op het geselecteerde beeld in het rood verschijnt, is de formule hangt af van cellen die zich in andere lijsten of in andere boeken:

Er op te klikken, kunnen we precies zien waar de invloed van de cel of het bereik:

Naast de "Broncellen" Er is een knop "gezinsleden", werken op dezelfde manier: toont pijlen uit de actieve cel met de formule cellen die afhankelijk zijn.

Button "Verwijder pijlen" gelegen in hetzelfde blok, maakt het mogelijk om Precedents, ten laste, of beide types in één keer verwijderen pijlen:

Hoe kan het bedrag (aantal, gemiddelde) waarden van cellen met meerdere vellen vinden

Laten we zeggen dat je meerdere gelijksoortige blad met de gegevens die u wilt toevoegen, vinden of verwerkt op een andere manier hebben:

Om dit te doen, de cel waarin u het resultaat te zien, voert u de standaardformule, zoals SOM (SUM), en voer het argument, gevolgd door een dubbele punt en de achternaam van de eerste plaat uit de lijst met bladen die u nodig hebt proces:

Je zal de hoeveelheid cel adres B3 ontvangen met lakens "Data1", "Data2", "data3":

Dergelijke adressering voor platen aangebracht achtereenvolgend. De syntaxis is: FEATURE = (pervyy_list: posledniy_list verwijzing naar het bereik!).

Hoe automatisch bouwen van een formulaic zinnen

Met behulp van de basisprincipes van het werken met tekst in Excel en een paar eenvoudige functies, kunnen worden bereid formulaic zinnen voor rapporten. Een aantal principes van het werk met de tekst:

• tekst combineren met de Mark & ​​(kan zijn functie CONCATENATE (TEKST.SAMENVOEGEN vervangen), maar dit is niet veel gebruik).
• niet altijd - De tekst wordt altijd tussen aanhalingstekens, de celverwijzingen met de tekst geschreven.
• Om het speciale teken "quotes" te krijgen, met behulp van karakter-functie (CHAR) argument 32.

Voorbeeld creëren template uitdrukkingen gebruikt formules:

resultaat:

In dit geval, naast de functie SYMBOL (CHAR) met de functie IF (de aanhalingstekens weergegeven) (IF), waardoor de tekst veranderen Afhankelijk van of er een positieve trend in de verkoop en de functie van de tekst (TEXT), waardoor een aantal weer te geven in een formaat. De syntax wordt hieronder beschreven:

TEXT (waarde; formaat)

Het formaat wordt opgegeven tussen aanhalingstekens op dezelfde manier als wanneer u een aangepast formaat in de ingevoerde "Cellen opmaken."

U kunt meer complexe teksten te automatiseren. In mijn praktijk was om lang te automatiseren, maar de routine opmerkingen over het beheer van rekeningen in het formaat "cijfer viel / rose bij de XX op het plan vooral te wijten aan de stijging / daling van de FAKTORA1 XX, stijging / daling van de FAKTORA2 YY... »met de veranderende lijst factoren. Als u een dergelijke opmerking te vaak en het proces van het schrijven kan algorithmization - de moeite waard eens verbaasd maken een formule of macro die u zal redden van ten minste een deel van het werk.

Hoe de gegevens in elke cel op te slaan na de fusie

Bij het combineren van de cellen behield slechts één waarde. Excel waarschuwt u wanneer u probeert samen te voegen cellen:

Dus, wanneer je een formule die afhankelijk is van elke cel had, zal het ophouden te werken na hun vakbond (bug # N / A in lijnen 3-4 voorbeelden):

Samenvoegen cellen, met behoud van de gegevens in elk van hen (misschien een formule, zoals in dit voorbeeld, de samenvatting; je zou willen cellen samenvoegen, maar alle gegevens in de toekomst te houden, of om hun bedoelingen te verbergen), combineren een cel op blad, selecteer ze en gebruik dan het commando "Format Painter" transfer formaat op de cellen die je nodig hebt en combineren:

Hoe maak je een samenvatting van verschillende databronnen te bouwen

Als u het bouwen van een geconsolideerde uit meerdere databronnen, is het noodzakelijk om toe te voegen aan de band of de werkbalk Snelle toegang "Wizard Draaitabel en grafieken" waarin sprake is van een dergelijke optie.

U kunt dit als volgt: "Bestand» → «Instellingen» → «werkbalk Snelle toegang» → «Alle het team» → «Wizard Draaitabel en diagrammen» → «Toevoegen»:

Daarna zal de band een bijbehorende pictogram te klikken op waardoor de toevoeging van de meester:

Als je erop klikt, verschijnt een dialoogvenster:

Daarin moet u "Een paar bands van consolidatie" te selecteren en klik op "Next". In de volgende paragraaf, kunt u de optie "Create een enkele pagina veld" of "Maak een pagina veld." Wilt u zelf op de proppen komen met een naam voor elk van de gegevensbronnen - naar het tweede item te selecteren:

In het volgende vak, voeg alle van de marges op basis waarvan een samenvatting te bouwen, en vraag hen de namen:

Na dat punt zal een draaitabelrapport in het laatste dialoogvenster hosten - op een bestaand of een nieuw werkblad:

Rapport Draaitabel klaar. Het filter "Pagina 1" kunt u kiezen slechts een van de gegevensbronnen, indien nodig:

Hoe kan ik het aantal keren dat de tekst te berekenen in de tekst van A B ( «MTS tarief SuperMTS" - twee exemplaren van de afkorting MTS)

In dit voorbeeld, kolom A, zijn er een paar regels tekst, en onze taak - om uit te vinden hoe vaak elk van hen voldoet aan de vereiste tekst in de cel E1:

Om dit probleem op te lossen, kunt u een complexe formule, bestaande uit de volgende functies:

1. LEN (LEN) - berekent de lengte van de tekst, één argument - de tekst. Bijvoorbeeld: LEN ( "machine") = 6.
2. Plaatsvervanger (plaatsvervanger) - vervangt de tekenreeks een bepaalde tekst met een ander. Syntax: plaatsvervanger (tekst; star_tekst; nov_tekst). Bijvoorbeeld: SUBSTITUTE ( "auto", "auto", "") = "mobile".
3. UPPER (BOVEN) - vervangt alle tekens in een tekenreeks naar hoofdletters. Het enige argument - de tekst. Voorbeeld: HOOG ( "machine") = "MACHINE". Deze functie zal nodig zijn om het zoeken niet hoofdlettergevoelig te maken. Immers, UPPER ( "Machine") = UPPER ( "Machine")

Een bepaald item van een tekenreeks naar een ander te vinden, moet je al zijn gebeurtenissen in het origineel te verwijderen en vergelijk de lengte van de string van het origineel:

LEN ( "Tariff Super MTS MTS") - LEN ( "Super Rate") = 6

En dan delen het verschil door de lengte van de lijn, waren we op zoek naar:

6 / LEN ( "MTS") = 2

Dat is het dubbele van de lijn "MTS" is in het origineel.

Er moet nog worden geschreven in de taal van het algoritme formules (aangeduid als "tekst" de tekst die we willen binnenvaren en "gewenst" - het aantal keren dat we geïnteresseerd zijn in):

= (LEN (tekst) -DLSTR (pas (HOOG (tekst) HOOG (gezochte); ""))) / LEN (gezochte)

In ons voorbeeld, de formule is als volgt:

= (LEN (A2) -DLSTR (SUBSTITUEREN (HOOG (A2), HOOG ($ E $ 1); ""))) / LEN ($ E $ 1)