• Úvod
• Excel návod
• Excel šablony
• Nástěnka
• Mobility
• Tipy na výlet
• Galerie
• Fórum

Příspěvky uživatele

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

Použijte KT.

Je to rychlejší na vytvoření i na použití.

Pokud máte Excel 365 nebo 2016, použijte MAXIFS.

1. Ve vzorci se musí násobit sloupec a řádek. Syntaxe zápisu maticových konstant se měnila. Proč, to ví asi jen ms. Dříve středník odděloval sloupce, nyní se sloupce odělují zpětným lomítkem a středník odděluje řádky. Záleží na verzi excelu.

2. Kromě toho, že vzorec funguje, víte jak to funguje?
Pokud ano, zkuste odhadnout kolik zkušených uživatelů ho pochopí. Používání podobných vzorců nepovažuji obvykle za dobrý nápad. Ale nic proti gustu.

@dontom
Ten vzorec by měl vypadat takto:

=INDEX(A1:B10;POZVYHLEDAT(1;SOUČIN.MATIC(--(PROB(ŘÁDEK(A1:A10)*{1\0\0\0};A1:A10*{1\0\0\0}+{0\9\-9\1}^99;1;ŘÁDEK(A1:A10))>=3000);1);0);2)

(Řádky a sloupce mají ve vzorci význam)

Moc nedoporučuji, je založen na tom, jak excel sčítá (a zahazuje "malá" čísla). Moc lidí to nepochopí.

Pokud se nehledá přesně, pak stačí normálně

{=INDEX(B1:B10;POZVYHLEDAT(3100;SUBTOTAL(9;POSUN($A$1;;;ŘÁDEK(A1:A10)));1))}

Do listu si vlož kolečko, nastav velikost barvu, ...

vyber kolečko, ctrl-c, vyber řadu nebo bod, ctrl-v

Smaž kolečko v listu

Záleží na tom, jak to vypadá v listu.

1. zkontrolovat formát (aby to nebyl text).
2. Pokud je to jedno číslo, tak F2 a znovu vložit.
3. Pokud je to sloupec tak text do sloupců
4. pokud je to rozhozené náhodně v listu, tak najít-nahradit, případě desetinného místa hledat "," nahradit ",".
5. Pokud tam desetinná čárka není, tak nahrazovat postupně jednotlivé číslice 0 za 0, 1 za 1, ...

Nebo třeba:
=INDEX($B$1:$B$100;(X11-DATUM(2015;1;1))/7 + 1)

Maticově:

=SUMA(KDYŽ(WEEKNUM(A4:A100+0;16)=I12;B4:B100;""))

Co vrátí:

? format(-657434, "dd.mm.yyyy")

Zkus toto

Sub Zamykani()

Dim bunka As Range
Dim oblast As Range

Set oblast = Range("A1:B2")

ActiveSheet.Unprotect Password:="000"

oblast.Locked = True
For Each bunka In oblast
If bunka.DisplayFormat.Interior.Color = RGB(51, 204, 51) Then bunka.Locked = False
Next bunka

ActiveSheet.Protect Password:="000"
End Sub


Ke kódu Pearsona - tohle bylo psáno pro 2003 a asi starší. Od verze 2007 to funguje jen náhodou, i když pominu problémy s lokální syntaxí vzorců.

Ten odkaz nikdy moc nefungoval
(Formula v podmíněném formátu je lokální - nutno převést, aktivních podmínek může být více + skládání výsledného vzhledu, ..., už si nevzpomínám co ještě)

Zkus vlastnost DisplayFormat

(nelze ji použít ve funkci)

Například si všimni, jak se jmenuje list, který skrýváš.

Opravdu je to Harok?

Přečti si znova na co jsi se ptal.

Pokud je poslední upřesnění opravdu to co potřebuješ. tak si na to napiš vzorec.

Uživatelské menu

Nejste přihlášen(a)

• přihlásit
• registrace

\n

Menu

On-line nástroje

Formulář Faktura

Aktivní diskuse