Standardizace a modifikace URL v Excelu

Praktický návod jak v Excelu standardizovat a modifikovat dataset URL. Zjistíte jak jednoduše a spolehlivě očistit varianty URL s lomítky a bez lomítek na konci, s www a bez www, s http nebo https. Data deduplikovat a získat čistý dataset pro další použití. Také se dozvíte jak URL rozdělit nebo dle potřeby skládat různé varianty URL. Informace oceníte při přípravách přesměrování, práci se složitejšími datasety nebo různých experimentech.

Excel může práci výrazně urychlit. Stačí znát pár správných vzorců. Manuální úkony jsou vhodné jen u opravdu malých data setů. Ani možnost vyhledání a homadného nahrazení (klávesková zkratka CTRL+H) není dostačují. Úkon je nutné obvykle aplikovat několikrát po sobě a některé případy stejně nepomůže vyřešit. Ukážu vám, jak se dá s URL pracovat efektivněji.

Na konci článku naleznete ke stažení Excel dokument, který obsahuje většinu uvedených příkladů a funkčních vzorců.

Obsah článku

• Odstranění HTTP, HTTPS a WWW
• Odstranění lomítek na konci URL
• Standardizace URL do základního tvaru
• Deduplikace a zadání k přesměrování
• Skládání a spojování částí URL
• Nahrazení názvu subdomény a domény
• Nahrazení parametrů, kotev a fragmentů
• Standardizace URL z Internet Archiv Wayback Machine
• Přehled použitých funkcí (česky i anglicky)
• Excel tabulka ke stažení
• Závěr

Odstranění HTTP, HTTPS a WWW

Na vše zatím stačí funkce SUBSTITUTE. Tedy nahrazení řetězce znaků za jiný řetězec. Nejdříve snadné odstranění jednoho elementu z URL.

=SUBSTITUTE(A1;"http://";;1)

Slovní vysvětlení:
DOSADIT ze zdrojové buňky A1 namísto “http://” nic a aplikovat pouze na první výskyt zadaného řetězce.

Pro odstranění více částí URL stačí pracovat se zanořením téhož vzorce.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"https://";;1);"http://";;1);"www.";;1)

Odstranění lomítek na konci URL

Zbavit se lomítek na konci URL je trošku náročnější. Je potřeba odmazat poslední znak z řetězce ve vybrané buňce. Ale protože cílem je odmazat pouze koncová lomítka, je nutné použít podmínku.

=IF(RIGHT(A1;1)="/";LEFT(A1;LEN(A1)-1);A1)

Slovní vysvětlení:
KDYŽ je pravda, že první znak ZPRAVA v buňce A1 je roven lomítku, zpracuj ZLEVA buňku A1, spočítej DÉLKA buňky A1 a vrať výsledek mínus jeden znak. Není-li přechozí podmínka pravda, vrať hodnotu buňky A1.

Standardizace URL do základního tvaru

Nyní stačí spojit všechno dohromady do jednoho vzorce. Vypadá trošku komplikovaně, ale neděje se tam nic záhadného, co byste neznali z výše uvedených příkladů.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(RIGHT(A1;1)="/";LEFT(A1;LEN(A1)-1);A1);"https://";;1);"http://";;1);"www.";;1)

Deduplikace a zadání k přesměrování

V této chvíli máte URL, které je vhodné očistit o duplicity. Nově vzniklý sloupec standardizovaných URL si zkopírujte. Stačí o pár sloupců vedle nebo do nového sheetu.

1. Vyberte celý sloupec s URL k deduplikaci.
2. Přepněte se do záložky “Data”.
3. Vyberte možnost “Remove duplicates”.
4. Zaškrtněte “My data has headers”, pokud je to aplikovatelné, a klikněte na OK.
5. Máte deduplikovaná data, se kterými můžete dál pracovat.

Pokud takto připravujete přesměrování třeba na novou doménu, tak zpravidla už stačí jen vývojáři napsat, aby přesměroval všechny možné varianty (viz níže) dodaných URL.

• http://
• https://
• http:// + www.
• https:// + www.
• http:// + /
• https:// + /
• http:// + www. + /
• https:// + www. + /

Skládání a spojování částí URL

Čas od času si budete potřebovat URL v různých kombinacích vytvořit sami. Do jednoho sloupce si připravte základní řetězce jako “https://” nebo “www.” a do dalších data ke spojení. Ke spojení pak stačí využít funkci CONCAT.

=CONCAT($A$2;B1;$A$4)

Slovní vysvětlení:
SPOJIT buňky A2, B1 a A4. Znak $ před referencí na sloupec a řádek označuje absolutní referenci. Pokud přetáhnu vzorec do dalších buňek, první reference bude vždy A2, třetí bude vždy A4, zatímco relativní odkaz na druhou zadanou buňku se bude dynamicky měnit.

Jak napsat znak dolaru ($):

• Po napsání nebo vykliknutí reference stačí zmáčknout klávesu F4.
• Klávesová zkratka CTRL+ALT+Ů.
• Klávesová zkratka PRAVÝ ALT+Ů.
• Klávesová zkratka ALT+36.

Nahrazení názvu subdomény a domény

Další manipulace s URL může být velice užitečná. Využití je spousta. Jednoduše stačí zanořit do výše uvedeného vzorce vícekrát SUBSTITUTE.

V uvedeném příkladu rozšířeného vzorce odstraňuji http://, https://, www., lomítka na konci a nahrazuji subdoménu “robin” za “batman”. A aktuální doménové jméno za variantu bez pomlčky.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(RIGHT(A1;1)="/";LEFT(A1;LEN(A1)-1);A1);"https://";;1);"http://";;1);"www.";;1);"robin";"batman";1);"link-brain.cz";"linkbrain.cz";1)

Nahrazení parametrů, kotev a fragmentů

Někdy může být užitečné oříznout z URL i parametry za otazníkem (?) nebo třeba část za hashem (#).

=SUBSTITUTE(A1;MID(A1;(FIND("#";A1;1));LEN(A1));;1)
=SUBSTITUTE(A2;MID(A2;(FIND("?";A2;1));LEN(A2));;1)

Oříznutí URL z Internet Archiv Wayback Machine

Specifický případ jsou URL, které vytváří Internet Archive Wayback Machine. Detailněji se budu Internet Archivu věnovat již velice brzy. Zatím jen ukážu, jak tyto URL ořezat do základního tvaru.

Toto je příklad URL: “http://web.archive.org/web/20170128175529/https://link-brain.cz“. Vím, že před částí, kterou chci získat je 5 lomítek. Z toho by šel vzorec vymyslet. Ale jednodušší je pracovat s minimálním počtem znaků, které vím, že vždy budou před pátým lomítkem. To je minimálně 27 znaků. Pak už bude následovat jen jedno lomítko před URL archivovaného webu. Finální součet znaků je tedy 28.

=SUBSTITUTE(A1;LEFT(A1;(FIND("/";A1;28)));;1)

Standardizaci získané URL doporučuji provést až jako druhý krok v novém sloupci.

Přehled použitých funkcí

Excel tabulka ke stažení

Většinu výše uvedených příkladů naleznete v této tabulce. Můžete si vyzkoušet a ověřit, jak jednotlivé vzorce fungují.

[embeddoc url=”https://link-brain.cz/wp-content/uploads/2017/03/2017-03-20-url-standardisation-examples.xlsx” height=”25%” download=”all” viewer=”microsoft” text=”Stáhnout soubor .XLSX s příklady.”]

Závěrem

Automatizace a tvorba dlouhodobě funkčních řešení se vyplácí. Pomocí uvedených vzorců ušetříte spoustu času a zjednodušíte si práci. Čím minimalističtější a jednodušší postup vymyslíte, o to snáž bude udržitelný a modifikovatelný.

Používáte nějaké užitečné vzorce pro práci s URL? Podělte se o ně. Řešíte nějaký konkrétní problém, který jsem ve článku neuvedl? Napište komentář a zkusím vám to pomoci rozlousknout.