1

Excel: Standardizace a modifikace URL

Chaoticum publikoval

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

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.

A B
1 http://link-brain.cz link-brain.cz

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.

A B
1 http://link-brain.cz link-brain.cz
2 http://www.link-brain.cz link-brain.cz
3 https://link-brain.cz link-brain.cz
4 https://www.link-brain.cz link-brain.cz
5 www.link-brain.cz link-brain.cz

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.

A B
1 http://link-brain.cz/ http://link-brain.cz
2 http://link-brain.cz http://link-brain.cz

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

A B
1 https://www.link-brain.cz link-brain.cz
2 www.link-brain.cz/ link-brain.cz
3 https://link-brain.cz/ link-brain.cz
4 https://www.link-brain.cz/kontakt link-brain.cz/kontakt
5 https://link-brain.cz/kontakt/ link-brain.cz/kontakt

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.

A B C D
1 http:// link-brain.cz https://link-brain.cz/
2 https:// link-brain.cz/kontakt https://link-brain.cz/kontakt/
3 www. link-brain.cz/nastroje https://link-brain.cz/nastroje/
4 / link-brain.cz/nastroje/url-opener https://link-brain.cz/nastroje/url-opener/

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.

A B
1 https://www.link-brain.cz linkbrain.cz
2 https://robin.link-brain.cz batman.linkbrain.cz
3 https://robin.link-brain.cz/kontakt batman.linkbrain.cz/kontakt

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 (#).

A B
1 https://link-brain.cz#!joker https://link-brain.cz
2 https://link-brain.cz?_escaped_fragment_=riddler https://link-brain.cz

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.

A B
1 http://web.archive.org/web/20170128175529/https://link-brain.cz https://link-brain.cz

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

Přehled použitých funkcí

Funkce v angličtině Funkce v češtině Typ funkce
SUBSTITUTE DOSADIT textová
IF KDYŽ logická
LEN DÉLKA textová
LEFT ZLEVA textová
RIGHT ZPRAVA textová
MID ČÁST textová
FIND NAJÍT textová
CONCAT CONCAT textová

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

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.

Byl pro Tebe obsah užitečný? Nenechávej si to pro sebe a sdílej!
  •  
  •  
  •  
  •  
  •  
  •  

Comments 1

Napsat komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *