Máte k dispozici pohyby na různých účtech za určité období. Chcete nějak jednoduše spočítat, jaké jsou pohyby na účtech dle jednotlivých bank.
A | B | |
1 | IBAN | Pohyb |
2 | CZ65 0300 0000 0002 1962 3990 | 25 425,24 |
3 | CZ65 0300 0080 1018 0308 5233 | 37 205,97 |
4 | CZ65 0710 0077 0400 0762 1491 | 61 339,56 |
5 | CZ65 0100 0000 0000 1810 8511 | 8 175,93 |
6 | CZ65 0710 0000 0000 0093 1761 | 57 472,71 |
7 | CZ65 2400 0000 0019 2120 0001 | 49 082,64 |
8 | CZ65 0710 0200 0100 0603 6231 | 11 510,43 |
9 | CZ21 2400 0000 0053 1410 1034 | 49 088,49 |
10 | CZ65 0300 0000 0001 6262 6014 | -672,78 |
Díky znalostem o konstrukci IBAN International Bank Account Number víte, že první čtveřice čísel je kódem banky. Podle těchto čísel budeme tedy sčítat pohyby na účtech.
Pro zjištění konkrétní banky z kódu IBAN můžeme použít např. funkci ČÁST.
=ČÁST(A:A;6;4)
Pomocí funkce KDYŽ můžeme do pomocné tabulky vypisovat pohyby na účtech při splnění podmínky, že IBAN obsahuje na správné pozici konkrétní kód banky. Podmínka je tvořena výrazem, kde srovnáváme textový řetězec, který vrací funkce ČÁST s kódem banky (abychom mohli do buňky zapsat 0100, musíme buňku buď formátovat jako text nebo použít zápis ="0100"). Vzorec ve sloupci D by pak mohl vypadat:
=KDYŽ(ČÁST($A:$A;6;4)=D$1;$B:$B;"")
Pokud IBAN odpovídá kódu banky v prvním řádku, pak se vypíše příslušný pohyb, jinak se nevypíše nic.
A | B | C | D | E | F | H | |
1 | IBAN | Pohyb | 0100 | 0300 | 0710 | 2400 | |
2 | CZ65 0300 0000 0002 1962 3990 | 25 425,24 | 25 425,24 | ||||
3 | CZ65 0300 0080 1018 0308 5233 | 37 205,97 | 37 205,97 | ||||
4 | CZ65 0710 0077 0400 0762 1491 | 61 339,56 | 61 339,56 | ||||
5 | CZ65 0100 0000 0000 1810 8511 | 8 175,93 | 8 175,93 | ||||
6 | CZ65 0710 0000 0000 0093 1761 | 57 472,71 | 57 472,71 | ||||
7 | CZ65 2400 0000 0019 2120 0001 | 49 082,64 | 49 082,64 | ||||
8 | CZ65 0710 0200 0100 0603 6231 | 11 510,43 | 11 510,43 | ||||
9 | CZ21 2400 0000 0053 1410 1034 | 49 088,49 | 49 088,49 | ||||
10 | CZ65 0300 0000 0001 6262 6014 | -672,78 | -672,78 | ||||
11 | |||||||
12 | Celkem: | 8 175,93 | 61 958,43 | 130 322,70 | 98 171,13 |
Při větším počtu účtů a bank by byla tabulka velmi nepřehledná. Další možností získání stejného výsledku je použití funkce SUMIF.
Do sloupce C si připravíme pomocí funkce ČÁST pomocný sloupec s kódy bank. Ve sloupci F použijeme funkci SUMIF, kde pole oblast budou údaje ve sloupci C, kritérium bude kód banky ze sloupce E a sčítaná oblast bude sloupec B. Vzorec v buňce F2 by mohl vypadat:
=SUMIF(C:C;E2;B:B)
A | B | C | D | E | F | |
1 | IBAN | Pohyb | Banka | Banka | Celkem | |
2 | CZ65 0300 0000 0002 1962 3990 | 25 425,24 | 0300 | 0100 | 8 175,93 | |
3 | CZ65 0300 0080 1018 0308 5233 | 37 205,97 | 0300 | 0300 | 61 958,43 | |
4 | CZ65 0710 0077 0400 0762 1491 | 61 339,56 | 0710 | 0710 | 130 322,70 | |
5 | CZ65 0100 0000 0000 1810 8511 | 8 175,93 | 0100 | 2400 | 98 171,13 | |
6 | CZ65 0710 0000 0000 0093 1761 | 57 472,71 | 0710 | |||
7 | CZ65 2400 0000 0019 2120 0001 | 49 082,64 | 2400 | |||
8 | CZ65 0710 0200 0100 0603 6231 | 11 510,43 | 0710 | |||
9 | CZ21 2400 0000 0053 1410 1034 | 49 088,49 | 2400 | |||
10 | CZ65 0300 0000 0001 6262 6014 | -672,78 | 0300 |
SUMA, KDYŽ, ČÁST, SUMIF