Jak vypsat hodnoty z oblasti VYŘEŠENO

rejze · 20. 4. 2015 20:13:54

Zdravím. Jde o vypsání hodnot obsažených v pomocných sloupcích. Jde o hodnoty v rozsahu 1-13 vytažené funkcí vlookup z databáze k druhům surovin. Stejné číslo se může objevit u více surovin,ale potřebuji vypsat jen jednou každé číslo,které je v dané oblasti obsaženo. Napadlo mne testovat countif a i to funguje,ale musí být jednodušší cesta,protože testovat větší rozsah hodnot by takhle nešlo. Poradí někdo jak na to prosím?

Editoval rejze (3. 5. 2015 07:55:04)

lp. · 20. 4. 2015 23:50:44

Asi by bylo vhodné nejdříve popsat problém. Pak bude možné něco poradit.

neutr · 21. 4. 2015 06:00:51

Countif je pro tento případ nejjednodušší cesta. Když se nad tím zamyslím, tak mne napadne totéž co "lp." Nenapsal jste například co myslíte tímto : protože testovat větší rozsah hodnot by takhle nešlo. Myslíte například místo 13 hodnot například 3000? Nebo tím myslíte počet všech položek seznamu?
Pokud máte velký seznam s mnoha položkami (obecně) tak stroj zatížíte každým typem vzorce. Jediným způsobem jak se vyhnout problému přetížení "ze vzorců navíc" je makro.
V každém jiném případě nějaké vzorce udělat musíte. V případě vzorců "COUNTIF" a očekávaným množstvím 13 různých proměnných jde pouze o 13 vzorců ať je seznam položek jak chce velký. Vzorce pro takto "malý" počet položek bych psal naráz (kopíroval) a jen přepisoval parametr hledaného čísla. Při tom bych asi ani neřešil skutečnost, že některá čísla ze 13 v seznamu být nemusí. Vzorec vrátí nulu. 13 položek se vejde na obrazovku.

Jinak by to asi bylo, když by počet hledaných proměnných přesahoval okno. Na nuly nikdo zvědavý není a tak by to chtělo seřadit. Zde už je potřeba sofistikovanější postupy. Uvedl bych 2 základní.
1. - Pomocný sloupec který vyhodnotí sloupec hledaných hodnot pomocí SMALL.
2. - Manuálně přetřídit sloupec hledaných čísel.
V obou případech je potřeba vzorec COUNTIF vnořit do podmínky IF tak aby se objevilo jen hledané číslo, nebo aby buňka zůstala prázdná (když hledané číslo v seznamu neexistuje). Je samozřejmé, že prohledávaný úsek musí být zadán pomocí absolutních adres. Příklad - vyhledávání ve sloupci C2:C2000 - 13 hledaných čísel. Vzorce umístíme do sloupce "E" - tedy v úseku E1:E13. Pokud jste zručný dá se to udělat jedním "vrzem". V žádném případě by Vám to nemělo zabrat víc nežli 2 minuty. Postup :
A. - Do E1 napíšeme vzorec "=IF(COUNTIF($C$2:$C$2000;1)=0;"";1)"
B. - Zkopírujeme obsah editačního řádku do paměti.
C. - Přepíšeme pole úseku z E1 na E1:E13 + ENTER 2x.
D. - Jsme nyní v buňce E2 a dáme Ctrl+V.
E. - Přepíšeme v editačním řádku 1 na 2 (je tam 2x přepis)
F. - Entrem se přesuneme do E3 a pokračujeme stejným způsobem až do čísla 13.
.......................
G1. - Nakonec úsek E1:E13 setřídíme. Teprve nyní opustíme tento úsek - je hotovo.
G2. - Místo manuálního přetřídění podobně provedeme ve sloupci "F" vzorec "=SMALL($E$1:$E$13;1)".

Pokud je hledaných čísel mnoho postupujeme buď místo zadání hledaného čísla vyjádřením řádku. Tedy například "=IF(COUNTIF($C$2:$C$2000;ROW())=0;"";ROW())". Tohle ale kopírujeme klasicky. (Nikoliv z příkazového řádku). Za ROW() se doplní číslo řádku jako hledaný parametr. Pokud tento vyhledávací seznam začíná nikoliv na prvním řádku, ale například na druhém zadáme "=IF(COUNTIF($C$2:$C$2000;ROW()-1)=0;"";ROW()-1)". Tento systém se ale nesmí třídit. Načítá vždy aktuální řádek a proto to vypadá, že třídění nefunguje.

Jinou cestou je konstrukce která už třídit jde. Původní vzorec "=IF(COUNTIF($C$2:$C$2000;1;"";1)" Rozebereme do více buněk :
1. buňka (E1) - obsahuje samostatně "=" (když tam něco k tomu bude - vyskočí chyba)
2. buňka (F1) - obsahuje "IF(COUNTIF($C$2:$C$2000;"
3. buňka (G1) - obsahuje "číslo 1" (to se bude v dalších řádcích inkrementovat)
4. buňka (H1) - obsahuje ";"";"
5. buňka (I1) - obsahuje "číslo 1" (to se bude v dalších řádcích inkrementovat)
6. buňka (J1) - obsahuje ")"
7. buňka (K1) - obsahuje "vzorec =E1&F1&G1&H1&I1&J1&"
Toto zkopírujeme podle počtu proměnných (hledaných čísel). Kopírované řádky G1 a I1 budou mít připočítávání př: do G2 je dán vzorec =G1+1 a podobně do I2 je dán vzorec =I1+1.
Ve sloupci "K" máme zpětně poskládané vzorce které všechny naráz načteme do paměti a vložíme zpět jen jako čísla a text. Úsek máme stále vybraný a dáme dialogem "Najít a nahradit" rovnítko které nahradíme zase rovnítkem. Stiskněte NAHRADIT VŠE (ve vybraném úseku) a z textu se naráz stanou vzorce.

Domnívám se, že jste neuvedl pro Vás podstatnou záležitost. Countif funguje jak má, ale výsledek je nepřehledný když je hledaných čísel mnoho a konstrukce přehledného řešení je složitá.

Editoval neutr (21. 4. 2015 06:19:54)

rejze · 21. 4. 2015 15:04:03

Jde o kalkulační list pro výrobu pokrmů a tímto bych vypisoval alergeny,kterých je právě 13. U některých surovin je alergenů více,u jiných nejsou vůbec. Zůstanu tedy u countif. Zatím děkuji.
EDIT: Takže jsem to udělal podle původní verze,tzn. Ve sloupci R mám 13 pomocných buněk kde je vzorec =IF(COUNTIF($N$6:$Q$22;1)>0;1&",";"") a v buňce kam se to má vypsat je =(R6&R7&R8&R9&R10&R11&R12&R13&R14&R15&R16&R17&R18&R19) Funguje to,ale Kdyby bylo alergenů např.200 by to znamenalo 200 pomocných buněk kdy vzorec nejde rozkopírovat a je nutno každou buňku ručně editovat a vypsat (R6&...R206) si taky nechci představovat. Proto mne zajímalo jiné řešení. Jak tedy na to?

Editoval rejze (21. 4. 2015 18:59:37)

neutr · 21. 4. 2015 20:18:07

Nemám představu jak to myslíte. Pro Countif mohou být ve stejném sloupci různá čísla. Pak jde zřejmě o to, že každá potravina má různé druhy alergenů. Některá 2, jiná 4 a podobně. Při tom ale nemusí být pro každý alergen samostatný sloupec. Můžeme například čísla zaměnit za písmena a místo čísel používat výpočet : CHAR(buňka)-64. Pro písmeno "A" je CODE(65), tedy Char(A) = 65, "B" = 66 a tak dál.
Potom alergenny 1=A, 2=B, 3=C, 4=D, 5=E, 6=F, 7=G, 8=H, 9=I, 10=J, 11=K, 12=L, 13=M, a případné další například 15=O, 17=Q...... Pokud by bylo čísel více jak asi 97 muselo by se použít místo ASCI UNICODE.
Metoda substitucí (náhrad) za znaky UNIKODE, které jsou unikátní velikostí "čísla" - je jich 16 bitů = cca 64000. Se znaky ASCI mají shodně jen prvních 128 znaků (7 bit). Potom 200 čísel je jako moucha. Použijeme například znaky 301 - 501 potom jednička = UNICHAR(301)-300. Problém je v tom, že znaky UNICODE většinou vypadají stejně. Vizuálně se nedají odlišit - jen výpočtem. Ale stejně je to daleko za předpokládanými potřebami.
UNICODE od čísla 33 do 255 jsou unikátní jako znaky na rozdíl od ASCI 8 bit kde jsou 4 znaky nevhodné (volají se různě, ale vrací zpět stejné jediné číslo). Tuhle blbou vlastnost UNICODE nemá.

Takže řešení asi existuje jen bude jinak uspořádané. Ale nemám jasnou představu jak by to mělo vypadat podle Vás. Ono půjde zřejmě zejména o to jak se alergeny načítají ze zdroje. Tuším, že by to mohlo být pod grafickým kódem, nebo v rozsáhlém textu halabala.
Teprve od toho se zřejmě odvine vhodná koncepce. Takže asi možnosti jsou, ale musel bych to já, nebo někdo jiný vidět + popis a vysvětlení co a proč.

rejze · 21. 4. 2015 20:55:27

Čísla alergenů jsou uloženy v seznamu potravin ve čtyřech sloupcích(nebývá více než 4 alergeny u jedné suroviny) s dalšími parametry k dané potravině. Do kalkulačního listu jsou vypsány funkcí vlookup též do čtyřech pomocných sloupců. První potravina obsahuje kupř. alergeny 1,7,11 druhá žádné,třetí alergen 8 a další znovu 7 atd. Každý z alergenů se ovšem v celkovém výpisu alergenů objeví jen jednou(1,7,8,11) Mám teď funkční řešení,takže jsem se ptal pro zajímavost a taky pro případ,že budu mít podobnou úlohu s větším rozsahem.

lp. · 22. 4. 2015 00:01:58

Tak tedy čistě pro zajímavost proměnný rozsah:

V A1 je max hledané číslo v rozsahu 1 : [A1]
V B2:D11 jsou jsou čísla (ne vše musí být vyplněné

V E1 je maticový vzorec (vloží se trojhmatem ctrl-shift-enter):

=IF(SUM(COUNTIF($B$2:$D$11;ROW(INDIRECT("1:"&$A$1)))>0)>=(COLUMN()-COLUMN($D$1));SMALL(IF(COUNTIF($B$2:$D$11;ROW(INDIRECT("1:"&$A$1)))>0;ROW(INDIRECT("1:"&$A$1));"");COLUMN()-COLUMN($D$1));"")

Vzorec se zkopíruje na potřebný počet polí vpravo (při tažení je vhodné stisknout ctrl)

Nebo použiju nějakou funkci, třeba

public Function Spojit(pole() as variant, optional oddelovac) as String
dim Vysledek
dim i as long, j as long

	if IsMissing(oddelovac) then oddelovac = ", "
	vysledek =	""
	for i = lbound(pole,1) to ubound(pole,1)
		for j = lbound(pole,2) to ubound(pole,2)
			if pole(i, j) <> "" then
				if vysledek = "" then
					vysledek = pole(i, j)
				else
					vysledek = vysledek & oddelovac & pole(i, j)
				endif
			endif
		next
	next
	Spojit = vysledek
	
end Function

a použiji maticový vzorec:

=SPOJIT(IF(COUNTIF($B$2:$D$11;ROW(INDIRECT("1:"&$A$1)))>0;ROW(INDIRECT("1:"&$A$1));""))

Editoval lp. (22. 4. 2015 00:45:22)

ludviktrnka · 22. 4. 2015 12:14:40

rejze napsal(a)

Ve sloupci R mám 13 pomocných buněk kde je vzorec =IF(COUNTIF($N$6:$Q$22;1)>0;1&",";"") a v buňce kam se to má vypsat je =(R6&R7&R8&R9&R10&R11&R12&R13&R14&R15&R16&R17&R18&R19) Funguje to,ale Kdyby bylo alergenů např.200 by to znamenalo 200 pomocných buněk kdy vzorec nejde rozkopírovat a je nutno každou buňku ručně editovat a vypsat (R6&...R206) si taky nechci představovat.

... ten vzorec countif by se dal podpořit ještě jedním pomocným sloupcem kde bude prostě 1,2,3, atd a vzorec na to bude odkazovat R6 = IF(COUNTIF($N$6:$Q$22;S6)>0;S6&",";"") - tím se zajistí jednoduché rozkopírování (případně bez pomocného sloupce lze místo odkazu S6 použít funkci ROW(A1) vracející číslo řádku tedy 1,2,3 ...).

Zapsání dlouhého vzorce R6&R7&R8 ... bych udělal roztažením &R6 dolů (vytvoří se &R6,&R7 atd ...) pak nakopírováním obsahu buněk do příkazového řádku v příslušné buňce mám ten vzorec. Tak bych to dělal při znalosti pouze nejzákladnějších metod :-)

Editoval ludviktrnka (22. 4. 2015 12:22:42)

rejze · 3. 5. 2015 07:53:56

Díky za ochotu. Značím jako vyřešené.

OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

#1 20. 4. 2015 20:13:54

Jak vypsat hodnoty z oblasti VYŘEŠENO

#2 20. 4. 2015 23:50:44

Re: Jak vypsat hodnoty z oblasti VYŘEŠENO

#3 21. 4. 2015 06:00:51

Re: Jak vypsat hodnoty z oblasti VYŘEŠENO

#4 21. 4. 2015 15:04:03

Re: Jak vypsat hodnoty z oblasti VYŘEŠENO

#5 21. 4. 2015 20:18:07

Re: Jak vypsat hodnoty z oblasti VYŘEŠENO

#6 21. 4. 2015 20:55:27

Re: Jak vypsat hodnoty z oblasti VYŘEŠENO

#7 22. 4. 2015 00:01:58

Re: Jak vypsat hodnoty z oblasti VYŘEŠENO

#8 22. 4. 2015 12:14:40

Re: Jak vypsat hodnoty z oblasti VYŘEŠENO

#9 3. 5. 2015 07:53:56

Re: Jak vypsat hodnoty z oblasti VYŘEŠENO

Zápatí