[Data validation] Kaskadno vezane liste, padajući meniji

pozdrav, moje znanje excel je vrlo limitirano pa bi molio vasu pomoc, siguran sam da to vama strucnjacima nece predstavljati nikakav problem.

dakle pokusavam napraviti tabelu sa padajucim meniima. planiram napraviti 4 padajuca menia svaki ovisan o predhodnom. prvi bi bio 3 magazina npr. "MENS HEALTH","NATIONAL GEOGRAPHIC", "LIFE", znaci lista bi se nalazila u Sheat1 A1:A3. drugi meni bi bio formati tih casopisa: npr. 100x350 + 3 mm napusta, formati koji bi ovisili o prvom casopicu bi bili od B1:B3, za drugi casopic C1:C4 za treci D1:D3. isto tako bi bili sortirani podatci za treci i cetvrti meni, svi podatci bi bili na Sheat1. problem je sto su podatci za 3 i 4 meni brojevi.

ovo su formule kojim sam se sluzio da bi slozio podatke za prva dva padajuca menia:

selektiram tri magazina u redu A i
1. Insert → Name → Create → i nazovem kategoriju "MAGAZINES".
2. za stupce B, C, D ponovio sam korake iz tocke 1 ali imenovao sam ih "MENSHEALTH", "NATIONALGEOGRAPHIC", "LIFE"
3. Sljedece je da odem na Sheat2 i u A1: Data → Validation → list → i u source bok =MAGAZINES
4. ponovio sam korake iz tocke 3 ali u B2 upisao formulu
=INDIRECT(SUBSTITUTE($A$1;" ";""))

e ovaj dio ide ok, sada nastaju problemi, trebam napraviti 3 i 4 padajuci meni (sa brojevima) koji su ovisni o drugom odnosno trecem padajucem meniu

nadam se da ga nisam previse zakomplicirao
hvala

Vidi ovaj link http://www.contextures.com/DataValDynamic.zip.

Koliko sam razumeo problem, mogao bi da iskoristiš onaj deo sa zadavanjem reference preko pomoćne kolone i VLOOKUP funkcije (Using Items with Illegal Characters), ili još fleksibilnije rešenje pomoću formule koja određuje opseg za validaciju (deo Using Dynamic Lists). Na navedenom linku imaš i primer koji možeš da preuzmeš.

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Jpeca hvala na odgovoru.
kao sto sam rekao moje znanje formula i ostalog u excelu je skroz lose. skontao sam kako napraviti 2- 3 pa cak i 5 padajucih menija u kojem svaki ovisi o predhodnom, sve je ok dok su u stupcima rijeci (sa razmakom ili bez) ali problem je kada su mi podatci u jednom meniju brojevi. npr. uzet cu ovaj primjer u tvom linku.
ako hocu dodati i treci padajuci meni npr.da mi se Aples dijeli na Crvene jabuke, zute jabuke itd. samo cu te podatke staviti u "List sheat" selektirati ta dva i nazvati ga "Aples". onda se prebacim na sheat2 kliknem na D2 i idem Data Validation list i ubacim formulu: =INDIRECT(SUBSTITUTE($A$1;" ";"")) i sto tako ako hocu da mi se Lemons dijeli isti postupak samo u drugu celiju. znaci bitno je da ih imenujem.
isto tako ako hocu cetvrti padajuci meni: npr. da mi Crvene jabuke izbace "5 kg" ili "3 kg" i to je ok jer cu jednostavno tu grupu nazvati "Crvenejabuke" i iskoristim ovu formulu iznad i to je to. e sada problem je ako hocu da mi u petom padajucem meniu "5 kg" izbaci "100 grama" ili bilo kakvu numericku vrijednost, problem je jer ne mogu imenovati tu kategoriju jer mi u name ne prihvaca brojeve i znakove.

ovo me vise izludjuje ako neko ima neki savjet ili link bio bih zahvalan, ja sam vec pregledao vecinu linkova koji se mogu naci na googlu ali nisam nasao dovoljno jasno objasnjenje.
hvala unaprijed

Ako je samo to problem, zašto ne promeniš konvenciju iz primera gde se imenovanje vrši sa npr "Apple" + "Col" u obrnuto: "Col" + "Apple"??

—Samo ćeš neznatno modifikovati formule a problem ti se neće javljati sa brojevima.

Nego, jedan drugi problem tu postoji a koji bi u finalu mogao da ti potopi sav dosadašnji trud. Šta ako postoje isti formati za dva različita časopisa?

Ovaj metod jeste pregledan, štaviše, lako se proširuje novim kategorijama, ali nosi i zahtev da različite kategorije ne mogu imati iste elemente (sem ako se, u toj tački, sami više ne granaju), tj unikatnost mora postojati i među stavkama različtih kategorija (a ne samo unutar liste što se podrazumeva).

Potpis

Have you found helpful examples, solutions or ideas-in-work, please consider the
following:
• Citation of *actual page* you are going to using is welcome.
• Have you saw "Value Added" examples you are going to incorporate to your
profitable blog, daily job or any money making venture: How about online donation?
↗Ana & Vlade Divac Foundation
Hvala!

e to, vidis toliko sam se zanijeo sa proslim ostom da sam skroz zaboravio za ovaj problem. to mi se desilo, posto za svaki casopis imam imam bar dva ista formata. posto sam skroz novi sa formulama i tek ucim osnove ovo je jedina tehnika koju ja poznam.
jel ima neko rijesenje za ovo, neka druga tehnika ili nesto slicno.

hvala na odgovoru

Jedna mogućnost koja zaobilazi formule je korišćenje VBA koda. Ideja je da se liste dinamički popunjavaju u trenutku kad se selektuje odgovarajuća ćelija. Za dinamičko popunjavanje listi može se napraviti kakav god algoritam. U prilogu je Primer gde se druga (povezana) lista popunjava na osnovu pomoćne kolone u kojoj se ispituje vrednost prve liste i pri tome se eliminišu eventualni duplikati - na ovaj način trudio sam se da kod bude što univerzalniji i da ne mora da se menja. Treća lista popunava se na osnovu vrednosti koje su izabrane u prvoj i drugoj listi i takođe se eliminišu eventualni duplikati.
Kod vezano za događaj selekcije ćelije nalazi se uokviru odgovarajućeg lista, dok je ostatak koda u posebnom modulu.

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Imam utisak da Jpeca nije najbolje razumeo problem sa duplikatima; u konkretnom slučaju, različite 'kategorije' mogu imati jednake vrednosti ali unutar 'kategorije', tj kolone sa podacima, podrazumeva se da su sve unikati. (Samim tim, podaci su odvojeni i održavaju se u posebnom radnom listu a ne u kakvoj 'živoj' tabeli sa dnevnim unosima, na primer.)

Ali, dobro, s obzirom da je predlog rešenja dat VBA programiranjem, sasvim je svejedno, prilagodićeš ga već kako ti odgovara.

Razmišljao sam o mogućim alternativama kojima bi se izbeglo pisanje makroa. Jedna od ideja je da obuhvatiš kompletan opseg sa svim podacima i njemu daš ime kao jednoj tabeli (npr SveListe), a onda, u zavisnosti od odabranih parametara, tabelu sužavaš na potrebnu kolonu sa podacima.

Za ovu manipulaciju će ti poslužiti moćna f-ja OFFSET, koja je inače upotrebljena i u primeru sa voćem/povrćem (vidi DataValDynamic.zip), samo još u kombinaciji sa INDEX i MATCH.

Naravno, od značaja je kako ćeš organizovati nove kolone, podrazumeva se da ti organizacija prati nekakvu hijerarhiju među podacima, da bi mogao kasnije da ih lociraš.

Problem je što se traži 5 nivoa dubine; zbog ovoga se vrlo brzo formira teško čitljiva 'kobasica' od f-ja. Po mojoj proceni, moguće je napraviti do 3 nivoa dubine na ovaj način da se ne probije i fizički limit. Zbog toga je nužno odrediti neka pravila; recimo, u prva dva nivoa, pretpostavimo, ne može doći do ponavljanja članova između kategorija (što je i sasvim verovatno), i to odradiš na taj (lakši) način, a preostala tri (gde se ponavljanja očekuju) rešiš sa OFFSET-om.

Takođe, neko kompromisno rešenje bi bilo da napišeš sopstvenu f-ju u VBA koja će ti vraćati traženi niz, već prema unetim odrednicama. Ovo je već mnogo preglednije od ugneždavanja onih nekoliko f-ja koje su ti na raspolaganju. To bi, istina, sad već bio 'makro' ali ti se ne traži da ga pokrećeš; koristiš ga najprirodnije kao i svaku drugu f-ju.

Ne znam da li je moguće, bilo bi interesantno videti rešenje koje bi definisalo klasu za ovo.
Tako da, recimo, svako novo grananje 'rađa' novog potomka postojeće instance (koje joj je roditelj - Parent), pa se za kretanje po stablu koristi svega nekoliko (već poznatih) odrednica - Parent, Child, Ancestor.

Ako neko ume ovo da odradi, bilo bi lepo da postuje ovde. ;-)

Potpis

Have you found helpful examples, solutions or ideas-in-work, please consider the
following:
• Citation of *actual page* you are going to using is welcome.
• Have you saw "Value Added" examples you are going to incorporate to your
profitable blog, daily job or any money making venture: How about online donation?
↗Ana & Vlade Divac Foundation
Hvala!

Problem se sastoji u sledecem:

potrebno je u sheetu 1 napraviti tablicu sa elementima iz sheeta 2

i to na sledeci nacin:

prva kolona sa oznakom vrsta sadzi dva moguca elementa A ili B

potreban mi je padajuci meni kojim se omogucavaju samo ta dva unosa.

druga kolona sadrzi precnike koji zavise od elemenata

takodje je potreban padajuci meni u kome se u zavisnosti od vrste elemenata

(A ili B) pojavljuju precnici koji su povezani sa tim elementima (npr A->5,6,7,8,9,10,11,12)
(b-> 5,7,9,11,13,15,17,19)

treca kolona sadrzi tezine elemenata koje su povezane sa odgovarajucim precnicima,
koji su opet povezani sa odgovarajucim vrstama

Tabela 1 je ono sto zelim da dobijem, pri cemu bi valjalo da se kopiranjem tog bloka koji je
formatiran kako treba odrzi funkcionalnost

Tabela 2 je rucno popunjena i tako bi trebao da izgleda konacni rezultat.

Inace, pregledao sam slicne teme, ali sam potpuni rookie, pa mi treba malo pojasnjenje problematike.

Hvala unapred.

Spaskoni

Iz primera bih rekao da ti nije potrebno tri padajuće liste nego samo dve. Treća karakteristika se računa na osnovu prve dve.
Detaljno uputstvo za formiranje liste za validaciju imaš ovde http://www.elitesecurity.org/t287256-1#1750664

1/ Po opisanom uputstvu imenuješ opseg {A, B} na listu 2 kao Vrsta i postaviš ga kao listu za validaciju za prvu padajuću listu

2/ Za drugu padajuću listu:
a -Imenuješ opsege D4:D11 i G4:G11 sa VrstaA i VrstaB respektivno.
b- Onda napraviš pomoćnu formulu (npr. u ćeliji E4) koja ti na osnovu izbora iz prve padajuće liste formira ime za izvor podataka za drugu padajuću listu (A-> VrstaA, B->VrstaB). Pogodnim izborom imena formula je jednostavna:

c- Na kraju kreiriaš drugu padajuću listu. U Data Validation dijalogu za Source unesi

3/ Treća karakteristika može da se odredi pomoću VLOOKUP funkcije
a - Imenuj opsege D4:E11 i G4:G11 sa lista 2 kao TabA i TabB
b - Napravi pomoćnu formulu koja ti npr. u F4 koja određuje koja tabela se koristi zavisno od izbora prve padajuće liste (slično kao u prethodnom koraku)

c - Na kraju odredi treću karakteristiku pomoću formule

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

za Jpecu:

ako zelimo da celije koje si povezao (red 4) iskopiramo u neki drugi red, da bismo prosirili tabelu, u koloni C, u svim celijama koje su iskopirane, pri proveri data validacije uvek imamo source=INDIRECT($E$4). Za n-ti red potrebna je formula source=INDIRECT($E$n). Kako to uraditi?

Direktan odgovor na tvoje pitanje je izostavi znak $ ispred 4 i kopiranje će se obavljati po relativnim adresama.
Ja bih predložio drugo rešenje koje možeš naći na forumu da dodaš dugme "Unos" koje će nakon izbora kopirati izabrane vrednosti u posebnu listu.

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Pozdrav svima,
@JPeca

Bio bih ti zahvalan ako bi mi pomagao da resim problem.I ja imam isto pitanje kao wolfbrother,odnosno cimi mi se da i mene muci isti problem.Imas dole
prikaceni fajl u Excelu gde sam pokusao da ti objasnim u cemu je problem.
Sta, samo se Vas dvojica razumete u ove stvari,pa gde je jos experata za ovu problematiku.Ajde prikljucite se,svaka pomoc je dobrodosla!

Unapred hvala na pomoci!

Pozdrav Wolfbrother!

Iz prikačenog fajla ne vidim u čemu je problem - izgleda mi OK?

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

I jeste OK, sto se tice rezultata.

Mada, na fajlu su u tabeli SUMA rucno uqcani brojevi i nema nema nikakvog formatiranja, nikakvih formula, nikakvih referenci. Mislim da je spaskoni postavio resenje kao i u prvom postu na temi i da oceqje pomoc (diskusiju) o varijantama za resavanje ovakvog problema. Zar ne, Spaskoni?

Tabelu Suma nisam ni gledao. Ukoliko se ona ručno formira za sve parove Vrsta/Prečnik i u njoj treba da budu sume težina navedenih u tableli 1 onda može

1- Imenuj kolone iz prve tabele npr. T1Vrsta, T1Precnik i T1Tezina.
2 -U koloni Težina tabele SUMA uneti formulu

pa je kopirati za sve specificirane slučajeve.

Takođe moguće je sumu dobiti korišćenjem matričnih (Array) formula.

Drugi način je da se tabela kreira kao izvedena (Pivot) tabela na osnovu unetih podataka je što je po meni bolje rešenje.

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

zanimljiva stvar...



isprobao sam ovu formulu i prijavio mi je gresku.

onda sam zamenio zarez sa tackom i zarezom (;) i proradilo je...



U helpu excela za ovu funkciju je data sintaksa koja se poklapa sa tvojim kodom, gde su redovi odvojeni zarezima.

SUMPRODUCT(array1,array2,array3, ...)

u cemu je stos?

U svim funkcijam u Excelu za razdvajanje argumenata koriste se , ili ; zavisno od regionalnog setovanja operativnog sistema -srpski ; - US/Engeski ,

U Helpu nema ovakav primer korišćenja SUMPRODUCT ali je to 3okc obradio na forumu http://www.elitesecurity.org/t282312-0#1686250. Na osnovu prvog argumenta dobije se niz 0 i 1 - nula na poziciji kad je nije zadovoljen uslov, a 1 kad jeste. Ovaj niz se množi sa odgovarajućim članovima iz niza težina pa se dobijeni proizvodi sabiraju.

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

zanima me jos jedna stvar...

ako ne znamo koliko ce redova biti u tabeli, kako resiti problem?

uvek mozemo rezervisati stotinak celija u svakoj koloni i njima dati ime T1Vrsta, T1Precnik i T1Tezina i na taj nacin obezbediti da se proracun odvija na isti nacin kao do sada. Postoji li neko elegantnije resenje, da se T1Vrsta, T1Precnik i T1Tezina sami formiraju na osnovu broja redova?

hvala ti na svim prethodnim odgovorima...

Pozdrav,potpuno se slazem sa wolfbrotherom,mislio sam da mi izbacite nekoliko varijanti kako da resim problem na sto laksi nacin.
Hvala na pomoci,ja sam moj problem resio.

Jedan je Marco Pantani!!!

Postoji http://www.contextures.com/xlNames01.html

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?