Kako se formula deluje, če. Primeri uporabe funkcije so tiho v Excelu

Funkcijo se uporablja za uporabo, če je potrebno povzeti vrednosti območja, ki ustreza določenemu merilu.

Opis funkcije

Funkcijo se uporablja za uporabo, če je potrebno povzeti vrednosti območja, ki ustreza določenemu merilu. Na primer, v stolpcu s številkami, ki jih je treba povzameti samo vrednosti, ki presegajo številko 5. Za to lahko uporabite podano formulo spodaj:
\u003d Vrednost (B2: B25, "\u003e 5") V tem primeru se v skladu z merilom preverijo povzete vrednosti. Po potrebi se lahko pogoj uporabi za eno območje in povzema ustrezne vrednosti iz drugega območja. Na primer, formula:
\u003d B2: B5; "Ivan"; C2: C5) Vrednosti iz območja C2: C5 so povzete, za katere so ustrezne vrednosti iz območja B2: B5 "Ivan".
Drug primer uporabe funkcije je prikazan na spodnji sliki:

Primer uporabe je tiho za izračun dobička s pogojem

Sintaksa

\u003d Tiho (območje; stanje; [doseg_suming])

Argumente

stanje območja range_suming.

Obvezni argument. Celic, merjena po merilih. Celice v vsakem območju morajo vsebovati številke, imena, nize ali sklicevanja na številke. Plesne celice in celice, ki vsebujejo besedilne vrednosti, se preskočijo.

Obvezni argument. Stanje v obliki številke, izraza, povezave do celice, besedila ali funkcije, ki določa, katere celice je treba povzeti. Na primer, stanje lahko zastopamo na naslednji način: 32, "\u003e 32", B5, "32", "jabolka" ali danes ().

POMEMBNO! Vsi besedilni pogoji in pogoji z logičnimi in matematičnimi znaki je treba skleniti v dvojnih narekovajih ("). Če je stanje številka, ni treba uporabljati ponudb.

Neobvezni argument. Celice, katerih vrednosti so seštele, če se razlikujejo od celic, določenih kot obseg. Če je argument območje_sum izpuščen, Microsoft Excel. Celice, določene v območju argumenta (enake celice, na katere se uporablja stanje), je povzeto.

Opombe

  • V argumentu se lahko pogoj uporablja nadomestne znake: vprašaj (?) In zvezdico (*). Vprašanja se ujema z enim znakom in zvezdico - vsako zaporedje znakov. Če želite neposredno najti vprašaj (ali zvezdico), morate podpisati znak "Tilda" (~).
  • Funkcija je tiho vrne napačne rezultate, če se uporablja za ujemanje strune, daljše od 255 znakov z nizom # pomeni!.
  • Argument Range_SUM ne sme sovpadati velikosti z razredom argumenta. Pri določanju dejanskih celic, ki jih je treba seštevati, se zgornja leva celica argumentnega območja uporablja kot začetna, nato pa se celice območja, ki ustrezajo območju argumenta, povzemajo.

Dodali bomo vrednote tistih v progah, katerih polja izpolnjujejo dva merila naenkrat (stanje in). Razmislite o besedilnih merilih, številskih in merilih v datum. Analizirali bomo funkcijo povzema ( ), Angleška različica Sumifs ().

Kot izvorna tabela vzemite tabelo z dvema stebroma (polja): Besedilo " Sadje"In numeric" Količina na zalogi"(Glejte primer datoteke).

Naloga1 (1 Merilo besedila in 1 numerični)

Poiščite število škatel blaga z nekaterimi Sadje In, Kdo. Ravnotežje škatel v skladišču Nič manj minimalnega. Na primer, število polj z blagom breskve (celica D. 2 ), ki imajo preostanek škatel na zalogi\u003e \u003d 6 (celica E. 2 ) . Pridobiti moramo rezultat 64. Izračun lahko realizira z niz formul, dajemo več (glejte primer datoteke Besedilo in število listov):

1. =Smerevilimn (B2: B13; A2: A13; D2; B2: B13; "\u003e \u003d" & E2)

Značilnosti sintakse: SMEREVULIMN (interval_suming; interval_liviya1; pogoj1; interval_loculation2; stanje2 ...)

  • B2: B13 interval_suming - Celice za seštevanje, vključno z imeni, nizi ali referencami, ki vsebujejo številke. Prazne vrednosti in besedilo se ne upoštevajo.
  • A2: A13.in B2: B13 interval_lovia1; interval_loculia2; ... predstavljajo od 1 do 127 razponov, v katerih se preveri ustrezna pogoj.
  • D2.in "\u003e \u003d" & E2 pogoj1; Stanje2; ... To je od 1 do 127 pogojev v obliki številk, izrazov, povezav do celice ali besedila, ki opredeljuje, katere celice bodo povzeti.


Vrstni red argumentov se vlije v funkcije Sumymbremn () in tiho (). V argumentu SumymeLimN () interval_suming. To je prvi argument, in v tiho () - tretji. Pri kopiranju in urejanju teh podobnih funkcij je treba zagotoviti, da so argumenti določeni v pravilnem vrstnem redu.

2. Druga možnost \u003d SummiPatic ((A2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2))
Podrobneje bomo opisali uporabo funkcije Sumpcy ():

  • Rezultat izračuna A2: A13 \u003d D2 je matrika (FALSE: Ljubezen: Leži: Laži: Resnica: Resnica: Resnica: False: False: False: False: Lie) Vrednost resnice ustreza naključju vrednosti iz stolpec Zvezek Merila, tj. Beseda breskev. Matrika je mogoče videti z označevanjem v A2: A13 \u003d D2, nato pa pritisnete;
  • Rezultat računanja B2: B13 je (3: 5: 11: 98: 4: 8: 56: 2: 4: 6: 10: 11), t.. Samo vrednosti iz stolpca B. ;
  • Rezultat množenja nizov elementov (A2: A13 \u003d D2) * (B2: B13) je (0: 0: 0: 0: 0: 8: 56: 0: 0: 0: 0: 0). Pri množenju števila vrednosti vrednosti vrednosti 0; in vrednost resnice (\u003d 1) se pridobiva številka;
  • Analizirali bomo drugo pogoj: rezultat izračuna - (B2: B13\u003e \u003d E2) je matrika (0: 0: 1: 1: 0: 1: 1: 0: 0: 1: 1: 1). Vrednosti v stolpcu " Število škatel na zalogi", Ki izpolnjuje merilo\u003e \u003d E2 (i.e.\u003e \u003d 6) ustreza 1;
  • Poleg tega je funkcija parov Sumpcy () spremenljive elemente nizov in povzemajo izdelke, pridobljene. Dobimo - 64.

3. Druga izvedba funkcije Sumpcy () je formula \u003d Sumpered ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

Po potrebi se datumi lahko vnesete neposredno v formulo \u003d SmerevilMN (B6: B17; A6: A17; "\u003e \u003d 01/15/2010"; A6: A17; "<=06.03.2010")

Za prikaz pogojev izbire v besedilnem nizu uporabite formulo \u003d "Prodaja za obdobje od" in besedila (D6; "dd.mm.yg") & "By" & Besedilo (E6; "dd.mm.yg")

V zadnji formuli.

Naloga4 (mesec)

Rahlo spreminjajte stanje prejšnje naloge: našli bomo skupno prodajo na mesec (glejte primer datoteke Mesec listov).

Formule so zgrajene podobno kot naloga 3, vendar uporabnik uvaja ne 2 datuma, toda ime meseca (predpostavlja se, da je tabela v enem letu).


Za rešitev tretjega opravila, tabela z konfiguriranim avtoFilter izgleda takole (glej primer datoteke Leaf 2 Datumi).


Prej je treba tabelo pretvoriti v niz rezultatov.

Povzeti vrednosti, ki izpolnjujejo določeno merilo (pogoj), se funkcija uporablja; , Angleška različica Sumif ().

Funkcija sintakse

Smerei.(Območje; Pogoj; [Range_suming])

Območje - Razpon celic, v katerih se iščejo vrednosti ustreznega argumenta Pogoj. Razpon lahko vsebuje številke, datume, besedilne vrednosti ali povezave do drugih celic. V primeru drugega argumenta - Range_suming. - izpuščeni, potem argument Območje Je tudi obseg, po katerem se izvedejo vrednosti izpolnjevanja argumenta. Pogoj (v tem primeru Območje Mora vsebovati številke).

Pogoj - merilo v obliki številke, izraza ali besedila, ki določa, katere celice je treba povzeti. Na primer, argument Pogoj Lahko se izrazi kot 32, "jabolka" ali "\u003e 32".

Range_suming. - paleto celic, ki vsebujejo številke, ki bodo povzete v primeru, da celice, določene v argumentu Območjeustreza argumentu Pogoj. Range_suming. - Izbirni argument. Če je izpuščen, se bo pozove opravilo v območju celic, navedenih v argumentu Območje.

Primeri

Razmislite o primeru, ko je argument Range_suming. izpuščena. V tem primeru bo postavitev v razponu celic, navedenih v prvem argumentu Območje (tj., mora vsebovati številke). Prav tako bo iskala vrednosti ustreznega argumenta Pogojki bo potem povzet. Naj bo to območje B5: B15. Oglejte si primer datoteke.

Rešujemo nalog:

  • poiščite vsoto vseh številk več kot ali enakih 10. Odgovori: 175. Formula: \u003d Simery (B5: B15; "\u003e \u003d 10")
  • poiščite vsoto vseh številk, ki so manjše ali enake 10. Odgovor: 42. Formula: \u003d Tiho (B5: B15;<=10")
  • poiščite vsoto vseh pozitivnih števil v območju. Formula: \u003d Suspentno (B5: B15; "\u003e 0"). Alternativa z uporabo funkcije Sumpcy () izgleda takole: \u003d Sumpered (B5: B15) * (B5: B15\u003e 0))

Obliko naloge pogoji(Merila) dovolj prilagodljiva. Na primer, v formuli \u003d Suspentno (B5: B15; D7 & D8) Merilo<=56 задан через ссылку D7&D8 : в D7. Vsebuje besedilno vrednost<=, а в D8. - ŠTEVILO 56 (glej sliko spodaj). Uporabnik, na primer, lahko zlahka spremeni merilo z uporabo v celici D7. . Enakovredna formula \u003d Tiho (B5: B15;<=56") или \u003d Tiho (B5: B15;<="&56) ali \u003d Tiho (B5: B15;<="&D8) ali \u003d Suspentno (B5: B15; D7 & 56).

Dober dan, dragi naročniki in spletna stran za obiskovalce spletnega dnevnika. Nazadnje smo ugotovili formulo podjetja in sem se odločil pisati o drugi zelo uporabni funkciji Excel O drugem zelo koristne funkcije. Smerei.. Obe funkciji lahko "vežeta" podatke iz različnih virov (tabele) na ključnem polju in pod določenimi pogoji so zamenljivi. Hkrati obstajajo velike razlike v njihovem imenovanju in uporabi.

Če je dodelitev aplikacije preprosto "Povlecite" podatke iz enega odličnega mesta v drugega, se uporabljajo za povzeti številčne podatke, da povzamejo po določenem merilu. Glavni namen te formule je povzetek podatkov v skladu z določenim pogojem. Približno je v pomoč. Vendar pa se bo to orodje uresničilo za druge namene. Na primer, pogosto uporabljam formulo, ki jo pogosto uporabljam namesto HDP, saj ne daje napak # N / D, ko nekaj ne more najti. In to je odlično in pospešuje delo.

Funkcijo je treba uspešno prilagoditi različnim nalogam. Zato bomo v tem članku razmislili o ne 1 (ena), vendar kar 2 (dva) primera. Prvi bo povezan s sprejetjem določenega merila, drugega - z "vlečenjem" podatkov, to je kot alternativa podjetju.

Primer potapljanja s funkcijo je tiho

Tabela prikazuje položaje, njihove količine in pripadajo določeni skupini blaga (prvi stolpec). Razmislite o poenostavljeni uporabi poenostavljene uporabe, ko moramo izračunati znesek samo na položajih, vrednosti, na katerih ustrezajo določenemu stanju. Na primer, želimo vedeti, koliko top se prodaja, t.j. Tiste, katerih vrednost presega 70 enot. Iskanje takšnega blaga z očmi, nato pa ročno povzamemo zelo priročno in hitro, zato je funkcija tukaj zelo primerna.

Najprej dodelimo celico, kjer se izračuna znesek. Nato pokličite čarovnika za funkcije. To je ikona fX. v vrstici formule. Poleg tega iščemo funkcijo na seznamu, ti si tiho in klikne nanj. Odpre se pogovorno okno, kje rešiti to nalogo, morate izpolniti samo dva (prva) polja treh predlaganih.


Zato sem klical ta primer poenostavljen. Zakaj 2 (dva) od 3 (tri)? Ker se naše merilo nahaja v obsegu povzema.

V polju "Območje" je označen območje Excel tabele, kjer se nahajajo vse vrednosti izvornih vrednosti, od katerih morate nekaj izbrati in nato zložite. Običajno se nastavi z miško.

V polju "kriterij" je stanje, v katerem bo izbrana formula. V našem primeru določamo "\u003e 70". Če ne postavljate narekovajev, se sami posnamejo. Toda na splošno bi moralo biti takšno merilo v narekovajih.

Zadnje polje "Dapad_suming" ne izpolnjuje, kot je že določeno na prvem področju.

Tako funkcija tiho prevzema merilo in začne izbrati vse vrednosti iz določenega območja, ki izpolnjuje določeno merilo. Po tem, vse izbrane vrednosti krat. Tako deluje funkcijski algoritem.

Izpolnite funkcije v čarovniku za funkcije, pritisnite gumb "Enter" na tipkovnici, ali v oknu čarovnika, mi da miš v redu. Za simcting se mora izračunana vrednost prikazati na mestu, ki je vnesena funkcija. V mojem primeru se je izkazalo 224pcs. To pomeni, da je bila skupna vrednost blaga, ki se prodaja v količini več kot 70 kosov 224pc. (To je vidno v spodnjem levem kotu okna čarovnika še pred pritiskom na "OK"). To je vse. To je bil poenostavljen primer, ko sta merilo in razpon postavitve na enem mestu.

Poglejmo na primer, ko merilo ne ujema z območjem seštevanja. To stanje se dogaja veliko pogosteje. Upoštevajte enake pogojene podatke. Moramo izvedeti znesek, ki ni več ali manj od katere koli vrednosti, vendar znesek določene skupine blaga, priznava, skupina

Če želite to narediti, ponovno sprostite celico s prihodnjim štetjem zneska in pokličite čarovnika za funkcije. Zdaj v prvem oknu določimo obseg, kjer je merilo vsebovano z izbiro za postavitev, to je v našem primeru, je to stolpec z imeni skupin izdelkov. Nato je merilo, ki je sam predpisan ročno, zapušča snemanje "Group G" v ustreznem polju ali pa preprosto označuje miško celico z želenim merilom. Zadnje okno je razpon, kjer se nahajajo nastavitve. Določam ga z miško, vendar tiskanje ni dovoljeno ročno doseči ročno.


Rezultat bo vsota blaga, prodanega iz skupine G - 153pc.

Torej smo pogledali, kako izračunati en znesek na enem določenem merilu. Vendar pa je naloga pogosteje, ko je potrebno izračunati več zneskov za več meril. Nič ni lažje! Na primer, moramo ugotoviti količino prodanega blaga za vsako skupino. To pomeni 4 (štiri) vrednosti v 4. (štiri) skupinah (A, B, B in D). Za to je ponavadi nekje na Excelovih ekspandih seznam skupin v obliki ločene plošče. Jasno je, da morajo imena skupin točno ujemati z imeni skupin v izvorni tabeli. Takoj dodajte končno vrstico, kjer je znesek še vedno nič.

Formula se nato predpiše za prvo skupino in se razteza vsem drugim. Tukaj morate biti pozorni na relativnost povezav. Obseg z merili in obsegom seštevanja morajo biti absolutne reference, tako da pri raztezanju formule, ne "ne gredo", vendar merilo, najprej morate določiti miško (in ne, da se ne registrira ročno), drugič, Obstajati mora relativna referenca, da ima vsak znesek lastno merilo.

Polja čarovnika za polnjenje polja s podobnim izračunom.


Kot je razvidno, za prvo skupino, in količina prodanega blaga je znašala 161pcs (spodnji levi vogal vzorca). Zdaj kliknite Enter in raztegnite formulo.

Vsi zneski so bili izračunani, njihov skupni rezultat pa je 535, ki sovpada z rezultatom v izvorni podatki. Torej, vse vrednosti so bile povzete, ničesar niso zamudili. Zadnja možnost uporabe je bila tiho, osebno se pogosto srečam.

Primer uporabe funkcije je tiho, da primerjate podatke

Kot nimam več, je funkcija tiha, lahko uporabite smiselno za vezi podatkov. Dejansko, če povzamete eno vrednost, bo ta vrednost. Ta aritmetična lastnost ni greh za uporabo v korist. Celo možno je, da Excelovi oblikovalci ne vedo o takem barbarski uporabi njihovih možganov.

Skratka, zlahka bodo enostavno prilagojene za veliko podatkov kot alternativo funkciji FRP. Zakaj uporabljati težo, če je ARP? Pojasnem. Prvič, tiho, za razliko od EPP, neobčutljivo na format podatkov in ne izda napake, če jo najmanj čakajo; Drugič, tiho namesto napak zaradi pomanjkanja vrednosti na določenem merilu vprašanja 0 (nič), ki omogoča brez nepotrebnih gest, da izračunajo rezultate območja s formulo. Vendar pa je en minus. Če v želeni tabeli se bo vsako merilo ponovilo, potem se ustrezne vrednosti povzemajo, kar ne vsebuje vedno "pull-up". Bolje je biti na straži. Po drugi strani pa je pogosto potrebno potegniti vrednosti na določeno mesto, medtem ko so pretvorjene položaje prepognjene. Samo vedeti morate lastnosti funkcije tiho in uporabo v skladu z navodili za uporabo.

Zdaj razmislite o primeru, kako je funkcija tiho, se izkaže, da je bolj primeren za privijanje podatkov kot UPR. Pustite podatke iz primera, ki ga prodajate v januarju. Želimo vedeti, kako so se spremenili februarja. Primerjava je primerna za proizvodnjo v isti tableti, po dodajanju drugega stolpca na desno in izpolnite svoje podatke za februar. Nekje v drugem Exhesev datoteki obstajajo statistični podatki za februar skozi celot, vendar želimo analizirati te pozicije, za katere je potrebno iz velike datoteke s prodajno statistiko vsega blaga zategnite želene vrednosti na naš znak. Za začetek, poskusimo uporabiti formulo UPR. Kot merilo bomo uporabili kodo izdelka. Rezultat je na sliki.


Jasno je, da se en položaj ni dohitel, in napako # N / D se izda namesto številske vrednosti. Najverjetneje, februarja, ta izdelek preprosto ni prodan in zato je odsoten v bazi podatkov za februar. Posledično je napaka # N / D prikazana v znesku. Če ni veliko položajev, potem problem ni velik, dovolj je, da ročno izbrišete napako, znesek pa bo pravilno preračunana. Vendar pa se število vrstic lahko meri s stotinami, računanje na ročno nastavitev pa ni popolnoma pravilna rešitev. Zdaj bomo uporabili formulo, ki namesto HDP.


Rezultat je enak, samo namesto napake # N / D, daje nič, kar vam omogoča, da običajno izračunate znesek (ali drug indikator, na primer povprečje) v zadnji vrstici. To je glavna ideja, zakaj morajo včasih uporabiti namesto HDP. Z velikim številom položajev bo učinek še bolj oprijemljiv.

V resnici je pogosto potrebno delati z velikimi podatkovnimi nizi, ki večkrat presegajo dimenzije monitorja. Za ogled in sledenje napak je zelo težko. Toda vse kosmiče plavajo v naslednjih izračunih. Takšni primeri niso primerni za razstavljanje s posnetki posnetkov v članku, zato vas vabim, da si ogledate posebej zabeležen video jezik o funkciji.

In tukaj je še ena lekcija o tihi



Priporočamo, da se preberete