Porovnavaci script v Exceli

Jen momentální nápad:
Uložit porovnáváné tabulky jako txt soubor a použít PSPad (Nástroje - porovnání textu). Barevně odliší rozdíly.

Chcel by som nieco viac automatizovane...zakaznik ma svoju tabulku firiem, kazda firma ma db (oznacenu bohuzial cislom) a tento subor si spravuje a udrzuje on.
Obcas mi ho posle a ja musim porovnavanim s vystupom z MSSQL zistit, ci nam sedi pocet firiem/db.
Robim to sem tam len, ale ulahcilo by mi to keby to bolo automaticke robotu lebo tych db je cca 100 a prechadzat to po jednej je dost otravne.
PSPAD b to ciatocne vedel vyriesit, ale pride mi to riesenie trosku neprakticke...hlavne ked viem, ze ten Excel to urcite zvlada len ja to s nim neviem.

Excel to určitě umí. Myslel jsem, že ti jde jen o porovnání dvou sloupců s údaji a označit rozdíly. To PSPad s přehledem zvládne.

No nie tak celkom...on ma tabulku, kde je prvy stlpec nazov firmy a druhy rok....atd.
Kazdy rok u kazdej firmy je ina db.
Cize mam firmy, ktore maju 1, 2, alebo 3 db a musim tieto 3 stlpce (roky) porovnat s tym, co mi vyplul SQL server.
Ak by som zistil, ze na SQL mam napriklad db, ktoru on nema (ci naopak) chcel by som to zobrazit.

Priklad...on vymazal db jednej firmy z roku 2019, ale mne to neoznamil. V zozname bola, ale v mojom vypise uz nie (server ju mal oznacenu ako recovering, ale nevedel ju obnovit, uz bola fyzicky vymazana). Bola to sice jeho chyba, ale mne to potom robilo v logoch zalohovacieho skriptu SQL servera bordel lebo mi to hlasilo chybu (skript chcel zalohovat db, ktora tam uz nebola).

Nějak mi to nemyslí. Proč značení názvu databázi nesjednotíte? Má to nějaká přesně daná pravidla, aby šel udělat převodník? Vzorová data by se hodila.

Nebo si počkej na Sikiho s Robertem, pro ty to bude práce na pět minut. Hlavně jim neříkej, že se tvůj zákazník jmenuje paní Piskáčková.

Tie db automaticky robi uctovny program...tabulku si spravil, aby sme vedeli ku ktorej firme je dana db priradena.
Nazvy db teda ovplyvnit nevieme.

Ahoj.
Porovnanie údajov - funkcia VLOOKUP.
Či už sú v jednom súbore v rôznych hárkoch, alebo v rôznych súboroch.
(Ak som teda dobre pochopil dotaz)
IF THEN nie je vhodné, pretože pár k hľadanému výrazu môže byť v cieli na úplne inej pozícii. Budeš však musieť porovnávať kombináciu dát databáza+rok (prípadne ešte ďalší identifikátor)
Ešte to chce podrobnejšie prediskutovanie

Host: Pištáčková To bolo dobré

To vyzera nadejne nicmenej som narazil na 2 problemy.
Prvym je, ze ked spravim funkciu dostanem nieco taketo (hladam hodnotu z tabulky klienta v hodnotacj SQL server (stlpec)):

=VLOOKUP(D4;K4:K81;1;FALSE)

Vysledok je spravny.
Problem je, ze v druhom riadku je funkcia takato:

=VLOOKUP(D5;K5:K82;1;FALSE)

Problem je, ze mi s posunutim hodnoty, ktora sa ma vyhladavat zaroven posuva aj oblast, ktoru prehladava, co samozrejme nechcem (ta zostava rovnaka).
Druhy problem je zaujimavy...ak otocim vyhladavanie, cize hladam hodnotu SQL servera v tabulke, kde su roky a vytvoril ju zakaznik (cize 4x100 poli cca) dostanem vysledok nedostupny-hodnota nie k dispozicii ani vo funkcii, ani vo vzorci.

Prehľadávané pole musí byť uzamknuté voči posunu vplyvom vypĺňania funkciou. Čiže namiesto relatívneho odkazu musí byť použitý absolútny odkaz (pre prehľadávané pole). Dosiahneš to tak, že po zadaní argumentu pre prehľadávané pole stlačíš F4

Tým získaš tvar: =VLOOKUP(D4;$K$4:$K$81;1;FALSE)
Pre nájdené hodnoty to musí fungovať aj opačne. Spravil si tam nejakú chybu

1. No hadze to tu chybu, nie je to tym, ze polia, ktore prehladava su prazdne niektore? Ja tam iny rozdiel nevidim totizto.
Akonahle skusim toto:
=VLOOKUP(K4;B4:E51;1;FALSE)
Dostanem vystup nedostupny.
B4:E51 je tabulka db, kde stlpce su roky, ale napriklad stlpec E je cely prazdny, kedze ide o rok 2022.

2. mam vo finale tento vzorec.
=IF(VLOOKUP(D5;$K$4:$K$81;1;FALSE)=D5;"Zhoda";"Chyba")
Toto vyzera byt ok, ak je v tabulke firiem prazdne pole hodi to chybu Nedostupny namiesto hlasky Chyba...takze asi mu vadia z nejakeho dovodu tie prazdne polia.
Rovnako ked vymazem zo stlpca K (zoznam db z SQL) jednu db namiesto hlasky Chyba dostanem chybovu hlasku nedostupny.

Takze asi robim niekde chybu;o).

F4 zaberá rôznym spôsobom. Pri definovaní argumentu z iného súboru alebo hárku pri stlačení bezprostredne po jeho zadaní zabsolutizuje odkaz na celé pole. Ale nič sa nedeje aj keď sa to takto naraz nepodarí, kedykoľvek môžeš vstúpiť do vzorca, nastavíš kurzor do ľubovoľného miesta ODKAZU resp adresy a stláčaním F4 cykluješ medzi kombináciami relatívny-relatívny, relatívny-absolútny, absolútny-relatívny, absolútny absolútny. Pre teba bude najlepšie zadať odkaz na prehľadávané pole tak, aby si mal aj pred stĺpcom aj riadkom znak dolára, t.j. absolútny. Absolútny odkaz znamená "zamknutý" voči posunu. Preto $K$4:$K$81. Tebe by stačilo aj K$4:K$81, čiže zamknúť len rozsah riadkov, keďže vypĺňaš len vo zvislom smere, čiže k posunom v stĺpcoch nedochádza

Skusil som to upravit rucne pridanim $, ale to nefungovalo, k uzamknutiu doslo az ked som to upravil cez F4.
Mozno som to nejako zle daval, v kazdom pripade pre mna nova a prinosna informacia (aj ked s Excelom pracujem minimalne).

musí to ísť aj ručne. Možno si tam dal ešte medzeru a preto to nešlo.
Sorry, momentálne odchádzam z domu, viem sa ti venovať neskôr. To, že v prehľadávanom poli sú aj prázdne hodnoty, nevadí.
Len sa pravdepodobne nerozumieme v tom, čo robí tretí parameter vo VLOOKUP. Takže:
Prvý parameter je: čo sa má hľadať
Druhý parameter: rozsah v ktorom sa má hľadať, ale POZOR! Hľadaná hodnota musí byť v PRVOM stĺpci hľadaného rozsahu!!!
Tretí parameter znamená, že v prípade, že v prvom stĺpci PREHĽADÁVAN0HO rozsahu nájde hľadanú hodnotu, vráti ti údaj zapísaný v stĺpci, ktorého poradie (počítajúc od prvého stĺpca PREHĽADÁVANÉHO rozsahu) určuje tretí parameter
Štvrtý parameter si nevšímaj, tam musí ísť FALSE
Pravdepodobne narážame na onú potrebnú KOMBINÁCIU spoločnosti a ročníka, ale viac viem až po návrate...

Tak kde sme? Ako vidíš, Siki ponúkol iný spôsob, ale nie som si istý, či to bude stačiť (či už moja verzia alebo jeho), keďže podľa mňa sme ešte niekde v medzikroku ku zdarnému zavŕšeniu.
Obe verzie budú asi potrebovať úpravu vstupov...

Sorry klucí, že se Vám do toho pletu, ale nestačil by obyčejný COUNTIF?

Mohol by, ale stále neviem presne akej štruktúry má dáta.
Hlavne ak má údaje, ktoré potrebuje porovnávať, vo viacerých stĺpcoch.
Ale po ich zlúčení, pre zjednoznačnenie, je pravda, že aj COUNTIF om dostane odpoveď.

A naopak, sme radi, že dávaš tip i na alternatívu

Tak aby som to objasnil pridavam printscreen.

Po ecko je ta abulka od klienta, K stlpec som si tam ja nakopiroval ako vystup z SQL.
Momentalne porovnavam data z tabulky klienta s tymi mojimi, pretoze naopak mi to nefunguje.

No, takže:
vzorec je v podstate v poriadku. Z výsledkov vyplýva:
Bunka G4 hovorí, že údaj z bunky D4 nie je nájdený.
Bunka G14 (a ostatné prázdne) dáva NEDOSTUPNÝ kvôli tomu, že prázdna hodnota sa nevyhľadáva (chyba vo VLOOKUP spôsobí, že nebude vyhodnotená funkcia IF, lebo vnorená funkcia dáva ERROR)
To, čo som písal, že prázdna bunka nevadí, platí pre prípad, že prázdna hodnota je v prehľadávanom poli.
Takže pre opačný prípad, že dáš hľadať napr K5 v poli D4 až D42, prázdne bunky v stĺpci D vadiť nebudú.
Čiže ak ošetríš prípad, že ak bunka v stĺpci D bude prázdna, tak nechaj prázdne (alebo vypíš "prázdne"), tak to bude ok

No ale aby sme to nekomplikovali, v prílohe zasielam príklad (s použitím Tebou použitých stĺpcov). Áno, najideálnejšie je poskytnúť ukážku, ako si spravil, aj keď natvrdo... Mohol si vymazať prvý stĺpec a dať sem "živú" tabuľku, ale nevadí.
V pripojenom súbore máš vzorec aj pre verziu s funkciou VLOOKUP, vrátane podmieneného formátovania, ktoré sa postará o to, že riadok s výsledkom "Chyba" podfarbí
a je tam aj verzia s funkciou COUNTIF. Schválne som tam dal také prípady, aby si si všimol, ako COUNTIF pracuje. Podľa toho by bolo potrebné naň urobiť "nadstavbu", podobne ako si urobil nad VLOOKUP (funkciou IF).
COUNTIF ti totiž udáva počet výskytov v prehľadávanom poli. Takže žiadané výsledky COUNTIF by boli 0 a 1. COUNTIF prázdnu bunku nevyhodnotí ako chybu. Všimni si však, že napriek tomu, že vľavo máš dva výskyty písmena "a" je výsledkom "1", pretože v cieľovom stĺpci sa písmeno "a" nachádza len raz.
Samozrejme, správne by malo byť, že či už v ľavom stĺpci alebo v cieli by sa mal každý prípad vyskytnúť maximálne raz...

Niesom v tomto az tak zbehly, sice mi makra fungovali, ale ked by to niekto videl . . .
a tak sa pytam, co tak index a match ?
zatial som pouzival aj ja vlookup . . . mozno by sme sa viac poducili . . . D.

Nevylučujem ďalšie možnosti, ale osobne nepoužívam funkcie INDEX a MATCH... Možno na moju škodu, ale nebol som nútený ich skúmať, keďže som sa vždy dopracoval k výsledku bez nich. Či už vzorcami alebo makrami. Momentálne neviem preskúmať, lebo som už zas mimo pc. Ale veď daj vzorec, kľudne to môže byť najlepšie riešenie...

Ved praveze ani ja, ja som myslel ci tu nieje niekto kto to pouziva, bolo by mozno vidiet vyhody nevyhody index vs vlookup . . .

SVYHLEDAT (VLOOKUP) - data musí být od prohledávané oblasti napravo. U fce INDEX na pozici dat vůči prohledávané oblasti nezáleží.

Samozrejme, každý stĺpec konkrétneho roka musíš porovnať voči cieľu zvlášť. V opačnom garde, t.j. hľadaní, či je údaj zo stĺpca K v niektorom zo stĺpcov jednotlivých rokov, by som využil funkciu OR v kombinácii s funkciami NOT a ISERROR. Tým vieš jedným multivzorcom zistiť, či údaj zo stĺpca K nachádza medzi údajmi stĺpcov jednotlivých rokov

No a pokiaľ tam nechceme akékoľvek pomocné stĺpce, nuž tak vyhodnocovaní vzorec dáme priamo do podmienky podmieneného formátovania. Viď priložený súbor Hárok 2.
Vzorec však treba upraviť v súlade s pravidlom, že podmienené formátovanie vyfarbí bunku vtedy, ak v ňom zapísanú podmienku vyhodnotí ako TRUE.
Čiže: v prípade, že sa údaj zo stĺpca D nachádza v stĺpci K, podfarbí sa na zeleno, v prípade, že sa údaj zo stĺpca D nenachádza v stĺpci K, podfarbí sa na červeno.
Podľa toho sú v podmienenom formátovaní dve podmienky: kedy vyfarbiť na červeno a kedy na zeleno.
Vhodným kombinovaním relatívnych a absolútnych odkazov stačí zadať dva (SLOVOM: DVA!!!) vzorce do podmieneného formátovania a CELÝ obsah stĺpcov B až E sa RAZOM vyfarbí podľa požiadavky.
Bez akýchkoľvek vyhodnocovacích stĺpcov so vzorcami...
Myslím, že toto je to, čo chceme...

Podobne sa dá ošetriť stĺpec K s tým, že tam bude podmienka formátovania kapánek komplikovanejšia, lebo má dať TRUE pre prípad, že údaj v stĺpci K je nájdený v stĺpci B OR v stĺpci C OR v stĺpci D... čiže výsledok VLOOKUP v stĺpcoch rokov nie je chybou: =OR(NOT(ISERROR(VLOOKUP(údaj K v stĺpci B)));NOT(ISERROR(VLOOKUP(údaj K v stĺpci C)));......)

A keďže Siki "stiahol" podklady pomocou OCR, tak predkladám moju verziu s kompletným podmieneným formátovaním s využitím Sikiho stiahnutých dát , keďže Fleg nám ten luxus neposkytol
V celom poli B4:D42 sú použité len dva vzorce pre podmienku podmieneného formátovania pre porovnanie s poľom K4:K42 (po jednom vzorci pre farbu zhody a pre farbu nezhody)
a v poli K4:K42 sú použité dva vzorce pre podmienku podmieneného formátovania pre súčasné porovnanie so všetkými tromi stĺpcami B4:D42 (taktiež pre dve informačné farby)
Toť vše...
Pokiaľ sa rozrastie počet rokov, stačí v podmienenom formátovaní rozšíriť oblasť platnosti podmienky.
Podobne, pokiaľ sa predĺžia zoznamy o riadky pod riadok 42, analogicky sa upravia rozsahy vo funkcii VLOOKUP a oblasť platnosti podmienky.
Samozrejme, možno to celé predpripraviť na ľubovoľný počet stĺpcov a ľubovoľné dĺžky zoznamov. Ja som to nadefinoval na aktuálne oblasti dát.
Môžeš si to odskúšať tak, že zmeníš niektorý údaj (či už v zdroji alebo v cieli). Odrazí sa to v oboch relevantných poliach.
Prehľad napr negatívnych výsledkov porovnania za konkrétny rok spravíš jednoducho filtrom farieb

Ďalšia verzia, ktorá je už preddefinovaná až po rok 2025 a predĺženými zoznamami po riadok 50.
Plus pridaná farebná kontrola pre označenie spoločnosti, u ktorej stačí, že nevyhovuje čo len jedna bunka v riadku (jeden vzorec pre podmienené formátovanie)
Môžeš odskúšať vyplnením buniek v rozsahu hrubo orámovaných časti, pre ktoré sú preddefinované vzorce a podmienky...

Ale či to bude to pravé orechové, neviem, keďže L-Core už napísal niečo v tom zmysle, že čosi takéhoto s úspechom riešil a priložil odkaz na nástroj
A týmto zatím končím

Takhle vypadá porovnání v PSPadu - to jsem měl na mysli.

A tady je varianta pomocí VBA.
Berte to jen jako hrubý, ale opravdu hrubý nástřel.
Hodně by záleželo, jak se vlastně mají hodnoty porovnávat (roky samostatně, nebo celá tabulka od zákazníka), kolik je vlastně hodnot celkem (myšleno počet řádků 100, 1000, 10 000 ???

PS: Jinak s tím obrázkem má Robert pravdu. Kua Flegu takovej zkušenej matador . Díky bohu za OCR

odstraněna příloha na přání autora (host)

Řešil jsem něco podobného, ale mnohem rozsáhlejšího (hodně přes 10.000 položek), porovnával jsem dosavadní a aktualizované textové stringy jedné hry (Cities Skylines) kvůli překladu. Každý string měl své ID, aktualizací některé ID ubyly, některé přibyly, některé zůstaly, ale změnil se text. Tu novou ENG verzi jsem musel porovnat s poslední přeloženou CZE. Problémem je, že ty ID jsou při každé nové aktualizaci různě zpřeházené, což naštěstí nevadí v použitelnosti. Nakonec jsem v Excelu vytvořil mohutné řešení, které si (po přípravě resourců) s aktualizací relativně snadno poradí, připraví překlopení poslední ENG do CZE a použije vše doposud přeložené, co se nezměnilo. Co se změnilo a je nové, označí. Co zmizelo, označí taky. V něm dopřekládáme každou novou aktualizaci a snadno z ní vyexportujeme data, které dalšími nástroji převedeme do formy použitelné ve hře.

A proč to píšu, vycházel jsem při tom porovnávání "dvou sloupců" z tohoto vzoru: https://appjesyka.files.wordpress.com/2017/11/porovnat.zip
Mrkni do VBA, třeba to nějak pomůže i tobě, pořádně jsem to tady nestudoval..

Dneska sem měl více času, tak se v souboru co jsem sem přikládal více štoural a přišel sem na chybu v kódu (záměna znamének +/-) . Což způsobovalo dvě chybějící hodnoty ve výsledcích!

V příloze je opravený soubor + přidán další modul s kódem pracujícím se slovníkem (Dictionary). Pro jeho spuštění je potřeba reference na knihovnu "Microsoft Scripting Runtime" !!!

Inspirace čerpána z kanálu Excel Macro Mastery

PS: Flegu nebo někdo z adminů odstraňte ten chybný soubor. Díky.