Určitě si vybavíte situaci, kdy číslo v buňce oproti standardu přiléhá k levému okraji, žádná z ikon pro vodorovné zarovnání přitom není aktivní, formát buňky je Obecný a Excel se tváří, jako že je vše v pořádku (neupozorňuje zeleným trojúhelníčkem na číslo uložené jako text). Setkáváte s tím stejně jako já během importu dat do Excelu a máte za to, že se jedná o bug, nebo-li chybu v softwaru? Jak si dnes ukážeme, platí zde víc než jinde, že v případě Microsoftu to není chyba, ale vlastnost softwaru.
Pokud Excel sám nenabízí nápravu převodu na skutečné číslo, pak si vypomůžeme přenásobením buněk jedničkou nebo ruční editací. Prostý přepočet listu v tomto případě nepomáhá stejně jako změna formátu.
Že uvedený stav není chyba, ale spíš nedomyšlená a špatně ošetřená vlastnost, mě přivedlo něco úplně jiného. Pokud existují v sešitu definované názvy, pak jejich výpis do listu zařídí karta Vzorce / skupina Definované názvy / Použít ve vzorci / Vložit názvy / Vložit seznam. A jejda, buňky se vzorci, které Excel vyplivne, mají obecný formát a přesto se sami od sebe nepřepočítají a nezobrazí výsledek. Ani nedošlo k přepnutí do jiného režimu, jak to umí tlačítko Zobrazit vzorce na kartě Vzorce. Tak jak to ten Excel dělá, že umí takhle „zmrazit“ buňku? Dlouho jsem si s tím nevěděl rady. Bylo ale zřejmé, že Excel tuto informaci musí někde uchovávat. Naštěstí už nežijeme v době binárních XLS souborů. Soubory typu XLSX jsou ve skutečnosti komprimované ZIP soubory s určitou strukturou a čitelným textovým obsahem jazyka XML.
Soubor demonstrovaný obrázkem výše jsem uložil, přejmenoval na ZIP a obsažené složky a soubory rozbalil. Předkládám podstatný výtah:
složka worksheets, soubor sheet1.xml:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <sheetdata> <row r="2" spans="2:2" x14ac:dyDescent="0.25"> <c r="B2" s="2" t="s"> <v>0</v> </c> </row> <row r="3" spans="2:2" x14ac:dyDescent="0.25"> <c r="B3" s="1" t="s"> <v>0</v> </c> </row> <row r="4" spans="2:2" x14ac:dyDescent="0.25"> <c r="B4" s="1"> <f>List1!$A$1</f> <v>0</v> </c> </row> </sheetdata> |
vysvětlivka:
element „row“ (tagy <row> a </row>) … Row, řádek
a jeho atribut „spans“ … Spans, rozsah sloupců pokrývající data daného řádku (tj. využitá oblast daného řádku), údaj pro optimalizaci
element „c“ (tagy <c> a </c>) … Cell, buňka
a jeho atribut „r“ … Reference, umístění buňky
a jeho atribut „s“ … Style Index, index užitého stylu buňky (viz složka xl, soubor styles.xml a atributy Number Format obsahující informaci o tom, co známe pod pojmem formát buňky)
a jeho atribut „t“ … Cell Data Type, datový typ
element „f“ (tagy <f> a </f>) … Formula, vzorec
element „v“ (tagy <v> a </v>) … Cell Value, hodnota, poslední vypočtená hodnota ze vzorce nebo index textové položky v Shared Strings (sharedStrings.xml)
složka xl, soubor sharedStrings.xml
1 2 3 | <si> <t>=List1!$A$1</t> </si> |
vysvětlivka:
element „si“ (tagy <si> a </si>) … String Item, textová položka
Je vidět, že definice buněk B2 a B3 se liší pouze v užitém stylu buňky. V souboru styles.xml si ověříme, že zatímco zmrazená buňka B3 má ve stylu uveden numFmtId=“0″ (General, Obecný), předformátovaná buňka B2 má numFmtId=“49″ (@, Text). Obě se přitom odkazují do tabulky sdílených textových řetězců (atribut t=“s“). Prolomená zmrazená buňka B4 je nadefinována odlišně. Její styl je rovněž Obecný, obsah ale určuje element „f“ (uvozuje vzorec, bez znaku „=“) a datový typ není uveden. Ke vzorcům se navíc vztahuje i soubor calcChain.xml, který stanovuje postup přepočtu listu.
Ze standardu Ecma (Standard ECMA-376 Office Open XML File Formats, 4. vydání, 2012) lze vyčíst, že Excel 2010 (XML formát souboru) zná následující datové typy:
- číslo, Number (t=“b“)
- datum, Date (t= „d“)
- pravdivostní hodnota (t=“b“)
- chybová hodnota, Error (t=“e“)
- textový řetězec, String (t=“str“ pro text coby výstup ze vzorce), Shared String (t=“s“ pro sdílené textové řetězce naformátované na úrovni celé buňky) a InlineString (t=“inlineStr“ pro komplikovanější, po částech naformátovaný textový řetězec)
Po dnešku jsem tedy snad nejen já o trochu chytřejší a budu si pamatovat, že vzhled a chování buňky neřídí jen její formát, ale že jej ovlivňuje i přiřazený datový typ (Cell Data Type, konkrétně atribut „t“ tagu <c>). Jak vyrobit „zmrzlinu“ běžnými prostředky? Kombinaci „Number Format = 0“ a datového typu Shared Strings asi dost dobře nejde buňce vnutit z prostředí Excelu. Už jen proto, že jakákoliv editace zmrazenou buňku prolomí. Ledové pasti ale nastražit můžete, stačí přenést formát z existující zmrazené buňky na jinou (předformátovanou jako Text). Buňka odkazující se vzorcem na zmrazenou buňku si rovněž přebírá její formát.
Soubor s příkladem:
excel-zmrazena-bunka.zip