Relativní a absolutní odkazy - styl A1 - R1C1

jipi

Přidáno: 16.12.10 17:36

Dobrý den. Prosím o radu, zda je možné v makru, jehož součastí je je výběr pomocí automatického filtru provést odkaz na hodnotu v nějaké buňce tak, jak to jde např. u funkcí (př. u fce: kriterium: ">="&A1). Děkuji

Pavel Lasák

Přidáno: 18.12.10 20:31

to Jipi: Pokud chápu tak v makru bych použil podmínky: http://office.lasakovi.com/excel/vba/ms-excel-podminky-if-then-elseif-else/

jipi

Přidáno: 02.01.11 20:09

Dobrý den. "Kudy chodím, tudy chválím" "office.lasakovi.com". A tak tedy: "Vše nejhorší Vám a Vašim blízkým v novém roce ať se vyhne velkým obloukem". Ale bohužel něco chci návazně na předchozí dotaz neb jsem se asi špatně vyjádřil (stáří již nedovolí): mám v makru: ActiveSheet.Range("$A$5:$EN$65000").AutoFilter Field:=34, Criteria1:=">=100" _ , Operator:=xlAnd a potřeboval bych zaměnit "Criteria1:=">=100" (případně na datum: Criteria1:=">=1.1.2000") " za odkaz na hodnotu v "nějaké" buňce na jiném listu tak, jak to jde např. ve fci "SUMIFS" PODMÍNKA: ">="&A1. Dotaz: "A co takhle nějaká odměna za Vaši radu? Pro začátek aspoň kvalitní "Mělnické" víno kdybych věděl adresu. Přeci nejsme v komunismu podle hesla "Každému podle jeho potřeb". Já uznávám mimo jiné heslo, které jsem měl na rýsovacím prkně "Základem úspěch je motivace lidí", pokud možno kladná ... Zdraví Petr Jirmus (jipi)

Pavel Lasák

Přidáno: 03.01.11 15:55

To jipi> Dekuji. Ja jsem otazku spatne pochopil. VBA je nutno doplnit o: Dim strCriteria As String Range("G3").Select strCriteria = ActiveCell.FormulaR1C1 ActiveSheet.Range("$C$3:$D$7").AutoFilter Field:=2, Criteria1:=strCriteria, Operator:=xlAnd (v buňce G3 zadávat napr. ">=100", tj. vcetne znaminek) Jeste zapremyslim, zda to nepujde jednoduseji a elegantneji.

Pavel Lasák

Přidáno: 03.01.11 16:22

To Jipi: Takže ani strejda google neporadil elegantnější řešení. Pokud mi pošlete email lasak@centrum.cz tak mohu zaslat ukázku zpět.

Dim strCriteria As String
Range("G3").Select
strCriteria = ActiveCell.FormulaR1C1

ActiveSheet.Range("$C$3:$D$7").AutoFilter Field:=2, Criteria1:=strCriteria, Operator:=xlAnd

jipi

Přidáno: 03.01.11 20:43

Bingo! Chodí to. Smekám. Tak to je "A". A jak s tím "B"? Hezký zbytek dne jipi

xlnc

Přidáno: 24.07.11 00:46

Na datumy ve filtrech pozor, obzvláště na kód vygenerovaný českým Záznamníkem maker. Zkuste se inspirovat tady: http://excelplus.net/infusions/cvs/view_file.php?file_id=27

Pavel Lasák

Přidáno: 24.07.11 06:18

To xlnc: Děkuji za odkaz.

Stanislav

Přidáno: 20.09.11 15:01

Dobrý den, měl bych dotaz ohledně vytvoření relativního odkazu na jiný sešit. Mám jeden soubor, který hodlám používat jako šablonu a potom několik excelovských souborů s různýma datama, které se jmenují stejně. Potřebuji nějak dosáhnout toho, aby excel s daty, který otevřu, tak aby byly tyto data nataženy do souboru, který používám jako šablonu. Zatím jsem bohužel nebyl úspěšný, protože soubor šablona mi pořád natahuje data stále z jednoho a toho samého excelu s datama... Můžete mi prosím poradit? Předem díky.

Stanislav

Přidáno: 20.09.11 15:11

Případně mi prosím napište na email kachynas@seznam.cz, díky moc

Pavel Lasák

Přidáno: 20.09.11 17:39

To Stanislav: Soubory se jmenuji stejně? To potom musí byt v různých adresářích.

David K.

Přidáno: 21.10.11 07:41

Dobrý den, kopírování s abs. i relativními odkazy je jednoduché, ale s čím mám potíž je následující: LIST1 - 700 záznamů po řádku, od A1 do A700. LIST2, využívá na řádku 1:6 relativní odkaz na =LIST1!A1 (A1=LIST1!A1; A2=LIST1!A1; A3=LIST1!A1; A4=LIST1!A1; A5=LIST1!A1 a A6=LIST1!A1) protože s tím pak počítám dál a proto. ALE! Jakmile na řádku A7 dám kopírovat vložit, tak mi (velmi správně) excel napíše vzorec A7=LIST1!A6 tedy o 5 víc, ale já nutně potřebuji, aby se odkazoval na druhý řádek listu1. A nevím jak na to. Prostě rozkopírovat 700*6 se mi fakt nechce.

Pavel Lasák

Přidáno: 21.10.11 16:20

David K. Osobně bych si na to napsal skript ve VBA. Jak to udělat efektně bez VBA bohužel nevím.

David K.

Přidáno: 14.11.11 13:26

Nevadí, už jsem na to přišel. =INDEX('L:\abc\a\M\[1.xlsx]M'!B$2:B$990;ZAOKR.DOLŮ((ŘÁDEK('L:\abc\a\M\[1.xlsx]M'!B2)+2)/4;1);1) Kromě vyhledávání je finta jen ve funkci ŘÁDEK(), pak už to jen hladá. Vezme číslo řádku, vydělí počtem opakování (v mém případě 4), zaokrouhlí a přičte aktuální číslo řádku (začal jsem na řádku 2, tak +2, na řádku 5 pak +5) a funguje to...

Pavel Lasák

Přidáno: 14.11.11 16:11

To David K.: Děkuji za publikaci řešení.

Radek

Přidáno: 30.11.11 17:31

Dobrý den, chtěl bych se zeptat jak mám zadat vzorec pro to, aby když v buňce bude písmeno např "D" aby buňky celá zčervenala Děkuji za radu

Pavel Lasák

Přidáno: 30.11.11 17:59

To Radek: Použijte podmíněné formátování: Podmíněné formátování – Excel 2007, 2010

Přidáno: 30.11.11 18:35

To Pavel : :-D jak snadné jen si vzpomenout když to zrovna člověk potřebuje :-D mnohokrát Vám děkuji za okamžitou odpověď

Pavel Lasák

Přidáno: 30.11.11 20:40

To Radek: proto jsem vytvořil tyto stránky. Ať si to nemusím pamatovat :-)

Lenka Holečková

Přidáno: 08.06.12 12:08

Dobrý den, mám problém s kopírováním v Excelu. Přestalo mi fungovat používání klávesových zkratek CTRLC a CTRLV - nebo spíš to funguje částečně - tedy překopíruje to hodnotu ale ne vzorec, barvu písma, barvu buňky, prostě to kopíruje bez formátu a CTRL X - sice zkopíruje hodnotu ale nevymaže ji z původní buňky. Zřejmě jsem někde něco odklikla, ale netuším co. Pomůžete mi, prosím?

Pavel Lasák

Přidáno: 08.06.12 18:01

To Lenka Holečková: Neinstalovala jste nějakou pomůcku na kopírování? Nespouštěla nějaký excelovský soubor s makrem? Funguje Vám kopírování normálně v ostatních aplikacích WOrd, PowerPoint.

Jan P

Přidáno: 18.07.12 20:27

Dobrý den, řeším v Excelu 2010 tuto možnost. Na řádku mám např. deset různých využitých buněk, první prázdná, další dvě s popisem a 7 s datumy. Každý datum má svůj smysl pojmenovaný sloupcem (ale teď je to fuk). Každé pole s datumem (na řádku) má své podmíněné formátování s tím, že při určitém překročení (přiblížení) datumu (dnešním dnem) se pole obarví určitou barvou. Potud vše zvládám. Co potřebuji. Potřebuji, aby v první prázdné buňce bylo podmíněné formátování této charakteristiky: pokud se jakékoliv datum v sedmi polích ocitne v intervalu "dneska - 2 dny; dneska + dva dny" tak prázdné pole se zvýrazní barvou (např. červeně) a vypíše do pole nejbližší datum z teěch sedmi. Nemohu zjistit, jak se pracuje s intervaly a např. ve funkci "když" už vůbec ne. Budete-li mít chvilku, pak Vám velice děkuji. Jan P

Petr Pecháček

Přidáno: 22.01.13 10:40

Drobný tip na práci s RC adresací (v "A1 režimu"): Odkaz vždy na stejné místo (zde stejný řádek, dva sloupce vlevo): =NEPŘÍMÝ.ODKAZ("RC[-2]";NEPRAVDA) I po přesunu buňky se vzorec bude odkazovat na stejné místo.

Pavel Lasák

Přidáno: 22.01.13 16:10

To Petr Pecháček: Díky za tip. Další možností použíti funkce NEPŘÍMÝ.ODKAZ jsem sepsal v článku: NEPŘÍMÝ.ODKAZ (INDIRECT) funkce Microsoft Excel

Jarka

Přidáno: 26.02.13 20:03

Děkuji Vám pane Lasáku. Tímto Vám chci alepsoň takto poděkovat za svělé zpracované stránky o Excelu.

Adam

Přidáno: 06.06.13 11:57

Zdravím, jak se prosívás zapisuje adresa do VBA? Načítá si data kurzl do Excelu z internetu a potřebuju v makru ve VBA odkázat na určitou buňku. Děkuji moc!

Adam

Přidáno: 06.06.13 12:22

Sub Macro8() ' ' Macro8 Macro ' ' ActiveCell.FormulaR1C1 = "=RC[-1]/odkaz na bunku" Selection.NumberFormat = "#,##0.00 [$USD]" Potřebuju prostě podělit tu bunku o jednu vlevo cislem, ktere je v jine bunce.

vendula.k.

Přidáno: 26.11.13 10:34

dobry den hledam ..................

Jiří

Přidáno: 30.03.14 22:15

Dobrý den, rád bych se zeptal, jak mohu nahradit rozpětí pro výpočet součtu =sum(a1:a10) údajem, který bude uveden v buňce C3? Nějak takto =sum(C3), kde C3 bude mít uvedeno rozpětí a1:a10. Dá se to udělat? Děkuji moc za radu. J.

Jiří

Přidáno: 30.03.14 22:24

:) Už jsem to našel....Omlouvám se, stačilo si přečíst komentáře. Pěkný den. j.

Petr

Přidáno: 03.04.14 11:16

Dobrý den, používám v souboru odkaz na jiný sešit, ale aby mi tento odkaz fungoval, musí být zdrojový sešit otevřen, jinak hází chybovou hlášku #HODNOTA. Tento odkaz je součástí funkce COUNTIF, ale to by mělo být pravděpodobně vedlejší a nemělo by to mít na funkčnost samotného odkazu vliv... Používám tento typ odkazu: ='C:\Dokumenty_moje\Excel\[priklad.xls]List1'!A1 Můžete mi někdo poradit, jak snadno předělat tento odkaz, aby nemusel být zdrojový sešit otevřený? Děkuji! Petr

Pavel Lasák

Přidáno: 11.06.14 18:16

To Petr: Doporučuji zkontrolovat, zda v možnostech je povoleno aktualizovat data z Externích sešitů. Pak Excel aktualizuje data automaticky.

Petr Pecháček

Přidáno: 15.06.14 14:30

"Při použití stylu tabulka na tabulce se můžete odkazovat na jednotlivé buňky". - Předpokládám, že toto tvrzení vychází z karty Domů / skupiny Styly / Formátovat jako tabulku. Zařazení ikony, její název i popis toho, co skutečně dělá, je ve skutečnosti podle mě - no pitomé. Formát je zde doprovodným efektem. Tím klíčovým je fakt, že dojde k převodu prostého seznamu na objekt Seznam (List, ListObject), dnes Tabulka (Table) s velkým T (karta Vložení / Tabulka). A tento objekt podporuje strukturované odkazy...

Pavel Lasák

Přidáno: 21.06.14 21:43

To Petr Pecháček: Díky za doplnění.

Saša

Přidáno: 02.07.14 08:21

Dobrý den, v bodovém grafu kde, mám vytvořenu závislost dvou bodů (těchto dvojic je velké množství),potřebuji aby se mi nezobrazovala v popisu bodu hodnota X ani hodnota Y. Potřebuji, aby se mi v popisu bodu zobrazil název, který je každé dvojici přiřazen v předchozí buňce, viz: A x y A.1 0,3 4,3 A.5 0,5 4,5 A.7 0,2 4,7 Pokud při zadávání grafu označím i sloupec A, nevytvoří se mi již závislost bodů x a y. Popisovat ručně body by bylo velmi zdlouhavé a přijde mi, že i nesmyslné. Děkuji za radu. Saša

Saša

Přidáno: 02.07.14 08:37

Omlouvám se za zmatky. Otázku přesouvám pod téma grafy. Saša

jan

Přidáno: 15.10.14 19:58

nedarí sa mi čítať dáta z riadku a ukladať do stĺpca, dá sa to riešiť? Ďakujem

Jojo

Přidáno: 23.10.14 08:48

To jan: -- vysvietiť oblasť + CTRL+C + kliknúť na políčko, kde má začať vkladať + Pravý klik myšou a vybrať "vložit jinak..." + zakliknúť "Transponovat" + OK

Pavel Lasák

Přidáno: 26.10.14 19:56

To Jan: Více v článku: Transponovat - transpozice dat

Zdeňka

Přidáno: 30.10.14 11:35

Dobrý den, ráda bych Vás poprosila o radu: při tvoření podmínky se odkazuji na jinou buňku s funkcí (výpočet věku). Hází to chybu, protože zadaná podmínka nevidí hodnotu (tedy číselný věk), ale funkci. Jak to udělat, aby podmínka fungovala? Zkoušela jsem různé cesty (viz níže) a formátování buněk, ale nic nepomáhá. =($Q$10-G36)/365 =DATEDIF(G36;DNES();"Y") Předem díky,

Pavel Lasák

Přidáno: 30.10.14 17:07

To Zdeňka: Doplnit pomocný sloupec s výpočtem?

Erik

Přidáno: 14.11.14 14:56

Dobrý den, nefunguje mi "programátorský" odkaz. Do buňky v Excelu jsem napsal přesně to, co je výše, tedy '=R[-1]C' (bez apostrofů, zkoušel jsem i malá písmena a smíšené odkazy) a dostal jsem hlášku, že ve vzorci se vyskytla chyba. Zkoušel jsem odkaz i vygenerovat pomocí funkce Odkaz například takto: '=odkaz(1;1;4;0)' výsledkem bylo: 'R[1]C[1]', což po napsání do vzorce způsobilo opět chybu. Co dělám špatně? Může to být nastavením? Dají se tyto vzorce používat v buňkách, nebo jen v kódu Visual Basicu? Klasické odkazy mi fungují. Používám MS Office 2010 pro studenty a domácnosti.

Petr

Přidáno: 10.12.14 15:47

Dobrý den, řeším problém jak odkázat ve vzorci na hodnotu v jiné buňce. =SUBTOTAL(109;$B$7:$B$(aby se na tomto místě se vzorec odkazoval na hodnotu která se mění v jiné buňce))/SUBTOTAL(109;$F$7:$F$554). Doufám, že jsem popsal problém smysluplně. Děkuji.

Petr

Přidáno: 15.12.14 08:42

To Petr: Dostal jsem odpověď na jiném fóru: řešení: =SUBTOTAL(109;NEPŘÍMÝ.ODKAZ("B7:B"&D1))/SUBTOTAL(109;NEPŘÍMÝ.ODKAZ("F7:F"&D1))

V.Cion

Přidáno: 24.02.15 08:55

Dobrý den pane Lasáku, prosím Vás o radu. Popř. alespoň o nasměrování. -v adresářích mám různě propojené excel soubory (buňky z různých souborů jsou propojené) -všechny jsou zatím "krkolomně" na disku D -těchto počítačů je více -všechny jsou synchronizované -lze udělat "něco" aby na různých počítačích byly ty adresáře na různých discích -tedy když ten počítač co má vše na C:\ něco změní a zesynchronizuje na ostatních kompech, tak aby to na těch ostatních co to mají třeba na D:\ nepřepsal právě to na C:\ -tzn. "jakýsi" absolutní odkaz na disky, přičemž je jedno kde ty propojené excel soubory na různých discích jsou (jestli na disku C, D, E atd) Děkuji za případnou radu. V.Cion

Pavel Lasák

Přidáno: 03.03.15 18:22

To V.Cion: Úplně přesně jsem nepochopil. Snad pomůže NEPŘIMÝ.ODKAZ http://office.lasakovi.com/excel/funkce/neprimy-odkaz-indirect/

Lenka

Přidáno: 08.05.15 13:59

Dobrý den, já Vám jdu hlavně poděkovat za úžasně přehledné a jasné stránky. Moc mi to pomáhá. Příští týden ve středu jdu na výběrové řízení, kde mě mimo jiné čeká i praktická zkouška z excelu. Samozřejmě jsem do CV uvedla, že ho ovládám i když s ním žádné zkušenosti nemám :D Tak teď bojuju a jakožto samouk se do toho snažím dostat. A díky Vašim stránkám jsem se posunula o kus dopředu. Tak snad to ve středu vyjde. Děkuji, děkuji, děkuji :)

Honza

Přidáno: 20.05.15 12:09

Dobrý den Prosím o radu jak docílím, aby při kopírování buňky zůstal odkaz na buňku stejný, ale změnil se list ze kterého chci čerpat. Např. ='List 1'!$N$25 a při překopírování o řádek níže by měl být odkaz ='List 2'!$N$25 atd. Lze to nějak vyřešit? Děkuji za informaci

Pavel Lasák

Přidáno: 18.07.15 07:02

To Honza: Využít funkci NEPŘIMÝ.ODKAZ.

PETR VALÁŠEK

Přidáno: 18.07.15 12:12

Dobrý den, velice si vážím vaši práce na stránkách, již mi několikrát pomohly. Zrovna řeším problém. Dala by se vytvořit buňka v EXCELU ve které by se měnila hodnota v závislosti na uloženém názvu souboru? Př.: c:/dokumenty/sešit1.xls v buňce by se ukázalo sešit1 po přeuložení na c:/dokumenty/sešit2.xls buňka by se změnila na sešit2 Předem velice děkuji za odpověď

Pavel Lasák

Přidáno: 07.08.15 21:44

To PETR VALÁŠEK: Vužít funkci POLÍČKO: =POLÍČKO("názevsouboru") http://office.lasakovi.com/excel/funkce/informace-informacni-funkce-excel/#POLICKO

Fanoušek

Přidáno: 25.08.15 08:20

Pane Vlasák, jste SUPER!!! Díky za Vaše stránky !

Fanoušek

Přidáno: 25.08.15 12:06

Pane Lasák!!! Omlouvám se za zkomolení! Váš Fanda! :)

Pavel Lasák

Přidáno: 10.10.15 12:53

To Fanoušek: Omluva přijata ;), klidně se příště podepíšte. Za svůj pravidvý názor se nemusíte stydět. :D

Miloš

Přidáno: 26.11.15 18:33

Dobrý den, prosím o radu. Jde mi o kopírování vzorců z jednoho sešitu do druhého. Pokud vzorce obsahují odkazy na jiný list téhož sešitu tak se do zkopírovaných vzorců automaticky doplní odkazy na původní sešit. Já je tam ale nechci. například z původního =List1!A1 se do nového sešitu zkopíruje =[odkud.xlsx]List1!A1 Jedná se mi o větší množství souborů a mazat ručně vložená propojení je zdlouhavé.

Pavel Lasák

Přidáno: 28.11.15 11:22

To Miloš: Přes kartu Data a upravit odkazy (soubor musí být uložen). Nebo přes VBA makro.

Miloš

Přidáno: 29.11.15 15:22

To Pavel Lesák, děkuji, ale nějak mi to nefunguje. Když zruším propojení přes DATA, UPRAVIT ODKYZY, ZRUŠIT PROPOJENÍ, tak se mi vzorce s propojením nenahradí vzorcem bez propojení, ale aktuální hodnotou, která odpovídá vlastně hodnotám ze souboru odkud jsem původní vzorce kopíroval. Nebo dělám něco blbě?

Pavel Lasák

Přidáno: 30.11.15 21:03

To Miloš: Napsal jsem článek kopírování odkazů speciál. Ještě mě napadla druhá možnost, najít a nahradit.

Honza S.

Přidáno: 04.12.15 13:57

Dobrý den, existuje možnost jak se odkazovat na buňky v relativní pozici od aktuální buňky bez přepínání stylů odkazů ? Tedy aby se to chovalo jako např. R[-3]C[5], ale styl odkazů by zůstal "A1" ? Děkuji, Honza

Pavel Lasák

Přidáno: 05.12.15 07:41

To Honza S.: Nerozumím otázce, nepomůže funkce POSUN nebo NEPŘÍMÝ.ODKAZ ?

Miloš

Přidáno: 13.12.15 19:14

To Pavel Lasák. Děkuji za radu. podle Vašeho článku kopírování odkazů speciál to funguje perfektně.

Autor Pavel Lasák: Doplněn klikatelný odkaz.

Honza S.

Přidáno: 26.12.15 18:00

to Pavel Lasák, problém jsem již vyřešil. Šlo o to, že používám funkci pro vyhodnocení textu jako by to byl vzorec a tam jsou odkazy typu R[1]C[1] velmi praktické, ale je nepříjemné, že pokud mám otevřený i jiný sešit, mám tam také tento typ odkazu. Vyřešil jsem to pomocí jednoduché funkce, kterou přikládám kdyby někdo řešil podobný problém. Honza. Function OdkazRC(pos_radek As Integer, pos_sloupec As Integer) Dim radek As Integer Dim sloupec As Integer radek = Application.ThisCell.Row sloupec = Application.ThisCell.Column OdkazRC = Cells(radek + pos_radek, sloupec + pos_sloupec) End Function

Adam J.

Přidáno: 21.01.16 16:49

Dobrý den, měl bych docela praktickou otázku. Potřeboval bych nějak vyřešit automatické odkazování z četných listů. Mám list na kterém je šablona se vzorečky, která představuje výdej potravin za jeden den. Tento list je pojmenován číslem. Těchto listů mám 22. Poté potřebuji v listu 'měsíční přehled' odkaz na stejnou buňku v v daném listu, což je nějak takto ='1'!$L$3. Nyní bych potřeboval nějak udělat, když to roztáhnu, aby se automaticky název listu změnil na ='2'!$L$3 ='3'!$L$3 atd. Snad je to k pochopení

Pavel Lasák

Přidáno: 26.01.16 21:16

To Adam J.: Nepomůže funkce: NEPŘIMÝ.ODKAZ

Adam J.

Přidáno: 27.01.16 10:26

To Pavel Lasák: to má za následek stejný efekt, jak popisuji výše. Spíše mi jde jen o to, aby se automaticky měnil název listu a odkaz na buňku zůstal stejný.

Pavel Lasák

Přidáno: 31.01.16 22:40

To Adam J. Když se tam doplním pomocný sloupec (řádek) tak to funguje.

Adam J.

Přidáno: 01.02.16 10:29

Tak to tu zkouším snad už 2 hodiny a nějak si už nevím rady je to na mě asi moc složité. Z nepřímého odkazu jsem pochopil, že je to odkaz na odkaz z textového řetězce. Však vykoumat, aby to fungovalo tak, jak já chci je problém. Těchto pomocných sloupců bych musel mít pro každý souhrn jeden, což je kontraproduktivní nebo jsem to špatně pochopil

Adam J.

Přidáno: 01.02.16 12:26

Ufff, tak jsem to konečně vyřešil přesně, jak jste říkal přes nepřímý odkaz, avšak ještě jsem si musel pohrát s uvozovkami a ampersandy pro připojování text. řetězců a samozřejmě pomocný sloupec je tam též. Napíši sem tedy příklad, jak to má vypadat: =NEPŘÍMÝ.ODKAZ("'"&$P3&"'!$L$3") možná by bylo dobré to přidat k příkladům u tématu NEPŘIMÝ.ODKAZ