Podmíněný součin

Možná jste se v Excelu setkali s funkcí SUMIF (případně SUMIFS), která umožňuje sečíst hodnoty, které splňují daná kritéria. Na podobném principu fungují funkce COUNTIF/COUNTIFS (vrátí počet buněk, které splňují dané kritérium) nebo AVERAGE/AVERAGEIFS (počítá průměr, opět při splnění kritria).

Co když ale potřebujeme nikoli podmíněný součet nebo průměr, ale podmíněný součin?

Praktické využití je nastíněno v následujícím příkladu.

Překladatel má tabulku, kde zjišťuje, kolik má zákazníkovi fakturovat za provedenou práci. Ví, že má základní sazbu za 1 přeloženou stranu ve výši 200 Kč (buňka B15), tuto sazbu pak násobí počtem stran (B14), ale hlavně koeficienty, dle typu překladu, obtížnosti a případných slev nebo navýšení (oblast buněk B2:B12).

Volbu různých koeficientů určuje vepsáním písmene "x" do buňky vedle (oblast C2:C12). Celkový koeficient (a tedy podmíněný součin) pak počítá v buňce B16. (Jedná se o maticový vzorec, čili je třeba jej potvrdit pomocí klávesové zkratky Ctrl + Shift + Enter. Vzorec se píše bez složených závorek, ty jsou doplněné automaticky.)

Buňka B18 pak zobrazuje výslednou cenu, počítanou standardní funkcí SOUČIN (bylo by také možné počítat jako =B14*B15*B16). 

Přizpůsobení tabulky podle rozlišení obrazovky

Pokud často pracujete s jedním excelovským souborem z různých počítačů a hlavně různých rozlišení, můžete narazit na problém. Na obrazovce s vyšším rozlišením vidíte celou tabulku přehledně, v celé své kráse. Na menším rozlišení pak vidíte pouze část tabulky.

Abyste ji zobrazili celou, můžete využít funkce lupy a zmenšit si tak zobrazení např. na 90 %.

 

Pokud s touto tabulkou ale pracujete několikrát za den, je neustálé přepínání velikosti zobrazení otravné. Dá se to však řešit makrem, které zjistí aktuální rozlišení a podle toho, při otevření souboru, upraví požadované přiblížení.

Kód níže vložte do VBE (editoru jazyka VBA – zobrazíte stisknutím klávesové zkratky Alt+F11) do objektu ThisWorkbook.

Declare Function GetSystemMetrics32 Lib "User32" Alias "GetSystemMetrics"(ByVal nIndex As Long) As Long
Public Sirka As Long
Public Vyska As Long

Private Sub Workbook_Open()

Sirka = GetSystemMetrics32(0)
Vyska = GetSystemMetrics32(1)

If Sirka > 1680 Then
If ActiveWindow.Zoom <> 90 Then ActiveWindow.Zoom = 90
Else
If ActiveWindow.Zoom <> 100 Then ActiveWindow.Zoom = 100
End If

End Sub

Pozn.: Zvýrazněné hodnoty můžete upravovat dle libosti.

Rozšířená funkce listu SVYHLEDAT

Vyhledávací funkce SVYHLEDAT standardně umožňuje vyhledávat hodnotu pouze na základě jednoho kritéria. Co když ale chceme něco víc?

1) Vyhledávání podle více kritérií

Potřebujete vyhledávat podle více kritérií najednou? Použijte například funkci SOUČIN.SKALÁRNÍ.

Obecně vzorec vypadá následovně:

=SOUČIN.SKALÁRNÍ((oblast_kritérií_1=kritérium_1)*(oblast_kritérií_2=kritérium_2)*(oblast_kritérií_3=kritérium_3);oblast_dat)

Konkrétní vzorec pak může vypadat např. takto:

=SOUČIN.SKALÁRNÍ((A2:A29=J2)*(B2:B29=K2)*(C1:H1=L1);C2:H29)

2) Vyhledávání hodnot "doleva"

Co když ale potřebujeme vyhledat hodnotu, která se nachází nalevo od prohledávaného sloupce? Zde využijeme kombinaci funkcí INDEX a POZVYHLEDAT.

Více je patrné z následujícího obrázku.