Přeskočit navigaci | Přeskočit na novinky

Nový videokurz na Seduo: Excel pro začátečníky

     

Jste zde: Úvodní stránka » excel » vba-listy-bunky » zapis-vzorec-funkci-do-bunky-excel-vba

Zapiš vzorec (funkci) do buňky - VBA Excel


Jak do buňky zapsat vzorec pomocí makra ve VBA?

Úvodem

Logo Excel VBA

Jak zapsat údaj do buňky jsem popsal v článku: Excel VBA - zapiš údaj do buňky. Co když, ale potřebujeme zapsat do buňky vzorec (funkci)? Opět na to není nic složitého. Jen je potřeba si uvědomit, že vzorce se mohu zapisovat ve dvou tvarech, klasicky A1 nebo jako RC. Tomu stačí přizpůsobit požadavek na zápis.


Jak lze vkládat do buňky

Funkci (vzorec) lze do buňky vkládat ve formátu:

  • A1
  • R1C1
  • strukturovaný - pokud použiváte styly tabulky

Dále může být funkce (vzorec) zadána ralativně, absolutně, nebo kombinovaně.

Aby to nebylo tak jednoduché tak název funkce lze psát anglicky, nebo česky (přesněji řečeno v jazyku lokalizace). V následujících kapitolách si vše projeme na praktickkých ukázkách.

Zapiš klasický vzorec A1

Zapsat klasický odkaz není problém, existuje více možností, které jsou popsány v tomto odstavci.

Odkazem na oblast

Range("B4").Formula = "=A1"

Nebo odkazem na buňku:

Cells(2, 2).Formula = "=A1"

Příklad smazání vzorce:

Range("B4").Formula = ""

Zapiš vzorec ve formátu RC

Range("H10").FormulaR1C1 = "=RC[-3]"

Nebo odkazem na buňku:

Cells(10, 10).FormulaR1C1 = "=RC[-3]"

Příklad smazání vzorce:

Range("AG" & k).FormulaR1C1 = ""

Další možností

Poznámka: Takové malé připomenutí, jak něco zapsat ať mám člověk rychle po ruce a nemusí lovit v dalších článcích, ve kterých je struktura podrobněji popsána.

Toto již kombinuje cykly či podmínky do vzorce vkláda nějakou kompilovanou hodnotu například:

k = 8 Range("A4").Formula = "=List5!D" & (k + 22)

Dynamicky výběr řádku:

k = 8 Range("A" & k).Formula = "=List5!D" & (k + 2)

Cyklus a podmínka

For k = 1 To 5 If Range("A" & k).Value = "0" Then Range("B" & k).Formula = "" Else Range("C" & k).Formula = "=D2" End If Next k

Zapiš funkci SUMA možností

Další příklady jak zapsat pomocí VBA do buňky funkci SUMA. Zapis v klasickém formátuu, R1C1 formátu, EN zápis i lokální (zapis českých překladů funkcí)

Jednoduchý zápis do jedné buňky

Range("A1").Formula = "=SUM(B2:B5)" Range("A2").FormulaR1C1 = "=SUM(RC[1]:R[3]C[1])" Range("A3").FormulaLocal = "=SUMA(B2:B5)" Range("A4").FormulaR1C1Local = "=SUMA(R[-2]C[1]:R[1]C[1])"

Jednoduchý zápis do více buněk - relativní

Pozor pokud budete chtít do několika po sobě jdoucích buněk zapsat tutéž funkci, mohou způsobit problém absolutní a relatvní odkazy. Pro relativní (hodnota se bude posouvat, nebude stejná).

Range("A8:A10").Formula = "=SUM(B6:B8)" Range("A12:A14").FormulaR1C1 = "=SUM(RC[1]:R[5]C)" Range("A16:A18").FormulaLocal = "=SUMA(B6:B8)" Range("A20:A22").FormulaR1C1Local = "=SUMA(R[-2]C[1]:R[1]C[1])"

Jednoduchý zápis do více buněk - absolutní

Odkaz na oblast buněk bude ve všech buňkách stejný (absolutní - tj. s dolarem $).

Range("A30:A32").Formula = "=SUM($B$6:$B$8)" Range("A34:A36").FormulaR1C1 = "=SUM(R2C1:R5C1)" Range("A38:A40").FormulaLocal = "=SUMA($B$6:$B$8)" Range("A42:A44").FormulaR1C1Local = "=SUMA(R2C1:R2C2)"

Poznámka: Doplněno na základě dotazu v komentáři.

Dynamická změna ve funkcích

Potřebujeteli měnit jednoduše oblast na základě výpočtu:

Hodnota = "E2" Range("I3").Formula = "=SUM(D2:" & Hodnota & ")"

nebo

Range("A1").FormulaR1C1 = "=SUM(R" & radek1 & "C" & sloupec1 & ":R" & radek2 & "C" & sloupec2 & ")"

Poznámka: Doplněno na základě dotazu v komentáři.

Závěrem

Díky ukázkám je jasné proč říkám, že překlady funkcí jsou zlo a vada na kráse jinak geniální programu Microsoft Excel. Pro další náměty či doplnění slouží komentáře.

Článek byl aktualizován: 01.11.2014 14:20

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2006. Své vědomosti a zkušenosti dávám k dispozici na různých školeních a konzultacích, ale také na tomto webu. K dispozici na tomto webu je mnoho návodu, tipů a triků včetně desítek různých šablon.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil

Doporučte tento článek přátelům

Pokud vám článek pomohl, případně si myslíte, že může pomoci i někomu dalšímu, budu rád když jej sdílením doporučíte přátelům - děkuji:



Komentáře


Letáček

Přidáno: 26.12.12 09:07

Prosím o radu. Jak zapsat vzorec =SUMA(A5:E5)ve VBA?, tj. když řádek nebo sloupec jsou proměnné

Pavel Lasák

Přidáno: 27.12.12 13:26

To Letáček: Například takto: Hodnota = "E2" Range("A1").Formula = "=SUM(A5:" & Hodnota & ")" nebo "složitěji" kdy každá hodnota je proměnná v zápisu R1C1: Range("A1").FormulaR1C1 = "=SUM(R" & radek1 & "C" & sloupec1 & ":R" & radek2 & "C" & sloupec2 & ")" Pro překlady funkcí EN-CZ a CZ-EN viz články: Funkce překlad: CZ - EN, Funkce překlad: EN CZ. V ToDo listu mám poznámku na další doplnění tohoto článku, jak zadávat funkce ;).

Pavel Lasák

Přidáno: 28.12.12 22:41

Článek je doplněn ;)

Dietlind

Přidáno: 29.01.13 11:20

Dobrý den, chtěla jsem se zeptat, jak by bylo možné zapsat funkci SUMIFS ve VBA tak, aby se to přepočítalo a výsledek by byl hodnota (ne vzorec).

Pavel Lasák

Přidáno: 29.01.13 17:11

To Dietlind: Nejprve zapsat vzorec, provede se výpočet a pak přečíst hodnotu z buňky http://office.lasakovi.com/excel/vba-listy-bunky/precti-udaj-do-bunky-excel-vba/

Dietlind

Přidáno: 30.01.13 10:48

Děkuji moc a díky taky za tyto stránky, které jsou skvělé:-)

Pesk

Přidáno: 30.07.13 07:46

To Pavel Lasák: Zdravím a moc děkuji za pěkné a přehledné stránky se spoustou užitečných informací (upřímně, ne ironicky ;-) Nicméně jednu informaci jsem zde nenašel: Jak zapsat výsledek běžné funkce do proměnné, t.j. aniž bych ji napřed zapsal do buňky a pak četl z buňky do proměnné. Např. potřebuji do proměnné "pocet" vlozit výsledek funkce =SUBTOTAL(2,A:A) . Moc děkuji za pomoc.

Pavel Lasák

Přidáno: 31.07.13 15:31

To Pesk: Jenom jednu informaci? ;) Tak tady je: MojeHodnota = Application.WorksheetFunction.Subtotal(2, Range("A:A")) MsgBox (MojeHodnota)

Martin

Přidáno: 04.10.13 10:48

Dobrý den, zkouším pomocí FormulaR1C1 vložit vzorec, který bere hodnoty z jiného listu, ale hází mi to nepravda. Vzorec: Cells(i, 2) = FormulaR1C1Local = "=ZAOKROUHLIT(SUMA(List4!R[+3]C[+1]:R[+3]C" & pocet + 2 & ")/" & pocet & "; 1)" Napadá Vás řešení? Děkuji Jinak také chválím stránky :-) Martin Svoboda

Martin

Přidáno: 04.10.13 14:32

Zdravím ještě jednou, nakonec vyřešeno takto: Cells(i, 2).FormulaR1C1 = "=ROUND(SUM(List4!R" & pos & "C[1]:R" & pos & "C" & pocet + 2 & ") / " & pocet & ", 1)" Přeji pěkný den, Martin Svoboda

Vítek

Přidáno: 30.12.13 22:17

Dobrý den, zajímaly by mě kody pro podmíněné formátování. Je možné použít vzorec typu R1C1? Moc díky Vítek Morava

Pavel

Přidáno: 13.01.14 09:50

Dobry den, potreboval bych vlozit do bunky vzorec s If Range("Sheet1!A2") = "=IF(Sheet2!A1>50;99;11)" a nejak se mi to nedari. Delam nekde chybu, nebo to nelze? Predem dekuji za info.

Pavel Lasák

Přidáno: 14.01.14 18:20

To Pavel: Viz ukázky v článku, nechybí ti v zápise .Formula ?

Lenka

Přidáno: 30.01.14 08:31

Ahoj, mám takový problém. Na jednom listu mám vzorce, které bych potřebovala "roztáhnout" (takže asi dynamické vzorce se změnou řádků) pomocí VBA do takové hloubky (tedy i dynamické pole), jaký mám počet výskytů řádkových zápisů. Jsem z toho zmatená jak to mám zapsat. Mohli byste mi poradit?

Michal Šuťak

Přidáno: 10.02.14 21:25

Ahoj, potrebujem cez makro vložiť do bunky funkciu LOOKUP a zapísané Range("A1").Formula = "=LOOKUP(...)" mi to nefunguje. Nevieš mi poradiť? Ďakujem.

Michal Šuťak

Přidáno: 19.02.14 22:15

Ahoj, už som na to prišiel. Range("A1").Select ActiveCell.FormulaLocal = "=LOOKUP(...)"

Milos

Přidáno: 01.05.14 11:26

Vytvárim soucet zapsanych hodnot ve sloupci. V tabulce je tolik radku kolik je dni v roce + nejaky den před a nejaky po ja mam 371. od 30.12.2013 do 4.1.2015 Kazdou radku jsem si specielne oznacil datumem a vedle cislem radku. V bunce U4 se mi zobrazuje aktualni datum funkci =DNES() ve sloupci U6 - U376 jsou ty datumy ve sloupci V6 - V376 jsou cisla radek v bunce V4 mam vzorec, který mi zobrazi na kterem radku se naleza aktualni datum, dnes je to 128 =VYHLEDAT(U4;U6:U376;V6:V376) ted bych potreboval secist počet vepsanych hodnot do aktuálního radku k dnesmimu datumu 1.5.2014 je to radka 6 - 128 a potom od následujícího radku do konce radka 129 - 376 radka první a posledni 6 a 376 jsou pevne danne 128 a 129 jsou promenne zítra to bude 129 a 130 atd. Ted bych potreboval dostat vzorece treba makrem do do zvolenych dvou bunek. Vzorce pro soucety vepsanych hodnot do sloupce N by vypadal asi takto: =POČET2(N6:N128) =POČET2(N129:N376) Do bunek ve sloupci N se vpisuje planovani dovolene třeba celkem 50 dni. Ted potrebuji scitat vybranou dovolenou do aktuálního datumu a naplánovanou dovolenou od aktuálního do konce. Bud bych potreboval do retezce POČET2(N6:N128)makrem pripsat = rovnitko, anebo makrem rovnou vytvori vzorec =POČET2(N6:N128), kde by se číslo 128 za N vlozilo s urcite bunky. Resim to uz dva dny stále nevim. Dekuji.

Jan

Přidáno: 07.05.14 22:04

ďakujem za rady a pomoc .... tvoje príspevky pomohli Môžem ich využívať aj v práci a na tvorbu aplikácii v exeli ???

Pavel Lasák

Přidáno: 08.05.14 16:28

To Jan: Proč bys nemohl? Jsou to návody k dalšímu použití.

Luboš

Přidáno: 22.05.14 15:01

Dobrý den, řeším zapsání "vzorce" do bunky, ale nedaří se mi to s desetinnou čárkou. Range(adresa).Formula = "=E$19*" & hodnota Proměnná "hodnota" funguje pokud je celé číslo (např. 105), ale jakmile je např.105,5 tak mi to hlásí chybu. Nevíte co dělám kde špatně? Děkuji předem.

Mirek

Přidáno: 30.06.14 09:44

To Luboš: A není problém v desetinné tečce a čárce?

Jirka

Přidáno: 12.08.14 11:07

tento vzorec, Range("A12:A14").FormulaR1C1 = "=SUM(RC[1]:R[5]C)" mi vypíše do cílových buněk nuly. Když za poslední "C" doplním [3], tak se součet již provede. Je takové pravidlo, že se oblast zadává od levého horního rohu do pravého dolního?

Pavel Lasák

Přidáno: 13.08.14 20:12

To Jirka: Jak fungují R1C1 odkazy http://office.lasakovi.com/excel/zaklady/relativni-absolutni-odkazy-excel/#06

Jan Kučera

Přidáno: 12.01.15 13:13

Zdravím, nedaří se mi rozchodit následující vložení funkce do buňky a nevím proč. Hodnotu proměnné TabName (As String) mám správně název listu (zde třeba "M02" (bez uvozovek)). Pokud si nechám výsledek složeného zápisu vypsat jako text a ručně jej vložím do buňky, pak mi ta funkce funguje správně. Zkoušel jsem i použít IF místo KDYŽ, ale nepomohlo. Prosím o pomoc Range("D3").Formula = "=KDYŽ('" & TabName & "'!E3=0;" & Chr(34) & Chr(32) & Chr(34) & ";'" & TabName & "'!E3)"

Přidáno: 12.01.15 13:45

Zdravím, nedaří se mi rozchodit následující vložení funkce do buňky a nevím proč. Hodnotu proměnné TabName (As String) mám správně název listu (zde třeba "M02" (bez uvozovek)). Pokud si nechám výsledek složeného zápisu vypsat jako text a ručně jej vložím do buňky, pak mi ta funkce funguje správně. Zkoušel jsem i použít IF místo KDYŽ, ale nepomohlo. Prosím o pomoc. Range("D3").Formula = "=KDYŽ('" & TabName & "'!E3=0;" & Chr(34) & Chr(32) & Chr(34) & ";'" & TabName & "'!E3)"

Jojo

Přidáno: 14.01.15 07:50

To Jan Kučera:TabName = "M02" Range("D3").Formula = "=IF(" & TabName & "!E3=0," & Chr(34) & Chr(32) & Chr(34) & "," & TabName & "!E3)"

Dáša

Přidáno: 05.02.15 21:30

Dobrý den, nevím jak mam zapsat funkci která podle čísla v jedné buňce vyplní funkci X ve vedlejším sloupci právě tolik buňěk kolik je hodnota toho čísla. Děkuji za jakoukoliv radu

Jan

Přidáno: 18.05.15 08:37

Dobrý den, prosím o nakopnutí, přoč mi nefunguje můj zápis. Sheets("SEZNAM").Cells(RecordNo, 20).Formula = "=CONCATENATE(ČÁST(POLÍČKO(""filename"";A" & RecordNo & ");1;NAJÍT(""["";POLÍČKO(""filename"";A" & RecordNo & "))-1);""POLOZKY\"";B" & RecordNo & ";"".pdf"")" Nefunguje ani ,na test, zjednodusený: Sheets("SEZNAM").Cells(RecordNo, 20).Formula = "=POLÍČKO(""filename"";A80)"

Jan

Přidáno: 29.05.15 13:30

Už sem se nakopl sám :-) 1) místo ; používat , 2) názvy funkcí v angličtině

Jarda

Přidáno: 30.05.15 13:46

To Dáša Mohlo by to být třeba takto? Sub Test() Dim i As Integer Dim col As Integer Dim rw As Integer i = ActiveSheet.Cells(10, 3) 'nebo ActiveSheet.Range("C10") col = ActiveWindow.ActiveCell.Column col = col + 1 For rw = 1 To i ActiveSheet.Cells(rw, col).Formula = "=SUMA(B1:B5)" Next rw End Sub

René

Přidáno: 27.06.15 14:55

Dobrý den, prosím o radu, chci vybrat více buněk, ale ne příkazem Range("A1:E1").Select , ale pomocí RC, prosím, poraďte, děkuji

Jarda

Přidáno: 02.07.15 17:49

to René Více buněk pomocí RC lze vybrat, ale stejně musíte použít Range. Range(Cells(3,3), Cells(12,8)).Select Tímto příkaze jsem vybral oblast Range("C3:H12") Syntaxe: Cells(řadek, sloupec) Cells se dá použít místo Range.

Filip

Přidáno: 20.10.15 21:08

Dobrý den, potřeboval bych vložit takovýto vzorec: =IF(I2>0;C2;IF(ISBLANK(I2);"";IF(I2=0;"Smazat";""))) ale hlásí mi to chybu Compile error: Expected: end of statement Je nutné tento vzorec, který jsem dřív používal normálně v buňce přepsat na IF ve VBA nebo je možné to vložit i takto? Děkuji

Radek

Přidáno: 04.02.16 16:31

Pekny den, chtel bych se optat jak na vlozeni VLOOKUP z jinehou souboru. Respektive potrebuji vkladat z vice souboru, kdy se meni jen nazev souboru. Pri rucnim vlozeni vzorce do bunky se propojeni provede, ale pri vlozeni pres VBA hlasi chybu. Zkusel jsem nize psanou strukturou, ale zadna varianta jak vlozit i celou cestu k souboru me nenapadla. Range("B1").Formula = "=VLOOKUP(A1,[" & myFileName & "]" & mySheetName & "!" & myRangeName & ",2,0)" Dekuji moc za pripadnou radu

Leoš

Přidáno: 17.02.16 15:23

Dobrý den, chtěl bych do relativního odkazu pusnem zadat místo číslice hodnotu, kterou si předním vypočítám. např.: horni_mez = (-1) * (radek_soucet - 3) With Cells(radek_soucet, 4) .NumberFormatLocal = "# ##0 Kč" .FormulaR1C1 = "=sum(r[-1]c:r[horni_mez]c) End With Bohužel takto to nefunguje. Děkuji

Leoš

Přidáno: 17.02.16 15:33

Dobrý den, Již jsem na to přišel. je potřeba to napsat ve tvaru: .FormulaR1C1 = "=sum(r[-1]c:r[" & horni_mez & "]c)"

marek

Přidáno: 25.04.16 13:30

ahoj, uz niekolko dni sa trapim s tym ako dat absolutny odkaz pri rc zapise.. ked si nahram makro tak tam je absolutny odkaz, ked si to makro zovseobecnujem s premennymi tak robi vsetko dobre len neviem ako mu povedat na tom ktorom miest ma byt dolar.. dik za radu activeCell.FormulaR1C1 = _ "=IF(AND(R[-" & riadok + 4 & "]C>0,OR(R[-9]C<-15,R[-" & riadok + 4 & "]C[" & stlpec - 4 & "]=0)),1,2)"

Martin

Přidáno: 22.08.16 16:27

Dobrý den, Prosím o radu kdy potřebuji vložit do buňky vzorec pro součet dvou buněk a na dalším řádku inkrementovat řádek o 1. Při: =A1+B1 na dalším řádku A2+B2 atd... Mám to v podmínce for, jedná se o spojení datumu a času do jedné buňky ve formátu "dd/mm/yyyy hh:mm". Děkuji za tip

Jojo

Přidáno: 24.08.16 08:09

To Martin:For i = 1 To 10 Range("C" & i) = Range("A" & i) + Range("B" & i) ' sčítať Next i

SK

Přidáno: 15.09.16 12:42

Ahoj všichni, jde tento problem: Zapiš vzorec (funkci) z jedné buňky do buňky ve které se nacházím. řešit jinak než makrem? Diky za odpověď

Jirka

Přidáno: 01.11.16 14:43

Ahoj, potřeboval bych poradit: potřebuji do jednoho sešitu přepisovat data z jiných sešitů. Mám makro, které mi dokáže vygenerovat cestu k jinému souboru a přepsat mi buňku z onoho sešitu do mého aktuálního sešitu. Problém je v tom, že bych potřeboval, aby makro pracovalo jako funkce a když rozkopíruju cílovou buňku (v mém případě A1), tak by mělo makro vzít hodnotu pro název z buňky C2 (makro pořád bere jen z C1). Díky za rady

Jirka

Přidáno: 03.11.16 10:52

Ještě ten kód Sub Makro2() Dim Predpona As String Dim Nazev As String Dim Koncovka As String Dim Cesta As String Predpona = "='C:\Users\SGHLQNP\Desktop\[" Nazev = Range("C1").Value Koncovka = ".xlsx]List1'!R1C1" Cesta = Predpona & Nazev & Koncovka Range("A1").Formula = Cesta End Sub

Jirka

Přidáno: 03.11.16 10:53


Přidání nového komentáře

Poznámka: Z časových důvodu není v mých sílách zodpovědět všechny dotazy. Děkuji za pochopení.




(vlož číslo padesátpět)

Z bezpečnostních důvodu dočasně nemůžete použít HTML značky, kromě <code> a </code> (pro vkládání VBA kódu). Děkuji za pochopení.
Děkuji, za Vaše komentáře.

Za obsah komentářů neodpovídám, jelikož jsou komentáře publikovány ihned po jejich napsání čtenářem. Toto nemohu nijak ovlivnit. Přesto si vyhrazuji možnost jakýkoli neslušný komentář smazat bez udání důvodu. V komentářích si tykáme. ;-)






Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2016 | 35059

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.