Záhada zmrazené buňky

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.

Když funguje Kontrola chyb...

Když funguje Kontrola chyb…

Ž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.

Buňka s klasickým formátem Text v porovnání se zmrazenou buňkou a "prolomenou" zmrazenou buňkou

Buňka s klasickým formátem Text v porovnání se zmrazenou buňkou a „prolomenou“ zmrazenou buňkou

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:

<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

<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

Klikni a stahuj!