MS Excel

Ahoj,
riešil som nasledujúci problém (mám už vyriešené, ale zaujímalo by ma, aký postup by použil niekto ďalší).
Zadanie: Odstrániť z tabuľky začínajúcej na riadku 10 duplicitné záznamy pre krajinu aa a dodávateľov aaa a bbb. V sheete som vyznačil záznamy, ktoré majú byť odstránené. Tzn. konkrétny dodávateľ nemusí byť iba u krajiny aa a naopak, nie na všetkých dodávateľov u krajiny aa je možné aplikovať odstránenie duplicitných záznamov. V reále má tabuľka viac než 100 tis. položiek a spústu stĺpcov a pár dodávateľov, u ktorých majú zostať iba neduplicitné položky. Z uvedeného dôvodu sa nie ako vhodné javilo použite nejakého pomocného stĺpca, cez ktorý by sa dalo potom filtrovať. Resp. vzorec, ktorý som použil ja, sa pre tak veľký rozsah dát, napriek tomu, že počítal správne, javil nad možnosti Excelu 2007 na stroji, ktorý mám v novej práci k dispozícii. Hľadal som preto riešenie cez rozšírený filter. Zrejme sa podarilo, ale otestujem až v práci, či sa s tým moja stará plečka v práci popasuje. Otázka teda znie: príde niekto na to, ako uvedený problém riešiť rozšíreným filtrom, prípadne vzorcom, ktorý uvedené spočíta rýchlo a excel u toho nebude zamŕzať? Bez toho, aby boli dáta sortované a pod. Hľadám non VBA riešenie. Uviedol som, že riešenie poznám a na záver uverejním, zaujíma ma, ako by sa s tým popasoval niekto iný.

Zaslat odpověď >

Strana: « předchozí 1 2

lubo(10.3.2016 1:23)#030684

Pár poznámek.

Řešit toto bez třídění je horor. Nástroje, jako PQ, to dělají někde uvnitř.

Třídění pomůže při identifikaci položek k odstranění.
Dále doba odstraňování více záznamů z podobně velkých datech je úměrná počtu samostatných bloků. To nejhorší co lze udělat je odstraňovat položky po jedné. (Pokud je jich víc než jedna až několik.)

Pokud si zadavatel neváží svého času, zamysli se na vzorci:

=KDYŽ(
SOUČIN.SKALÁRNÍ(
(($G$11:G11&$H$11:H11)=([@country]&[@vendor]))*
([@country]=tblCountry[country])*
COUNTIF(tblVendor[vendor]; [@vendor]))

<2; "keep"; "delete")

Na každém řádku spojí sloupce porovná a vytvoří sloupce shoda/neshoda.
Každá položka vytvořeného vektoru se násobí. To se děje i v případě, že pro danou country a nebo dodavatele se duplicity nemažou.

Tedy ohromný počet výpočtů, současně SOUČIN.SKALÁRNÍ není nejefektivnější funkce.

1. Pomocný sloupec s [country]&[vendor]
2. Samostatně (více sloupců nebo vnořené KDYŽ)
když(country; nechat;
když(dodavatel;nechat;
když(unikátní;nechat;smazat)))

Tak se eliminují zbytečné operace.

K hledání unikátních položek je vhodná funkce POZVYHLEDAT (MATCH):
=POZVYHLEDAT([@country]&[@vendor];[country]&[vendor];0)

Funkce vždy najde. Stačí výsledek porovnat s akt. řádkem.

Funkce je v nejhorším případě (nejsou duplicity) stejně rychlá jako countif. Doba se zkracuje při vyšším počtu duplicit (Pokud funkce najde shodu končí, countif počítá do konce). A samozřejmě záleží na pozici v seznamu.

Lze to trochu modifikovat:
když(country; řádek;
když(dodavatel;řádek;
když(unikátní;řádek;0)))

Pak lze odstranit duplicity (zatím jsem netestoval, jestli je rychlejší odstranění duplicit nebo odstranění vybraných.)citovat

AL(10.3.2016 21:08)#030715

lubo, ďakujem za points, ale vôbec nie som v stave pochopiť túto časť Tvojho príspevku:

1. Pomocný sloupec s [country]&[vendor]
2. Samostatně (více sloupců nebo vnořené KDYŽ)
když(country; nechat;
když(dodavatel;nechat;
když(unikátní;nechat;smazat)))

Tak se eliminují zbytečné operace.

K hledání unikátních položek je vhodná funkce POZVYHLEDAT (MATCH):
=POZVYHLEDAT([@country]&[@vendor];[country]&[vendor];0)

Funkce vždy najde. Stačí výsledek porovnat s akt. řádkem.

Funkce je v nejhorším případě (nejsou duplicity) stejně rychlá jako countif. Doba se zkracuje při vyšším počtu duplicit (Pokud funkce najde shodu končí, countif počítá do konce). A samozřejmě záleží na pozici v seznamu.

Lze to trochu modifikovat:
když(country; řádek;
když(dodavatel;řádek;
když(unikátní;řádek;0)))

Pak lze odstranit duplicity (zatím jsem netestoval, jestli je rychlejší odstranění duplicit nebo odstranění vybraných.)

Buď nechápem, ako to myslíš, alebo je možné, že nechápeš presne Ty, čo potrebujem dosiahnuť ja. Ja potrebujem identifikovať tie bunky, ktoré som v riešení označil žltou farbou a ku ktorým som dal do listu advanced filter printscreen želaného výsledku.
Na to, že skalárny súčin nebude schodnou cestou pri použití pomocného vzorca, som prišiel. Samozrejme, pokiaľ dáta zotriedim podľa krajiny a podľa dodávateľov, tak to bude realizovateľné i pomocou vzorca, ktorý som zostavil. Ja som sa ale triedeniu dát chcel vyhnúť a napadlo ma riešiť to cez rozšírený filter. Vtip je v tom, že ten filter v 2016 funguje, ale v 2007 nie. Zaujímalo ma, prečo tomu tak je.
Inak ale, pokiaľ by si bol tak láskavý a do mojej prílohy vložil svoje riešenie, ktoré, ako som zmienil, z Tvojho popisu nechápem, tak by som Ti bol vskutku vďačný.citovat

lubo(10.3.2016 23:13)#030720

Nechtělo se přemýšlet, jestli seznam znamená ano nebo ne.

Proč filtr ve 2007 nefunguje nevím, tuto verzi teď nemám. Můžeš zkusit alternativu

=KDYŽ(F11<>tblCountryAF[country];NEPRAVDA;
KDYŽ(JE.NEDEF(POZVYHLEDAT(G11;tblVendorAF[vendor];0));NEPRAVDA;
POZVYHLEDAT(H11;tblSourceDataAF[cv];0)<>(ŘÁDEK(H11)-ŘÁDEK(tblSourceDataAF[[#Záhlaví];[cv]]))))

Příloha: zip

30720_wallchallenge.zip (9kB, staženo 28x)

citovat

AL(11.3.2016 0:25)#030721

Lubo, ďakujem za osvetlenie, z tej prílohy som to pochopil. Pekný vzorec. Skúsil by som ešte eliminovať pomocný stĺpec cv, v tom prípade ale zase bude treba maticový vzorec, a to sa uvidí, či bude použiteľné na veľkom rozsahu dát: =IF([@country]<>tblCountry[country];ROW();
IF(ISNA(MATCH([@vendor];tblVendor[vendor];0));ROW();
IF(MATCH([@country]&[@vendor]; [country]&[vendor]; 0)=(ROW()-ROW(tblSourceData[[#Headers];[country]]));ROW();0))). Ten rozšírený filter vyskúšam až zajtra, či pobeží na 2007, keby to šlo, tak to by bola špica, na 2016 funguje. Ráno dám vedieť, zatiaľ veľmi pekne ďakujem.

edit: Teraz som si uvedomil, že nedokážem v kritériu rozšíreného filtra eliminovať stĺpec cv, nakoľko to kritérium, pokiaľ zapíšem pomocou maticového vzorca, tak to nefunguje ani v 2016. Ako kritérium by som teda musel použiť zápis:
=IF(G11<>tblCountry[country]; FALSE;
IF(ISNA(MATCH(H11; tblVendor[vendor];0 ));FALSE;
MATCH(I11;tblSourceData[cv];0 ) <> ROW(G11)-ROW(tblSourceData[[#Headers];[country]])))

Takže budem filtrovať priamo v tabuľke buď pomocou vzorca Tvojho, alebo mojej maticovej modifikácie hore uvedenej, prípadne ešte zjednodušenej:=IF([@country]<>tblCountry[country];FALSE;
IF(ISNA(MATCH([@vendor];tblVendor[vendor];0));FALSE;
MATCH([@country]&[@vendor]; [country]&[vendor]; 0)<>(ROW()-ROW(tblSourceData[[#Headers];[country]]))))
Rozšírený filter je proste passé, zdá sa. Ráno sa každopádne ozvem.citovat

AL(11.3.2016 9:45)#030724

Lubo, Tvoje riešenie na 2007 funguje. Moje pokusy dostať to celé do jedného vzorca zlyhali, Excel to proste v tom maticovom tvare nezvládne prepočítať. Využijem riešenie, ktoré si mi poslal. Vyhnem sa aspoň triedeniu, pridať do tabuľky dva výpočtové stĺpce zas nie je až taká tragédia, hlavne, že to funguje. Ďakujem ešte raz.citovat

lubo(11.3.2016 10:07)#030726

K tomu řešení bez pomocných sloupců - je tu něco za něco. Bez pomocných sloupců se sloupce spojují v každém vzorci, při velkém počtu vzorců to stojí dost času a také dost zdrojů.citovat

Strana: « předchozí 1 2

Téma: Challenge - mazanie záznamov

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje