Internetcompetitie voor jongeren is mijn alternatief. Volledig Russische internetwedstrijd voor jongeren “Mijn alternatief

Goedemiddag, beste Habro-bewoners!

Van tijd tot tijd hebben sommigen (en misschien meer dan sommigen) van ons te maken met taken waarbij kleine hoeveelheden gegevens worden verwerkt, variërend van het samenstellen en analyseren van een huishoudbudget tot berekeningen voor werk, studie, enz. Misschien wel de meest geschikte tool hiervoor is Microsoft Excel (of misschien andere analogen, maar ze komen minder vaak voor).

De zoekopdracht leverde me slechts één artikel over Habré op over een soortgelijk onderwerp - "Talmoed gebruikt formules in Google SpreadSheet". Het geeft een goede beschrijving van de basiszaken voor het werken in Excel (hoewel het niet 100% over Excel zelf gaat).

Nadat we een bepaalde pool van verzoeken/taken hadden verzameld, ontstond het idee om deze te typeren en mogelijke oplossingen voor te stellen (hoewel niet allemaal mogelijk, maar die snel resultaten opleverden).

We zullen het hebben over het oplossen van de meest voorkomende problemen die gebruikers tegenkomen.

De beschrijving van de oplossingen is als volgt opgebouwd: er wordt een casus gegeven met de initiële taak, die geleidelijk ingewikkelder wordt, en voor elke stap wordt een gedetailleerde oplossing met uitleg gegeven. De namen van de functies zullen in het Russisch worden gegeven, maar de originele naam in het Engels zal bij de eerste vermelding tussen haakjes worden vermeld (aangezien, volgens de ervaring, de overgrote meerderheid van de gebruikers de Russische versie heeft geïnstalleerd).

Case_1: Logische functies en bijpassende functies
“Ik heb een reeks waarden in een tabel en het is noodzakelijk dat wanneer aan een bepaalde voorwaarde/reeks voorwaarden wordt voldaan, een bepaalde waarde wordt weergegeven” (c) Gebruiker

De gegevens worden meestal in tabelvorm weergegeven:

Voorwaarde:

• als de waarde in de kolom "Aantal" groter is dan 5,
• dan moet u de waarde “Geen bestelling vereist” weergeven in de kolom “Resultaat”,

De formule "ALS" zal ons hierbij helpen, die verwijst naar logische formules en in de oplossing alle waarden kan produceren die we vooraf in de formule schrijven. Houd er rekening mee dat eventuele tekstwaarden met aanhalingstekens worden geschreven.

De syntaxis van de formule is als volgt:
ALS(logische_expressie, [waarde_als_waar], [waarde_als_onwaar])

• Logische expressie is een expressie die resulteert in TRUE of FALSE.
• Waarde_if_true - de waarde die wordt afgedrukt als de logische expressie waar is
• Waarde_if_false - de waarde die wordt afgedrukt als de Booleaanse expressie onwaar is

Formulesyntaxis voor oplossing:

=IF(C5>5, “Geen bestelling vereist”, “Bestelling vereist”)

Bij de uitvoer krijgen we het resultaat:

Het komt voor dat de voorwaarde complexer is, bijvoorbeeld de vervulling van 2 of meer voorwaarden:

• als de waarde in de kolom 'Aantal' groter is dan 5 en de waarde in de kolom 'Type' 'A' is

In dit geval kunnen we ons niet langer beperken tot het gebruik van alleen de “IF”-formule; we moeten een andere formule aan de syntaxis ervan toevoegen. En dit zal een andere logische formule “EN” zijn.
De syntaxis van de formule is als volgt:
AND(booleaanse_waarde1, [booleaanse_waarde2], ...)

• Booleaanse_waarde1-2, enz. - een te testen toestand, waarvan de evaluatie resulteert in de waarde TRUE of FALSE

Het resultaat uitvoeren in cel D2:

=ALS(EN(C2>5,B2=“A”),1,0)

Door een combinatie van twee formules te gebruiken, vinden we dus een oplossing voor ons probleem en krijgen we het resultaat:

Laten we proberen de taak ingewikkelder te maken - een nieuwe voorwaarde:

• als de waarde in de kolom 'Aantal' 10 is en de waarde in de kolom 'Type' 'A' is
• of de waarde in de kolom Hoeveelheid is groter dan of gelijk aan 5 en de waarde Type is B
• dan moet u de waarde “1” weergeven in de kolom “Resultaat”, anders “0”.

De syntaxis van de oplossing is als volgt:
Het resultaat uitvoeren in cel D2:

=ALS(OF(EN(C2=10,B2=“A”); EN(C2>=5,B2=“B”)),1,0)

Zoals u aan de invoer kunt zien, bevat de IF-formule één OR-voorwaarde en twee EN-voorwaarden. Als ten minste één van de voorwaarden van het 2e niveau de waarde “TRUE” heeft, wordt het resultaat “1” weergegeven in de kolom “Resultaat”, anders is het “0”.
Resultaat:

Laten we nu verder gaan met de volgende situatie:
Laten we ons voorstellen dat, afhankelijk van de waarde in de kolom ‘Voorwaarde’, een bepaalde voorwaarde moet worden weergegeven in de kolom ‘Resultaat’ hieronder, de overeenkomst tussen de waarden en het resultaat;
Voorwaarde:

• 1 = EEN
• 2 = B
• 3 = B
• 4 = G

Wanneer u een probleem oplost met de functie "ALS", is de syntaxis als volgt:

=ALS(A2=1,“A”, ALS(A2=2,“B”, ALS(A2=3,“C”, ALS(A2=4,“D”,0))))

Resultaat:

Zoals u kunt zien, is het schrijven van een dergelijke formule niet alleen niet erg handig en omslachtig, maar kan het ook enige tijd duren voordat een onervaren gebruiker deze kan bewerken in geval van een fout.
Het nadeel van deze aanpak is dat deze toepasbaar is voor een klein aantal omstandigheden, omdat ze allemaal handmatig moeten worden ingevoerd en onze formule moet worden “opgeblazen” tot grote maten, maar de aanpak onderscheidt zich door een volledige “omnivoriteit” van waarden ​en veelzijdigheid in gebruik.

Alternatieve oplossing_1:
Gebruik de CHOOSE-formule
Functie syntaxis:
SELECT(indexnummer, waarde1, [waarde2], ...)

• Indexnummer - het nummer van het geselecteerde waardeargument. Het indexnummer moet een getal tussen 1 en 254 zijn, een formule of een verwijzing naar een cel die een getal tussen 1 en 254 bevat.
• Waarde1, waarde2,... - een waarde van 1 tot 254 waardeargumenten, waaruit de functie “SELECT” met behulp van het indexnummer de uit te voeren waarde of actie selecteert. Argumenten kunnen getallen, celverwijzingen, specifieke namen, formules, functies of tekst zijn.

Bij gebruik voeren we onmiddellijk de resultaten van de voorwaarden in, afhankelijk van de opgegeven waarden.
Voorwaarde:

• 1 = EEN
• 2 = B
• 3 = B
• 4 = G

Syntaxis van formule:

=KEUZE(A2, “A”, “B”, “C”, “D”)

Het resultaat is vergelijkbaar met de bovenstaande IF-functieketenoplossing.
Bij het toepassen van deze formule gelden de volgende beperkingen:
In cel “A2” (indexnummer) kunnen alleen getallen worden ingevoerd en de resultaatwaarden worden in oplopende volgorde weergegeven van 1 tot 254 waarden.
Met andere woorden, de functie werkt alleen als cel “A2” getallen van 1 tot 254 in oplopende volgorde bevat, en dit legt bepaalde beperkingen op bij het gebruik van deze formule.
Die. als we willen dat de waarde “G” wordt weergegeven bij het opgeven van het getal 5,

• 1 = EEN
• 2 = B
• 3 = B
• 5 = G

dan heeft de formule de volgende syntaxis:
Het resultaat uitvoeren in cel B2:

=KEUZE(A31, “A”, “B”, “C”, “D”)

Zoals u kunt zien, moeten we de waarde “4” in de formule leeg laten en het resultaat “G” overbrengen naar het serienummer “5”.

Alternatieve oplossing_2:
Hier komen we bij een van de meest populaire Excel-functies, waarvan de beheersing van elke kantoormedewerker automatisch een “ervaren Excel-gebruiker” /sarcasme/ maakt.
Syntaxis van formule:
VERT.ZOEKEN(opzoekwaarde, tabel, kolomnummer, [interval_opzoeken])

• Zoek_waarde – de waarde waarnaar door de functie wordt gezocht.
• Een tabel is een bereik van cellen die gegevens bevatten. In deze cellen zal de zoekopdracht plaatsvinden. Waarden kunnen tekst, numeriek of Booleaans zijn.
• Kolomnummer - het nummer van de kolom in het argument “Tabel” waarvan de waarde wordt afgeleid als er een overeenkomst is. Het is belangrijk om te begrijpen dat de kolommen niet langs het algemene werkbladraster (A.B,C,D, enz.) worden geteld, maar binnen de array die is opgegeven in het argument "Tabel".
• Interval_lookup - bepaalt of de functie een exacte overeenkomst of een geschatte overeenkomst moet vinden.

Belangrijk: de functie “VERT.ZOEKEN” zoekt alleen naar een overeenkomst op basis van het eerste unieke record. Als de gezochte_waarde meerdere keren aanwezig is in het argument “Tabel” en verschillende waarden heeft, dan zal de functie “VERT.ZOEKEN” alleen de EERSTE overeenkomst vinden, de resultaten voor alle andere overeenkomsten wordt het gebruik van de formule “VLOOKUP” (VLOOKUP) geassocieerd met een andere benadering van het werken met gegevens, namelijk de vorming van “directories”.
De essentie van de aanpak is het creëren van een "map" van de correspondentie van het argument "Searched_value" met een specifiek resultaat, los van de hoofdarray, waarin de voorwaarden en de bijbehorende waarden worden geschreven:

Vervolgens wordt in het werkende deel van de tabel een formule opgeschreven met een link naar het eerder ingevulde naslagwerk. Die. in de directory in kolom “D” wordt de waarde uit kolom “A” gezocht en wanneer een match wordt gevonden wordt de waarde uit kolom “E” weergegeven in kolom “B”.
Syntaxis van formule:
Het resultaat uitvoeren in cel B2:

Resultaat:

Stel je nu een situatie voor waarin je gegevens uit de ene tabel naar de andere moet halen, en de tabellen zijn niet identiek. Zie voorbeeld hieronder

Het is te zien dat de rijen in de “Product”-kolommen van beide tabellen niet overeenkomen, maar dit vormt geen obstakel voor het gebruik van de functie “VLOOKUP”.
Het resultaat uitvoeren in cel B2:

Maar bij het oplossen ervan komen we een nieuw probleem tegen: bij het "uitrekken" van de formule die we naar rechts hebben geschreven van kolom "B" naar kolom "E", zullen we het argument "kolom_nummer" handmatig moeten vervangen. Dit is een arbeidsintensieve en ondankbare taak, daarom komt een andere functie ons te hulp: "COLUMN" (COLUMN).
Functie syntaxis:
KOLOM([link])

• Een verwijzing is een cel of celbereik waarvoor u het kolomnummer wilt retourneren.

Als u een record gebruikt zoals:

dan geeft de functie het nummer van de huidige kolom weer (in de cel waarin de formule is geschreven).
Het resultaat is een getal dat kan worden gebruikt in de functie “VLOOKUP”, die we zullen gebruiken en de volgende formule krijgen:
Het resultaat uitvoeren in cel B2:

=VERT.ZOEKEN($A3,$H$3:$M$6, KOLOM(),0)

De functie "COLUMN" bepaalt het nummer van de huidige kolom, dat zal worden gebruikt door het argument "Column_Number" om het nummer van de zoekkolom in de directory te bepalen.
Als alternatief kunt u de volgende constructie gebruiken:

In plaats van het getal “1” kunt u elk getal gebruiken (en niet alleen aftrekken, maar ook optellen bij de resulterende waarde) om het gewenste resultaat te verkrijgen, als u niet wilt verwijzen naar een specifieke cel in de kolom met het nummer dat we nodig hebben.
Het resulterende resultaat:

We blijven het onderwerp ontwikkelen en compliceren de voorwaarde: stel je voor dat we twee mappen hebben met verschillende gegevens over producten en dat we de waarden in de tabel moeten weergeven met het resultaat, afhankelijk van welk type map is aangegeven in de "Directory" kolom
Voorwaarde:

• Als het nummer 1 wordt aangegeven in de kolom "Directory", moeten de gegevens uit de tabel "Directory_1" worden gehaald. Als het nummer 2 is, vervolgens uit de tabel "Directory_2" in overeenstemming met de opgegeven maand

De oplossing die meteen in mij opkomt is de volgende:

=ALS($B3=1; VERT.ZOEKEN($A3,$G$3:$I$6; KOLOM()-1,0); VERT.ZOEKEN($A3,$K$3:$M$6; KOLOM()-1;0 ))

Pluspunten: de naam van de map kan van alles zijn (tekst, cijfers en hun combinatie), nadelen - het past niet goed als er meer dan 3 opties zijn.
Als telefoonboeknummers altijd cijfers zijn, is het zinvol om de volgende oplossing te gebruiken:
Het resultaat uitvoeren in cel C3:

=VERT.ZOEKEN($A3, SELECT($B3,$G$3:$I$6,$K$3:$M$6), KOLOM()-1,0)

Pluspunten: de formule kan maximaal 254 directorynamen bevatten. Nadelen - hun naam moet strikt numeriek zijn.
Resultaat voor formule met behulp van de SELECT-functie:

Bonus: VERT.ZOEKEN op basis van twee of meer kenmerken in het argument 'zoekwaarde'.
Voorwaarde:

• Laten we ons voorstellen dat we, zoals altijd, een array met gegevens in tabelvorm hebben (zo niet, dan brengen we de gegevens ernaar, we moeten waarden uit de array halen op basis van bepaalde kenmerken en deze in een andere tabelvorm plaatsen); .

Beide tabellen worden hieronder weergegeven:

Zoals uit de tabelformulieren blijkt, heeft elk artikel niet alleen een naam (die niet uniek is), maar behoort het ook tot een specifieke klasse en heeft het een eigen verpakkingsmogelijkheid.
Met behulp van een combinatie van naam en klasse en verpakking kunnen we hiervoor een nieuw kenmerk aanmaken, in de tabel met de gegevens maken we een extra kolom “Extra kenmerk” aan, die we vullen met de volgende formule:

Met behulp van het “&”-symbool combineren we drie kenmerken tot één (het scheidingsteken tussen woorden kan van alles zijn, of helemaal niet, het belangrijkste is om een ​​vergelijkbare zoekregel te gebruiken)
Een analoog van de formule kan de functie "CONCATENATE" zijn, in welk geval deze er als volgt uit zal zien:

=SAMENVOEGEN(H3;"_";I3;"_";J3)

Nadat voor elk record in de gegevenstabel een extra attribuut is aangemaakt, gaan we verder met het schrijven van een zoekfunctie voor dit attribuut, die er als volgt uit zal zien:
Het resultaat uitvoeren in cel D3:

=IFOUT(VLOOKUP(A2&"_"&B2&"_"$G$2:$K$6,5,0),0)

In de functie “VERT.ZOEKEN” gebruiken we als argument “zoekwaarde” dezelfde combinatie van drie kenmerken (naam_klasse_verpakking), maar we nemen deze al in de tabel om te vullen en voeren deze rechtstreeks in het argument in (als alternatief kunnen we de waarde voor het argument in een extra kolom in de tabel die moet worden ingevuld, maar deze actie is niet nodig).
Ik herinner u eraan dat het gebruik van de functie "IFERROR" noodzakelijk is als de gewenste waarde niet wordt gevonden, en de functie "VLOOKUP" geeft ons de waarde "#N/A" (meer hierover hieronder).
Het resultaat staat op de onderstaande afbeelding:

Deze techniek kan voor een groter aantal kenmerken worden gebruikt, de enige voorwaarde is de uniciteit van de resulterende combinaties; als hieraan niet wordt voldaan, zal het resultaat onjuist zijn.

Case_3 Zoeken naar een waarde in een array, of wanneer VLOOKUP ons niet kan helpen

Laten we een situatie bekijken waarin we moeten begrijpen of de celarray de waarden bevat die we nodig hebben.
Taak:

• de kolom “Zoekvoorwaarde” bevat een waarde en u moet bepalen of deze aanwezig is in de kolom “Zoekmatrix”.

Visueel ziet alles er zo uit:

Zoals we kunnen zien, is de functie “VLOOKUP” hier machteloos, omdat We zijn niet op zoek naar een exacte match, maar eerder naar de aanwezigheid van de waarde die we nodig hebben in de cel.
Om het probleem op te lossen is het noodzakelijk om een ​​combinatie van verschillende functies te gebruiken, namelijk:
"ALS"
"ALS FOUT"
"LAGER"
"VINDEN"

Om iedereen op de hoogte te houden: we hebben 'ALS' al eerder besproken, dus laten we verder gaan met de functie 'ALS'.

IFERROR(waarde; fout_waarde)

• De waarde is het argument dat op fouten wordt gecontroleerd.
• Value_on_error - de waarde die wordt geretourneerd als er een fout optreedt bij het berekenen van de formule. De volgende soorten fouten zijn mogelijk: #N/A, #VALUE!, #REF!, #DIV/0!, #NUMBER!, #NAME? en #LEEG!.

Belangrijk: deze formule is bijna altijd vereist bij het werken met arrays met informatie en mappen, omdat Het komt vaak voor dat de waarde die u zoekt niet in de directory staat, en in dit geval retourneert de functie een foutmelding. Als er in een cel een fout wordt weergegeven en de cel is betrokken bij bijvoorbeeld een berekening, dan zal dat ook met een fout gebeuren. Bovendien kunnen aan cellen waarin de formule een fout retourneerde, verschillende waarden worden toegewezen, wat hun statistische verwerking vergemakkelijkt. Ook kunt u in geval van een fout andere functies uitvoeren, wat erg handig is bij het werken met arrays en waarmee u formules kunt bouwen, rekening houdend met nogal vertakte omstandigheden.

"LAGER"

• Tekst - tekst geconverteerd naar kleine letters.

Belangrijk: de functie "LOWER" vervangt geen tekens die geen letters zijn.
Rol in de formule: aangezien de functie “FIND” zoekt en rekening houdt met het hoofdlettergebruik van de tekst, is het noodzakelijk om alle tekst naar hetzelfde hoofdlettergebruik te converteren, anders is “thee” niet gelijk aan “thee”, enz. Dit is relevant als de registerwaarde geen voorwaarde is voor het zoeken en selecteren van waarden, anders kan de formule “LOWER” niet worden gebruikt, waardoor de zoekopdracht nauwkeuriger zal zijn.

Laten we nu de syntaxis van de FIND-functie eens nader bekijken.

FIND(zoektekst, bekeken_tekst, [startpositie])

• Zoek_tekst - de tekst die gevonden moet worden.
• Zoek_tekst - de tekst waarin u de gezochte tekst wilt vinden.
• Start_position - het teken van waaruit de zoekopdracht moet worden gestart. Het eerste teken in de tekst "view_text" is genummerd 1. Als het nummer niet is opgegeven, is het standaard 1.

De syntaxis van de oplossingsformule ziet er als volgt uit:
Het resultaat uitvoeren in cel B2:

=ALS(ALS(FIND(LIJN(A2), LIJN(E2),1),0)=0,“mislukt”,“bingo!”)

Laten we de logica van de formule stap voor stap analyseren:

1. LOWER(A2) – converteert het Search_Text-argument in cel A2 naar kleine letters
2. De FIND-functie begint te zoeken naar het geconverteerde argument Search_Text in de Search_Text-array, dat door de LOWER(E2)-functie wordt geconverteerd, ook naar kleine letters.
3. Als de functie een overeenkomst vindt, d.w.z. retourneert het serienummer van het eerste teken van het overeenkomende woord/waarde, de TRUE-voorwaarde in de “ALS”-formule wordt geactiveerd, omdat de resulterende waarde is niet nul. Als gevolg hiervan wordt in de kolom “Resultaat” de waarde “Bingo!” weergegeven.
4. Als de functie echter geen overeenkomst vindt, d.w.z. het serienummer van het eerste teken van het overeenkomende woord/de overeenkomende waarde wordt niet aangegeven en er wordt een fout geretourneerd in plaats van de waarde, de voorwaarde in de “IFERROR”-formule wordt geactiveerd en een waarde gelijk aan “0” wordt geretourneerd, die overeenkomt aan de FALSE-voorwaarde in de “ALS”-formule, omdat de resulterende waarde is “0”. Als gevolg hiervan wordt de waarde “mislukt” weergegeven in de kolom “Resultaat”.

Zoals te zien is in de bovenstaande afbeelding, vinden we dankzij de functies "LOW" en "FIND" de gewenste waarden, ongeacht het geval van de tekens en de locatie in de cel, maar we moeten aandacht besteden aan regel 5 .
De zoekterm is ingesteld op "111", maar de zoekarray bevat de waarde "1111111 cookies", maar de formule retourneert het resultaat "Bingo!" Dit gebeurt omdat de waarde “111” is opgenomen in de reeks waarden “1111111”, waardoor er een match wordt gevonden. Anders zal deze voorwaarde niet werken.

Case_4 Zoeken naar een waarde in een array op basis van verschillende voorwaarden, of wanneer VLOOKUP ons nog meer niet kan helpen

Laten we ons een situatie voorstellen waarin u een waarde moet vinden uit de “Tabel met het resultaat” in de tweedimensionale array “Directory” volgens verschillende voorwaarden, namelijk door de waarde “Naam” en “Maand”.
De tabelvorm van de taak ziet er als volgt uit:

Voorwaarde:

• Het is noodzakelijk om de gegevens met het resultaat in de tabel te plaatsen in overeenstemming met het samenvallen van de voorwaarden "Naam" en "Maand".

Om dit probleem op te lossen is een combinatie van de functies “INDEX” en “ZOEKEN” geschikt.

Syntaxis van de INDEX-functie

INDEX(matrix, rijnummer, [kolomnummer])

• Array - een celbereik waaruit waarden worden weergegeven als hun zoekvoorwaarden overeenkomen.
• Als de array slechts één rij of één kolom bevat, is respectievelijk het argument row_number of column_number optioneel.
• Als de array meer dan één rij en één kolom beslaat en er slechts één van de argumenten row_number en column_number wordt opgegeven, retourneert de functie INDEX een array die bestaat uit de gehele rij of de gehele kolom van het arrayargument.
• Lijnnummer - het nummer van de regel in de array waarvan u een waarde wilt retourneren.
• column_number - het nummer van de kolom in de array waaruit u een waarde wilt retourneren.

Met andere woorden: de functie retourneert uit de opgegeven array in het argument “Array” de waarde die zich bevindt op het snijpunt van de coördinaten die zijn opgegeven in de argumenten “Row_Number” en “Column_Number”.

MATCH-functiesyntaxis

VERGELIJKEN(opzoekwaarde, opzoekmatrix, [overeenkomsttype])

• Lookup_value is de waarde die overeenkomt met de waarden in het lookup_array-argument. Het argument lookup_value kan een waarde zijn (getal, tekst of Booleaanse waarde) of een verwijzing naar een cel die een dergelijke waarde bevat.
• Looked_array - het celbereik waarin de zoekopdracht wordt uitgevoerd.
• Match_type is een optioneel argument. Het getal is -1, 0 of 1.

De functie MATCH zoekt in een celbereik naar een opgegeven element en retourneert de relatieve positie van dat element in het bereik.
De essentie van het gebruik van een combinatie van de functies "INDEX" en "ZOEKEN" is dat we naar de coördinaten van waarden zoeken op basis van hun naam langs de "coördinatenassen".
De Y-as is de kolom 'Naam' en de X-as is de rij 'Maanden'.

Een deel van de formule:

VERGELIJKEN($A4,$I$4:$I$7,0)

retourneert het getal langs de Y-as, in dit geval is het gelijk aan 1, omdat de waarde "A" is aanwezig in het gezochte bereik en heeft een relatieve positie van "1" in dat bereik.
deel van de formule:

WEDSTRIJD(B$3,$J$3:$L$3,0)

retourneert #N/B omdat de waarde "1" valt niet binnen het bereik dat wordt bekeken.

We hebben dus de coördinaten ontvangen van het punt (1; #N/A) dat de functie "INDEX" gebruikt om te zoeken in het argument "Array".
De volledig geschreven functie voor cel B4 zou er als volgt uitzien:

=INDEX($J$4:$L$7, VERGELIJKEN($A4,$I$4:$I$7,0), VERGELIJKEN(B$3,$J$3:$L$3,0))

Als we de coördinaten kenden van de waarde die we nodig hadden, zou de functie er in wezen als volgt uitzien:

=INDEX($J$4:$L$7,1,#N.v.t.))

Omdat het argument “Kolom_Nummer” de waarde “#N/A” heeft, zal het resultaat voor cel “B4” overeenkomen.
Zoals uit het resulterende resultaat blijkt, komen niet alle waarden in de tabel met het resultaat overeen met het naslagwerk en als resultaat zien we dat sommige waarden in de tabel worden weergegeven als “#N/A” , wat het moeilijk maakt om de gegevens voor verdere berekeningen te gebruiken.
Resultaat:

Om dit negatieve effect te neutraliseren, gebruiken we de functie “IFERROR”, waarover we eerder hebben gelezen, en vervangen we de waarde die bij een fout wordt geretourneerd door “0”, waarna de formule er als volgt uit zal zien:

=IFOUT(INDEX($J$4:$L$7, VERGELIJKEN($A4,$I$4:$I$7,0), VERGELIJKEN(B$3,$J$3:$L$3,0)),0)

Resultaat demonstratie:

Zoals u op de afbeelding kunt zien, interfereren de “#N/A”-waarden niet langer met onze daaropvolgende berekeningen met behulp van de waarden in de resultatentabel.

Case_5 Een waarde vinden in een reeks getallen

Laten we ons voorstellen dat we een bepaald teken moeten geven aan getallen die binnen een bepaald bereik vallen.
Voorwaarde:
Afhankelijk van de kosten van het product, moet het in een bepaalde categorie worden ingedeeld
Als de waarde binnen het bereik ligt

• Van 0 tot 1000 = A
• Van 1001 tot 1500 = B
• Van 1501 tot 2000 = B
• Van 2001 tot 2500 = G
• Ruim 2501 = D

De functie ZOEKEN retourneert een waarde uit een rij, kolom of array. De functie heeft twee syntactische vormen: vector- en array-vorm.

ZOEKEN(opzoekwaarde, opzoekvector, [resultaatvector])

• lookup_value is de waarde waarnaar de functie ZOEKEN zoekt in de eerste vector. Opzoekwaarde kan een getal-, tekst-, boolean-, naam- of waardereferentie zijn.
• Watch_vector is een bereik dat uit één rij of één kolom bestaat. De waarden in het argument lookup_vector kunnen tekst, getallen of Booleaanse waarden zijn.
• De waarden in het argument lookup_vector moeten in oplopende volgorde staan: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; anders retourneert de functie ZOEKEN mogelijk een onjuist resultaat. Kleine letters en hoofdletters worden als gelijkwaardig beschouwd.
• resultaat_vector is een bereik dat uit één rij of kolom bestaat. De resultaat_vector moet dezelfde grootte hebben als de lookup_vector.

=WEERGEVEN(E3,$A$3:$A$7,$B$3:$B$7)

De argumenten "View_vector" en "Result_vector" kunnen in arrayvorm worden geschreven - in dit geval hoeft u ze niet in een aparte tabel op een Excel-werkblad weer te geven.
In dit geval ziet de functie er als volgt uit:
Het resultaat uitvoeren in cel B3:
=WEERGEVEN(E3;(0;1001;1501;2001;2501);("A", "B", "C", "D", "D"))

Case_6 Sommatie van getallen op basis van kenmerken

Om getallen op te tellen op basis van bepaalde kenmerken, kunt u drie verschillende functies gebruiken:
SUMIF – somt op met slechts één attribuut
SUMIFS – sommen over meerdere kenmerken
SOMPRODUCT – som van meerdere kenmerken
Er is ook een optie om "SUM" en de matrixformulefunctie te gebruiken, wanneer de "SUM"-formule wordt verhoogd naar een array:
((=SOM(()*()))
maar deze aanpak is behoorlijk lastig en wordt volledig gedekt door de functionaliteit van de formule "SUMPRODUCT".
Voor meer details over de syntaxis "SUMPRODUCT":

SOMPRODUCT(matrix1, [matrix2], [matrix3],...)

• Array1 is de eerste array waarvan de componenten moeten worden vermenigvuldigd en vervolgens moeten de resultaten worden toegevoegd.
• Array2, array3… - van 2 tot 255 arrays, waarvan de componenten moeten worden vermenigvuldigd en vervolgens de resultaten moeten worden toegevoegd.

Voorwaarde:

• Vind de totale verzendkosten voor elk product voor een bepaalde periode:

Zoals blijkt uit de tabel met gegevens, is het, om de kosten te berekenen, noodzakelijk om de prijs met de hoeveelheid te vermenigvuldigen en de resulterende waarde, met toepassing van de selectievoorwaarden, over te dragen naar de tabel met het resultaat.
Met de SUMPROIZ-formule kunnen dergelijke berekeningen echter binnen de formule worden gemaakt.
Het resultaat uitvoeren in cel B4:

=SOMPRODUCT(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Laten we de formule in delen bekijken:
– stel de selectievoorwaarde in de kolom “Naam” van de tabel in met gegevens in de kolom “Naam” in de tabel met het resultaat

($K$3:$K$11>=B$3)*($K$3:$K$11 – we stellen een voorwaarde op basis van het tijdsbestek, de datum is groter dan of gelijk aan de eerste dag van de huidige maand, maar kleiner dan de eerste dag van de volgende maand. Op dezelfde manier staat de voorwaarde in de tabel met het resultaat, de array staat in de tabel met de gegevens.
– vermenigvuldig de kolommen “Aantal” en “Prijs” in de tabel met de gegevens.
Het onbetwiste voordeel van deze functie is de vrije volgorde van de opnamevoorwaarden; ze kunnen in elke volgorde worden geschreven, dit heeft geen invloed op het resultaat.
Resultaat:

Laten we nu de voorwaarde ingewikkelder maken en de vereiste toevoegen dat selectie voor de naam "cookies" alleen voorkomt in de klassen "klein" en "groot", en voor de naam "rollen" alles behalve de klasse "met jam":

Het resultaat uitvoeren in cel B4:

=SOMPRODUCT(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
Er is een nieuwe voorwaarde toegevoegd aan de formule voor het selecteren van cookies:

(($I$3:$I$11=“klein”)+($I$3:$I$11=“groot”))

– zoals u kunt zien, worden twee of meer voorwaarden in één kolom gescheiden in een aparte groep met behulp van het “+”-symbool en de voorwaarden tussen extra haakjes.
Er is ook een nieuwe voorwaarde toegevoegd aan de formule voor selectie op basis van rollen:

=SOMPRODUCT(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 “met jam”);($L$3:$L$11)*($K$3:$K$11))

Dit:

($I$3:$I$11<>"met jam")

– in feite was het in deze formule mogelijk om een ​​selectievoorwaarde te schrijven op dezelfde manier als bij het selecteren via cookies, maar dan zou je drie voorwaarden in de formule moeten vermelden, in dit geval is het gemakkelijker om een ​​uitzondering te schrijven - is niet gelijk aan “met jam” hiervoor gebruiken we de waarde "<>».
Als groepen kenmerken/klassen van tevoren bekend zijn, is het over het algemeen beter om ze in deze groepen te combineren, waardoor naslagwerken ontstaan, dan om alle voorwaarden in een functie te schrijven en deze op te blazen.
Resultaat:

Welnu, hier komen we aan het einde van onze korte handleiding, die in feite veel langer zou kunnen zijn, maar het doel was nog steeds om een ​​oplossing te bieden voor de meest voorkomende situaties, en niet om de oplossing te beschrijven voor specifieke (maar veel interessantere gevallen). ).
Ik hoop dat de handleiding iemand zal helpen bij het oplossen van problemen met Excel, want dit betekent dat mijn werk niet voor niets is geweest!

Bedankt voor uw tijd!

“Mijn Alternatief” is een nationaal jeugdproject gericht op het identificeren, aanmoedigen en verspreiden van de beste vormen van vrijetijdsbesteding voor jongeren die voldoen aan de principes van een gezonde levensstijl, het bevorderen van fysieke en sociale activiteit en de afwijzing van tabak, alcohol en drugs. Projectactiviteiten, waarbij rekening wordt gehouden met leeftijd, sociale, culturele, geslacht en andere specifieke kenmerken van interactie met de doelgroep, hebben verschillende niveaus van impact op het publiek: emotioneel, cognitief en gedragsmatig. Het project omvat evenementen die informatieve, propaganda-, educatieve en educatieve functies vervullen. Als onderdeel van het project wordt de All-Russische internetwedstrijd voor jeugdvrije tijd "My Alternative" gehouden. De doelstellingen van de wedstrijd zijn het motiveren van jonge mensen om een ​​gezonde levensstijl te leiden, het bevorderen van de afwijzing van tabak, alcohol en drugs, en het vestigen van de aandacht op een actieve levensstijl en sport door het identificeren en verspreiden van de beste vormen van vrijetijdsbesteding voor jongeren. De wedstrijd wordt in 3 fasen gehouden: de eerste fase is het verzamelen van applicaties en video's op de projectwebsite. Video's van de deelnemers vertellen over hun gezonde levensstijl, sport en hobby's, die worden gekenmerkt door een competitieve instelling, gedrevenheid en verlangen naar uitmuntendheid. De tweede fase is een open internetstemming, op basis van de resultaten waarvan 10 finalisten van de competitie worden bepaald. Nog eens 10 finalisten worden bepaald door de wedstrijdcommissie. De derde fase - de finalisten van de internetwedstrijd presenteren hun video's op het Jeugdfestival "Mijn Alternatief". Er worden 5 winnaars bepaald door de Competitiecommissie. Iedereen van 16 tot en met 44 jaar kan aan de Competitie deelnemen. Als onderdeel van het Jeugdfestival 'Mijn Alternatief' worden niet alleen de winnaars van de online competitie beloond, maar ook een concertprogramma met deelname van populaire artiesten en groepen.

Doelen

1. Het bevorderen van een gezonde levensstijl

Taken

1. Vorming van een jeugdgemeenschap die mensen verenigt die een actieve en gezonde levensstijl leiden
2. Het promoten van de waarden van een gezonde levensstijl onder jongeren

Rechtvaardiging van sociale betekenis

Het is duidelijk dat “levensstijlziekten” een zeer ernstige impact hebben, niet alleen op de indicatoren van de fysieke gezondheid van de Russische bevolking, maar ook op de kenmerken van de sociale, spirituele en morele sfeer. Het probleem heeft mondiale proporties aangenomen. Tegenwoordig vormt de afname van het gezondheidspotentieel van de jongere generatie een ernstig demografisch en bevolkingsprobleem. Het landelijke jongerenproject ‘Mijn Alternatief’ is een publiek initiatief en heeft een langetermijnkarakter. In vijf jaar tijd namen ruim drieduizend unieke videowerken van jongeren van 14 tot 44 jaar uit het hele land deel aan de wedstrijd. De geografie van de competitie is het breedst: meer dan duizend steden, dorpen en dorpen van Sevastopol tot Vladivostok. Ruim 100.000 mensen namen deel aan de stemming. In 2017 waren onder de winnaars van de wedstrijd bijvoorbeeld de broers uit Tula, die in een paar jaar tijd een dansbeweging in Tula van de grond af aan konden ontwikkelen tot een ongelooflijk populaire trend onder jongeren. Twee broers wilden gewoon alleen dansen en studeren, en nodigden vrienden, kennissen en gewoon gelijkgestemde mensen uit om zich bij hun team aan te sluiten. In slechts een paar jaar tijd hebben ze een school gecreëerd waar honderden jonge mensen uit Tula en de regio doorheen zijn gegaan, en het aantal mensen dat op dezelfde golflengte zit met hun broers groeit voortdurend. Kinderen en tieners zijn gepassioneerd door dansen en brengen hun liefde voor een actief en gezond tijdverdrijf bij al hun vrienden bij. In 2016 werd de eerste plaats ingenomen door een jonge man uit Zheleznogorsk, die erin slaagde een groot team van gelijkgestemde mensen te verzamelen, dat massale sportevenementen in heel Siberië organiseert. In 2015 was de winnaar van de wedstrijd een rolstoelgebruiker uit de noordelijke stad Nizjnevartovsk, die betrokken was bij powerlifting. En in 2014 betoverde de winnaar van de wedstrijd van Tolyatti het internetpubliek en de jury dankzij de video "I went to the taiga" en zijn Siberische huskyhonden. In zijn video zei hij dat hij dankzij zijn hobby - een team van sledehonden, waarmee hij deelneemt aan races, waarmee hij nu werkt en op expedities gaat in Noord-Karelië, zijn gewicht weer normaal heeft gemaakt. Het alternatief van een andere deelnemer aan de Competitie uit Sebastopol kreeg, dankzij zijn overwinning daarin, de steun van het stadsbestuur. Ook won een video over de hobby's van een andere deelnemer aan de wedstrijd de Russische etappe van de Jaguar Land Rover- en Virgin Galactic-competitie, met als gevolg dat hij Rusland ging vertegenwoordigen op het internationale podium in Groot-Brittannië. Iedereen die meedoet aan de Competitie zoekt niet naar gemakkelijke wegen en is een waar voorbeeld voor jongeren.

31 mei dit jaar In het Gostiny Dvor Exhibition Complex wordt een festival met verschillende jeugdactiviteiten en vrijetijdsactiviteiten gehouden "Mijn alternatief".

Hier kunnen festivalgasten officiële GTO-testen ondergaan in het DOSAAF-testcentrum in Rusland.

En op de locatie van het project “New Format Sports Center” – een multifunctionele geprefabriceerde sportbaan gemaakt van PAMPTRACK-composietblokken met verschillende geometrieën – zal het mogelijk zijn om deel te nemen aan een gezonde levensstijl in de vorm van nieuwe moderne jeugdsport en recreatie. Bewegingen langs de baan worden uitgevoerd op fietsen, loopfietsen, scooters, rolschaatsen en andere vormen op wielen zonder te trappen of van het oppervlak te duwen. De baan is door iedereen te gebruiken zonder leeftijdsbeperkingen, het opleidingsniveau maakt niet uit. Het is erg handig voor kinderen, omdat het het vermogen ontwikkelt om het evenwicht te bewaren (vanaf 2 jaar oud) en een positief effect heeft op de ontwikkeling van de hersenen. Er zullen instructeurs op de pompbaan zijn die iedereen uitrusting en beschermende uitrusting zullen verstrekken voor het proefrijden. Tussen de schaatssessies door worden er masterclasses en demonstratieoptredens van atleten gehouden.

IN 19:00 Op 31 mei vindt het hoofdevenement van het festival plaats: een galaconcert van de Muz-TV-zender, gewijd aan het eren van de finalisten en winnaars van de All-Russische internetwedstrijd "My Alternative", waarin de volgende artiesten en musical groepen zullen deelnemen: Artik & Asti, 5sta Family, Misha Marvin, DONI, Katya Lel, Alex Malinovsky, Marie Crimebreri, Artyom Kacher, Albina, VivA, Anastasia Spiridonova, Alla Reed en anderen.

De prijsuitreiking wordt bijgewoond door: absolute wereldkampioen boksen Konstantin Tszyu en bronzen medaillewinnaar van de Olympische Spelen van 2014, tweevoudig medaillewinnaar van de wereldkampioenschappen, tweevoudig Europees kampioen skeleton Elena Nikitina.

Volledig Russische internetwedstrijd voor vrijetijdsbesteding voor jongeren “Mijn alternatief” is een van de evenementen van het gelijknamige sociaal belangrijke project, gericht op het identificeren van de beste video over projecten, initiatieven en praktijken van verschillende vormen van jeugdvrije tijd die voldoen aan de principes van een gezonde levensstijl.

Competitie doelen– jongeren motiveren om een ​​gezonde levensstijl te leiden, een afwijzing van tabak, alcohol en drugs bevorderen, en ook de aandacht vestigen op een actieve levensstijl en sport door de beste vormen van vrijetijdsbesteding voor jongeren te identificeren en te verspreiden.

"Mijn alternatief"- dit is het belangrijkste feest van het jaar voor degenen die een actieve levensstijl leiden, sporten en weten wat echte extreme sporten zijn.

De competitie verloopt in drie fasen. De eerste fase - het verzamelen van applicaties en video's op de projectwebsite - www.ligazn.ru - eindigde op 25 februari 2018. Video's van de deelnemers vertellen over hun gezonde levensstijl, sport en hobby's, die worden gekenmerkt door een competitieve instelling, gedrevenheid en verlangen naar uitmuntendheid. In totaal is de tweede fase van de competitie bereikt 208 deelnemers van 134 nederzettingen Rusland. Total deed mee aan de wedstrijd 841 aanvragen.

De tweede etappe vond plaats met 19 maart tot 15 april 2018 in open formaat Internetstemmen, waarna 20 finalisten werden geïdentificeerd. Ook werden er 3 finalisten bepaald door de beslissing van de Competitiecommissie.

Tijdens de derde fase van de competitie, die zal plaatsvinden 31 mei 2018 in Moskou in Gostiny Dvor in Conferentiezaal nr. 1 van 10.00 – 13.00 uur presenteren de finalisten van de internetwedstrijd hun video’s op Jeugdfestival “Mijn Alternatief”. De 5 winnaars worden bepaald door de Competitiecommissie, waarin experts op het gebied van social adverteren, vertegenwoordigers van publieke organisaties en de media zitting zullen hebben.

De winnaars van de Competitie ontvangen diploma's en geldprijzen:

Ik plaats (1 winnaar) – 75.000 roebel,

2e plaats (2 winnaars) – 50.000 roebel (elk),

III plaats (2 winnaars) – 30.000 roebel (elk).

(Alle geldbeloningen zijn inclusief belastingen).

Het project wordt uitgevoerd met behulp van een subsidie ​​van de president van de Russische Federatie voor de ontwikkeling van het maatschappelijk middenveld, verstrekt door "Fonds voor presidentiële subsidies" .

Festival met verschillende jeugdactiviteiten en vrijetijdsactiviteiten "Mijn alternatief"zal binnen plaatsvinden XII All-Russisch Forum “De gezondheid van de natie is de basis van de Russische welvaart”, dat wordt gehouden van 30 mei tot 1 juni 2018 in Moskou in het Gostiny Dvor Exhibition Complex (Ilyinka St. 4).

Mediapartners van het project: tv-zender Muz-TV, Vesti.Ru, radiostation “Sport-FM”, sociaal netwerk “Odnoklassniki”, wellnessportaal “Live”, tv-zender “Tochka Otryva”.

De All-Russische internetwedstrijd voor jeugdvrije tijd "My Alternative" is een van de evenementen van het gelijknamige Nationale Jeugdproject, gericht op het identificeren van de beste video over projecten, initiatieven en praktijken van verschillende vormen van jeugdvrije tijd die voldoen aan de principes van een gezonde levensstijl.

De doelstellingen van de All-Russische internetwedstrijd voor jongeren “Mijn alternatief” zijn om jongeren te motiveren een gezonde levensstijl te leiden, om de aandacht te vestigen op een actieve levensstijl en sport door de beste vormen van jeugdvrije tijd te identificeren en te verspreiden.

De jongeren van Yaroslavl worden uitgenodigd om te praten over hun gezonde levensstijl!

Iedereen tussen de 16 en 44 jaar kan meedoen.

De gezichten van het project dit jaar zijn de absolute wereldkampioen boksen Konstantin Tszyu, drievoudig Olympisch kampioen Alexander Karelin, Olympisch kampioen Elena Slesarenko en anderen.

Voorzitter van de National Health League, academicus Leo Bockeria:

“Als we het hebben over een gezonde levensstijl, mogen we de jongere generatie natuurlijk niet uit het oog verliezen, omdat de jeugd de toekomst is van het geweldige land waarin we leven. Ieder van ons die in iets in het leven is geslaagd, heeft op een gegeven moment iemand geïmiteerd. Het maakt niet uit wie het was: vader, moeder, buurman of oudere vriend, leraar, beroemde atleet. Je hoeft dus alleen maar te kijken naar wie er in de buurt is, letterlijk bij je, waar je actief je vrije tijd onderweg kunt doorbrengen, die velen helaas achter de computer of voor de tv doorbrengen. Daarom roep ik jongeren op om aandacht aan zichzelf te besteden. Om succesvol te zijn, moet je allereerst gezond zijn. Ik wens je veel succes met de wedstrijd “Mijn Alternatief”!”

Absoluut wereldkampioen boksen Konstantin Tszyu:

“Toen ik mijn carrière begon, was er een beroemde atleet op wie ik wilde lijken. Hij toonde vooral vriendelijkheid in de ring. Een heer in de ring zijn en laten zien welk soort boksen mensen leuk vinden, was ook erg belangrijk voor mij. Deze kwaliteiten waren een essentieel onderdeel van mijn leven in de sport, waardoor ik de doelen kon bereiken die ik had bereikt. Als we de lat voor onszelf hoog leggen, moeten we, om deze te bereiken of hoger te komen, veel aan onszelf werken. Dit dagelijkse werk geeft veel persoonlijke voldoening als je het gestelde doel bereikt. Bovendien inspireert iemands verlangen om beter te worden en een zekere concurrentiekracht anderen om zijn voorbeeld te volgen. Ik wens je veel succes in de competitie! Leid een gezonde levensstijl, sport, streef ernaar om beter te worden!”

Jongeren worden uitgenodigd om te praten over hun gezonde levensstijl

Het MUZ-TV-kanaal en de Al-Russische publieke organisatie “National Health League” presenteren: Nationaal jeugdproject “Mijn alternatief”! Dit is het belangrijkste feest van het jaar voor degenen die een actieve levensstijl leiden, sporten en weten wat echte extreme sporten zijn.

De doelstellingen van de wedstrijd zijn het motiveren van jonge mensen om een ​​gezonde levensstijl te leiden, het bevorderen van de afwijzing van tabak, alcohol en drugs, en ook het vestigen van de aandacht op een actieve levensstijl en sport door het identificeren en verspreiden van de beste vormen van vrijetijdsbesteding voor jongeren.

De competitie zal plaatsvinden in drie fasen. De eerste fase – het verzamelen van applicaties en video’s op de website van de National Health League – www.ligazn.ru – eindigt op 25 februari 2018. De video van de deelnemer moet vertellen over zijn gezonde levensstijl, sport en hobby's, die worden gekenmerkt door een competitieve instelling, gedrevenheid en verlangen naar uitmuntendheid. De tweede fase vindt plaats van 19 maart tot 15 april 2018 in de vorm van open internetstemmen, op basis van de resultaten waarvan 20 finalisten zullen worden bepaald. Ook zullen er 3 finalisten worden bepaald door de beslissing van de Competitiecommissie. Tijdens de derde fase van de competitie, die plaatsvindt van 30 mei tot 1 juni 2018 in Gostiny Dvor in Moskou, zullen de finalisten hun video's presenteren op het Jeugdfestival “My Alternative”. De vijf winnaars van de Competitie worden bepaald door de Competitiecommissie, waarin experts op het gebied van sociale reclame, vertegenwoordigers van publieke organisaties en vertegenwoordigers van de media zitting zullen hebben.

Iedereen tussen de 16 en 44 jaar kan deelnemen aan de Competitie.

De winnaars van de Competitie ontvangen diploma's en geldprijzen:

• 1e plaats (1 winnaar) - 75.000 roebel,
• 2e plaats (2 winnaars) - 50.000 roebel (elk),

III plaats (2 winnaars) - 30.000 roebel (elk).