Excel už má zabudovaný webový scraper, k tomu ho používám

Web scraping pravděpodobně zní jako něco vyhrazeného pro vývojáře a hackery, ale může to být stejně jednoduché jako získávání dat z webové stránky přímo do Excelu bez ručního kopírování a vkládání. Místo přepínání mezi prohlížečem a tabulkou zůstáváte v Excelu, vložíte adresu webové stránky a data se téměř okamžitě objeví ve vašem sešitu.

Ačkoliv to může znít složitě, nepotřebujete Python, rozšíření prohlížeče ani žádné znalosti programování. Pokud máte přístup k Power Query ve své aplikaci Excel, což byste měli mít, pokud používáte Excel 2016 nebo novější, můžete načíst téměř cokoli, od jednoduché produktové tabulky až po živý API zdroj.

Použijte Power Query k načtení tabulek, souborů a API do svého tabulkového sešitu

Není potřeba žádný kód ani externí nástroje pro získávání dat

Nejlepším vstupním bodem pro web scraping v Excelu je volba Z webu v Power Query, kterou najdete ve skupině Získat a transformovat data na kartě Data. Vložte URL a Excel se automaticky pokusí detekovat všechny tabulky na stránce. Když to funguje, můžete do své tabulky získat čistá, obnovitelná data jen několika kliknutími. Samozřejmě, webové stránky nejsou vždy tak spolupracující.

Když web nezobrazuje svá data v pěkně formátovaných tabulkách, funkce Power Query Přidat tabulku pomocí příkladů vám stále může pomoci získat to, co potřebujete. Místo toho, abyste se spoléhali zcela na automatickou detekci, vám tato funkce umožňuje ukázat Excelu, jaký typ dat chcete. Můžete zadat několik ukázkových hodnot, jako je název časové osy a její odpovídající rozsah dat, nebo je vybrat z návrhů v rozbalovacím seznamu. Power Query poté pomocí rozpoznávání vzorců stránku prohledá a automaticky vytáhne odpovídající data.

Proces začíná stejným způsobem jako standardní import z webu. Ve skupině Získat & Transformovat Data na kartě Data vyberte Z webu, do zobrazeného dialogového okna zadejte URL webové stránky a klikněte na OK. Pokud vás Excel vyzve k výběru způsobu ověření, Anonymní je obvykle výchozí možnost a vyžaduje pouze kliknutí na Připojit.

Chcete-li použít Přidat tabulku pomocí příkladů, podívejte se do levého dolního rohu okna Navigátor po načtení stránky. Kliknutím na tlačítko se otevře interaktivní rozvržení s živým náhledem webu vedle prázdné tabulky. Odtud můžete přímo zadávat příkladové hodnoty do sloupců a případně přidat další sloupce. Mějte na paměti, že tato funkce funguje pouze s hodnotami do 128 znaků, takže je vhodnější pro kratší pole, jako jsou jména, ceny, identifikátory a data, spíše než pro dlouhé popisy.

Otestoval jsem to na stránce Wikipedie o generacích, protože jsem chtěl jeden sloupec uvádějící generace a druhý obsahující jejich časové rozpětí a popisy. Po přidání dalšího sloupce jsem napsal „Ztracená generace“ a „1883 až 1900,“ a pak vybral odpovídající návrhy z rozbalovacího seznamu. Stejný postup jsem zopakoval u Generace největší, a Excel mi automaticky doplnil zbývající generace. Po tom všem jsem už jen musel vložit tabulku do svého pracovního listu.

Power Query také jde nad rámec stahování standardních HTML stránek. Můžete se připojit přímo k URL souborům, včetně CSV souborů hostovaných na GitHubu, JSON exportů z interních systémů nebo XML zdrojů, a Excel automaticky analyzuje obsah do strukturovaných tabulek. Podporuje také autentizovaná připojení k aplikačnímu programovacímu rozhraní (API), což znamená, že můžete pracovat se službami, které vyžadují API klíče, aniž byste potřebovali samostatný nástroj pro stahování dat.

Pod kapotou se Power Query spoléhá na tři různé funkce v závislosti na typu zdroje, ke kterému se připojujete:

Web.Obsahy	Tato funkce zpracovává obsah mimo prohlížeč, jako jsou CSV a JSON API, s nejširší podporou autentizace.
Web.ObsahProhlížeče	Toto je moderní výchozí volba pro HTML stránky, postavená na enginu WebView2 prohlížeče Microsoft Edge.
Webová stránka	Toto je starší funkce založená na Internet Exploreru, která stále existuje pro kompatibilitu, ale postupně se vyřazuje.

U většiny moderních pracovních postupů budete hlavně pracovat s Web.Contents a Web.BrowserContents.

Vytváření chytřejších webových dotazů

Dynamické URL, parametry řízené tabulkami a pár triků pro weby, které nefungují dobře

Statické dotazy, které čerpají z pevné URL, jsou velmi užitečné, ale Power Query se stává mnohem flexibilnějším, když začnete vytvářet dynamické dotazy. Místo spoléhání se na jeden trvalý odkaz můžete vytvářet URL z jednotlivých částí a kdykoliv vyměňovat parametry, když potřebujete různé údaje. Například, pokud chcete získat informace pro různé předměty, aniž byste museli pokaždé znovu sestavovat dotaz, můžete URL sestavit přímo v Power Query.

V dialogovém okně Z webu vyberte místo Základní možnost Upřesnit. Odtud použijte sekci Části URL k postupnému sestavení adresy. Do prvního textového pole zadejte konstantní část URL, například schéma a cestu:

https://en.wikipedia.org/wiki/

Poté můžete vybrat Přidat část pro vytvoření dalších sekcí pro části dotazu nebo hodnoty proměnných, jako je konkrétní generace nebo země. Například můžete nastavit Část URL 2 na parametr s názvem Topic s hodnotou Generation, a poté nastavit Část 3 na #Social_generation. Při sestavování dotazu Excel generuje živý náhled finální URL.

Po výběru Transformovat data v okně Navigátora vstoupíte do editoru Power Query, kde můžete tyto parametry spravovat efektivněji. Na kartě Domů vyberte Spravovat parametry pro vytvoření upravitelných hodnot, které lze znovu použít v celých vašich dotazech. To znamená, že později můžete změnit parametr Téma z Generace na Zillennials a tabulky ve vašem listu se automaticky aktualizují s daty z této nové stránky.

Můžete to posunout ještě dále tím, že připojíte dotaz přímo k hodnotám uloženým ve vašem tabulkovém listu. To funguje obzvláště dobře, když máte dlouhý seznam identifikátorů, jako jsou NPI čísla poskytovatelů zdravotní péče, ID zákazníků nebo SKU produktů. V editoru Power Query přejděte na kartu Přidat sloupec a vyberte Vlastní sloupec. Tam můžete napsat vzorec, který kombinuje základní URL s hodnotami získanými ze sloupce ve vašem listu.

Například, pokud máte sloupec obsahující sociologické termíny jako Demographic_cohort nebo Cultural_generation, můžete použít takovýto vzorec:

=Web.Page(Web.Contents("https://en.wikipedia.org/wiki/" & Text.Replace(Text.Trim([Column1]), " ", "_"))){0}[Data]

Možná budete muset upravit přihlašovací údaje nebo nastavení ochrany soukromí, pokud jste se na stránku předtím nepřipojili, ale jakmile je to hotové, nastavení by mělo fungovat dobře. Místo vytváření dotazů jeden po druhém můžete načíst data pro stovky položek současně odkazováním na sloupec tabulky.

Pokud stále nemůžete získat přesná data, která potřebujete z webové stránky, mohou vám pomoci vývojářské nástroje ve vašem prohlížeči Chrome (nebo jiném). Otevřete záložku Síť, znovu načtěte stránku a sledujte požadavky, které se načítají na pozadí. Mnoho moderních webových stránek naplňuje své stránky prostřednictvím interních JSON API, se kterými je často mnohem snazší pracovat než s samotnou stránkou. Můžete narazit na endpoint, který vypadá nějak takto:

https://en.wikipedia.org/w/api.php?action=parse&page=Generation&format=json

Připojení Power Query přímo k API koncovému bodu, místo abyste získávali data z viditelné webové stránky, vám obvykle poskytne rychlejší, čistší a spolehlivější výsledky.

Stojí za to se to naučit, i když nikdy nic neodstraníte

Webový konektor Power Query je plně podporovaná funkce, která je průběžně aktualizována a kterou Microsoft zdokonaloval roky. I když nakonec nikdy nezískáte vážné zkušenosti s webovým scrapováním, naučit se, jak funguje, vám stále může ušetřit hodně času.