Hogyan írjunk képletet az Excel Learningben. Maguk a képletek kellenek

Jó napot!

Valamikor valami hihetetlen dolog volt számomra, ha egyedül írtam egy képletet Excelben. És még annak ellenére sem, hogy gyakran dolgoznom kellett ebben a programban, nem írtam mást, csak szöveget...

Mint kiderült, a legtöbb képlet nem bonyolult és könnyen kezelhető, még egy kezdő számítógép-felhasználó számára is. A cikkben szeretném felfedni azokat a legszükségesebb képleteket, amelyekkel gyakran kell dolgozni...

És hát kezdjük...

A cikkben szereplő összes művelet az Excel 2007-es verziójában jelenik meg.

Az Excel program elindítása után megjelenik egy ablak sok cellával - a táblázatunk. A program fő jellemzője, hogy meg tudja számolni (mint egy számológép) az Ön által írt képleteit. Egyébként minden cellához hozzáadhatsz egy képletet!

A képletnek "=" jellel kell kezdődnie. Ez kötelező feltétel. Ezután írja be, hogy mit kell számolnia: például "=2+3" (idézőjelek nélkül), és nyomja meg az Enter billentyűt - ennek eredményeként látni fogja, hogy az "5" eredmény megjelent a cellában. Lásd az alábbi képernyőképet.

Fontos! Annak ellenére, hogy az A1 cellában az "5" szám szerepel, a számítás a ("=2+3") képlet szerint történik. Ha egyszerűen "5"-et ír a szomszédos cellába, akkor amikor a kurzort erre a cellára mozgatja - a képletszerkesztőben a felső sor, Fx) - az egyszerű "5" számot fogja látni.

És most képzelje el, hogy egy cellába nem csak a 2+3 értéket írhatja be, hanem azoknak a celláknak a számát, amelyek értékeit hozzá kell adni. Tegyük fel, hogy „=B2+C2”.

Természetesen a B2-ben és C2-ben is kell néhány szám, különben az Excel az A1-es cellában 0-val egyenlő eredményt jelenít meg.

És még egy fontos megjegyzés...

Ha másol egy cellát, amelyben képlet található (például A1), és beilleszti egy másik cellába, akkor az nem másoljaértéke "5", és maga a képlet!

Sőt, a képlet egyenesen arányosan változik: vagyis ha A1-et A2-be másoljuk, az A2 cellában lévő képlet egyenlő lesz a "=B3+C3" értékkel. Az Excel automatikusan módosítja a képletet: ha A1=B2+C2, akkor logikailag A2=B3+C3 (minden szám 1-gyel nő).

Az eredmény egyébként A2=0, mivel a B3 és C3 cellák nincsenek megadva, vagyis egyenlők 0-val.

Ily módon a képletet egyszer megírhatja, majd a kívánt oszlop összes cellájába másolja - és maga az Excel elvégzi a számítást a táblázat minden sorában!

Ha nem szeretné, hogy a B2 és C2 megváltozzon másoláskor, és mindig ezekhez a cellákhoz legyen kötve, akkor csak adjon hozzájuk egy „$” jelet. Egy példa alább látható.

Így bárhová másolja az A1 cellát, az mindig a kötött cellára fog hivatkozni.

2. Értékek hozzáadása sorokban (SUM és SUMIFS képlet)

Természetesen az egyes cellákat összeadhatja az A1+A2+A3 stb. képlet elkészítésével. De hogy ne szenvedjen ilyen szenvedést, az Excelben van egy speciális képlet, amely összeadja a cellákban lévő összes értéket amit kiválasztasz!

Vegyünk egy egyszerű példát. Többféle terméknév is van raktáron, és tudjuk, hogy az egyes termékekből mennyit külön-külön kg-ban. raktáron van. Próbáljuk kiszámolni, hogy mennyi kg-ban. rakomány a raktárban.

Ehhez lépjen arra a cellára, amelyben az eredmény megjelenik, és írja be a következő képletet: =SUM(C2:C5)”. Lásd az alábbi képernyőképet.

Ennek eredményeként a tartomány összes cellája összegezni fog, és látni fogja az eredményt.

2.1. Kompozíció feltétellel (feltételekkel)

És most képzeljük el, hogy vannak bizonyos feltételek, vagyis nem az összes értéket kell összeadnunk a cellákban (Kg, raktáron), hanem csak bizonyosakat, mondjuk egy árral (1 kg) kevesebb mint 100.

Van egy kiváló képlet erre a „ SUMIFS”-re.Egy példa, amelyet közvetlenül követ a képlet minden szimbólumának magyarázata.

=SUMIFS(C2:C5;B2:B5;”, ahol:

C2:C5– a hozzáadandó oszlop (cellák);

B2:B5– az az oszlop, amelyben a feltétel ellenőrzésre kerül (azaz az ár például 100-nál kisebb);

A “ maga a feltétel, vegye figyelembe, hogy a feltétel idézőjelbe van írva.

Ebben a képletben nincs semmi bonyolult, a lényeg az, hogy megfigyeljük a C2:C5; B2:B5 megfelelést - helyesen; C2:C6;B2:B5 helytelen. Azaz az összegzési tartománynak és a feltételtartománynak arányosnak kell lennie, különben a képlet hibát ad vissza.

Fontos! Az összegnek sok feltétele lehet, vagyis nem az 1. oszloppal, hanem egyszerre 10-el lehet ellenőrizni, sok feltételt szabva.

3. A feltételeket kielégítő sorok számának megszámlálása (COUNTIFS képlet)

Meglehetősen gyakori feladat a cellaértékek összegének kiszámítása, és a bizonyos feltételeket kielégítő cellák számának kiszámítása. Néha túl sok a feltétel.

És hát... kezdjük.

Ugyanebben a példában megpróbáljuk megszámolni a 90-nél nagyobb árú termékneveket (ha körülnézel, elmondhatod, hogy 2 ilyen termék van: mandarin és narancs).

Az áruk megszámlálásához a kívánt cellában írja be a következő képletet (lásd fent):

=COUNTIFS(B2:B5;”>90″)ahol:

B2:B5– az általunk beállított feltételnek megfelelően ellenőrzött tartomány;

“>90”– maga a feltétel idézőjelek között van.

Most próbáljuk meg kicsit bonyolultabbá tenni a példánkat, és még egy feltétel szerint adjunk hozzá számlát: 90 feletti árral + a készleten lévő mennyiség kevesebb, mint 20 kg.

A képlet a következő alakot ölti:

=COUNTIFS(B2:B6;”>90″;C2:C6;”

Itt marad mindenugyanaz, kivéve még egy feltételt ( C2:C6;”). Egyébként sok ilyen feltétel lehet!

Egyértelmű, hogy egy ilyen kis táblázathoz senki nem fog ilyen képleteket írni, de egy több száz soros táblánál az teljesen más kérdés. Például ez a táblázat több mint vizuális.

4. Értékek keresése és helyettesítése egyik táblázatból a másikba (BVR képlet)

Képzeljük el, hogy egy új táblázatot kaptunk új árcédulákkal a termékhez. Jó, ha 10-20 név van – mindet kézzel is "töltheti". És ha több száz ilyen név van? Sokkal gyorsabb, ha az Excel önállóan megtalálja a megfelelő neveket az egyik táblából a másikban, majd az új árcédulákat a régi táblázatunkba másolja.

Ehhez a problémához a BVRképletet használjuk. Egy időben ő maga is "habar" logikai képletekkel "lódult", mígnem találkozott ezzel a csodálatos dologgal!

És hát kezdjük...

Íme a példánk + egy új táblázat árcédulákkal. Most automatikusan le kell cserélnünk az új árcédulákat az új táblázatból a régire (az új árcédulák pirosak).

Helyezze a kurzort a B2 cellába - vagyis abba az első cellába, ahol automatikusan módosítanunk kell az árcédulát. Ezután megírjuk a képletet, ahogy az alábbi képernyőképen is látható (a képernyőkép után lesz róla részletes magyarázat).

=VPR(A2;$D$2:$E$5;2), ahol

Az A2az az érték, amelyet keresni fogunk, ha új árcédulát szeretnénk venni. Esetünkben az "alma" szót keressük az új táblázatban.

$D$2:$E$5– válassza ki teljesen az új táblázatunkat (D2:E5, a kijelölés a bal felső sarokból átlósan a jobb alsó sarokból áll), vagyis hol fog a keresés végrehajtásra kerülni. A "$" jel ebben a képletben azért szükséges, hogy a képlet más cellákba másolásakor a D2:E5 ne változzon!

Fontos!Az "alma" szó keresése csak az elsőben történika kiválasztott táblázat oszlopában, ebben a példában az "alma" kifejezés a D oszlopban lesz keresve.

2– Ha az „alma” szót megtalálta, a függvénynek tudnia kell, hogy a kiválasztott táblázat (D2:E5) melyik oszlopából másolja a kívánt értéket. Példánkban a 2. oszlopból másoljon (E), mert az első oszlopban (D) kerestünk. Ha a dedikált keresési táblázat 10 oszlopból áll, akkor az első oszlopban történik a keresés, és 2-10 oszlop között választhat másolást.

Annak érdekében, hogya =VPR(A2;$D$2:$E$5;2)képlet új értékekkel helyettesítse más termékneveket is – csak másolja át a a termék árcímkéit tartalmazó oszlop (példánkban másolja a B3:B5 cellákba). A képlet automatikusan megkeresi és kimásolja a kívánt értéket a táblázat oszlopából.

5. Következtetés

A cikkben megvizsgáltuk az Excellel való munka alapjait, kezdve a képletek írásának megkezdésével. Példákat adtunk azokra a leggyakoribb képletekre, amelyekkel a legtöbb Excelben dolgozó embernek gyakran dolgoznia kell.

Remélem, valakinek szüksége lesz a szétszedett példákra, és segít felgyorsítani a munkáját. Jó kísérleteket!

És milyen képleteket használsz, lehet valahogy egyszerűsíteni a cikkben megadott képleteket? Például gyenge számítógépeken, amikor bizonyos értékeket megváltoztatnak a nagy táblázatokban, ahol a számítások automatikusan megtörténnek, a számítógép néhány másodpercre lefagy, újraszámol, és új eredményeket mutat...