< návrat zpět

MS Excel


Téma: vyhledávání s maticí rss

Zaslal/a 20.8.2020 3:27

FantasykZdravím,
potřeboval bych vyřešit takový problém...
Mám v listu STOPS tabulku ( zdroj pro vyhledávání )
pak tam je list DISORDER_A
tam je uveden ve sloupci A Lis (vyhledávací parametr) potřeboval bych:

Lis 700136 ( v listu STOPS ozn. žlutě )
aby to vyhledalo, když ve sloupci K+M něco najde ozn. červeně přepsalo do buňky v DISORDER_A, ( označeně červeně )
a tak samo převedlo i ten čas ( označené zeleně ) .

Př.
v listu DISORDER_A-
v řádku kde je 700136 ve sloupci (E - Porucha 1) - napsalo:
Přestavba forem - oprava posma a ve sloupci (F čas) napsalo:30 Sloupec (H - Porucha 2) napsalo: Sklad supportu a sloupci (F čas) napsalo: 3
a těch poruch tam může být i 20

Děkuji moc za pomoc

Příloha: zip47732_report2020_v1.04.xlsm.zip (160kB, staženo 5x)
Zaslat odpověď >

#047743
elninoslov
No skúste, či to bude použiteľné...
Příloha: zip47743_report2020_v1.04.zip (178kB, staženo 9x)
citovat
#047751
Fantasyk
Dekuji mockrat,
To by me nebapadlo, jelikoz to ani nechapu jak jsi to udelal..
Mohl by jsi mi to trochu vysvetlit?

Dikcitovat
#047755
elninoslov
Vysvetliť ??? To by bolo na dlho. Skúsim to iba krátko popísať.
Pre zjednodušenie chápania vzorcov je použitých niekoľko Definovaných názvov:

PODSKUPINA
=OFFSET(STOPS!$K$1;MATCH(DISORDER_A!$A2;STOPS!$B$2:$B$1998;0);;COUNTIF(STOPS!$B$2:$B$1998;DISORDER_A!$A2))
=POSUN(STOPS!$K$1;POZVYHLEDAT(DISORDER_A!$A2;STOPS!$B$2:$B$1998;0);;COUNTIF(STOPS!$B$2:$B$1998;DISORDER_A!$A2))
MATCH nájde pozíciu (druhý riadok má index 1), kde sa nachádza lis
COUNTIF zistí počet záznamov toho lisu
OFFSET použije tieto 2 hodnoty na nastavenie oblasti podskupiny (predpoklad je, že sú pokope zoskupené !!!)
Pri zadávaní tohto vzorca do Def. názvu je potrebné stáť na 1. výsledkovej bunke E2.

POZNAMKA
=OFFSET(PODSKUPINA;;2)
=POSUN(PODSKUPINA;;2)
Poznámka je tá istá oblasť, len posunutá o 2 vpravo

PROSTOJ
=OFFSET(PODSKUPINA;;-5)
=POSUN(PODSKUPINA;;-5)
Prostoj je opäť tá istá oblasť, len posunutá o 5 vľavo

CHYBY
=((PODSKUPINA<>"")*1)+((POZNAMKA<>"")*2)
=((PODSKUPINA<>"")*1)+((POZNAMKA<>"")*2)
Kvôli spájaniu pomlčkou " - " treba rozlíšiť 4 stavy:
-žiadna chyba ani v Podskupine, ani v Poznámke (0 nebude nič písať)
-chyba iba v Podskupine (1 nebude dávať za ňu pomlčku)
-chyba iba v Poznámke (2 nebude dávať pomlčku pred ňu)
-chyby aj v Podskupine aj v Poznámke (3 bude medzi ne dávať pomlčku)
tie spočítané príznaky (0,1,2,3) sa nakoniec použijú v CHOOSE/ZVOLIT na určenie toho, čo sa má udiať ohľadom pomlčky.

IDX_CHYBY
=IF(CHYBY>0;ROW(PODSKUPINA)-MIN(ROW(PODSKUPINA))+1)
=KDYŽ(CHYBY>0;ŘÁDEK(PODSKUPINA)-MIN(ŘÁDEK(PODSKUPINA))+1)
Keďže niektoré riadky v oblasti lisu nemusia obsahovať chybu, potrebujeme si zistiť indexy tých v oblasti, ktoré chybu majú. Takže otestujeme predošlý názov CHYBY na číslo >0 a priradíme mu vypočítaný index (teda riadok oblasti lisu mínus prvý riadok oblasti lisu [MIN slúži na určenie 1. riadku, dá sa použiť aj MATCH ako v prvom vzorci])

PORADIE_CHYBY
=SMALL(IDX_CHYBY;INT((COLUMN()-5)/3)+1)
=SMALL(IDX_CHYBY;CELÁ.ČÁST((COLUMN()-5)/3)+1)
No a tento Def. názov už iba vracia postupne od najmenšieho indexy, v ktorých sa nachádza nejaká chyba (predchádzajúci Def. názov IDX_CHYBY). Poradové číslo chyby sa mení vždy každé 3 stĺpce. Teda E,F,G = 1; H,I,J = 2; K,L,M = 3 ...

Kompletujúci vzorec je
=IFERROR(CHOOSE(INDEX(CHYBY;PORADIE_CHYBY);INDEX(PODSKUPINA;PORADIE_CHYBY);INDEX(POZNAMKA;PORADIE_CHYBY);INDEX(PODSKUPINA;PORADIE_CHYBY)&" - "&INDEX(POZNAMKA;PORADIE_CHYBY));"")
=IFERROR(ZVOLIT(INDEX(CHYBY;PORADIE_CHYBY);INDEX(PODSKUPINA;PORADIE_CHYBY);INDEX(POZNAMKA;PORADIE_CHYBY);INDEX(PODSKUPINA;PORADIE_CHYBY)&" - "&INDEX(POZNAMKA;PORADIE_CHYBY));"")
V ňom sa na základe CHYBY (viď popis) rozhoduje či sa uplatní
1 - chyba iba v Podskupine
INDEX(PODSKUPINA;PORADIE_CHYBY)2 - chyba iba v Poznámke
INDEX(POZNAMKA;PORADIE_CHYBY)3 - obe chyby
INDEX(PODSKUPINA;PORADIE_CHYBY)&" - "&INDEX(POZNAMKA;PORADIE_CHYBY)0 - stav bez chyby ošetruje IFERROR, lebo CHOOSE s parametrom 0 dá chybovú hodnotu (musí začínať od 1), a to vyrieši IFERROR.

To bez chyby sa dá urobiť aj tak, že sa k CHYBY pripočíta 1 a riešenie stavov v CHOOSE bude
1 - bez chyby
""2 - chyba iba v Podskupine
INDEX(PODSKUPINA;PORADIE_CHYBY)3 - chyba iba v Poznámke
INDEX(POZNAMKA;PORADIE_CHYBY)4 - obe chyby
INDEX(PODSKUPINA;PORADIE_CHYBY)&" - "&INDEX(POZNAMKA;PORADIE_CHYBY)

Načítanie času je jednoduché, podľa rovnakého princípu indexu chyby

Čas v minutách, tu som urobil chybu, treba
=IF(F2="";"";MINUTE(F2))
=KDYŽ(F2="";"";MINUTA(F2))
opraviť napr. na toto
=IF(F2="";"";TEXT(F2;"[m]")*1)
=KDYŽ(F2="";"";HODNOTA.NA.TEXT(F2;"[m]")*1)

EDIT: Príloha v pôvodnom príspevku vymenená za novú s týmto novým počítaním minút.citovat
#047756
avatar
@elninoslov
Ja som zvedavý, čo budú tvoji "žiaci" robiť, keď sem prestaneš chodiť. 5 10

-------

Manažeři a inženýři

Cestuje skupina inženýrů se skupinou manažerů ve vlaku. Každý manažer má svoji jízdenku, inženýři mají dohromady jen jednu. Najednou jeden z inženýrů volá: "Jde průvodčí!" a všichni inženýři se natlačí do jednoho WC. Průvodčí zkontroluje lístky manažerů a vidí, že dveře na WC jsou zamčené. Zabouchá na dveře a zavolá: "Jízdenku, prosím!" Z WC se pode dveřmi vysune jeden lístek, průvodčí ho cvakne, prostrčí zpět, poděkuje a spokojeně odchází.

Na zpáteční cestě mají manažeři jen jeden lístek a světe, div se, inženýři nemají žádný. Jeden z manažerů uvidí průvodčího a zakřičí: "Jde průvodčí!" a všichni manažeři se utíkají schovat na WC. Inženýři trochu pomaleji odcházejí na další WC. Poslední z inženýrů, ještě než se schová, zabouchá u manažerů a zavolá: "Jízdenku, prosím ..."

Ponaučení: Manažeři často používají inženýrská řešení, aniž by jim rozuměli.citovat
#047757
elninoslov
Beriem to ako 2 frky, a oba dobré 5citovat
#047759
Fantasyk
Luxusni, ale pokud to bylo na me tak nejsem manager :-)
Elninoslov mohl by jsi i ucit za prachy tady to..citovat
#047761
avatar

Fantasyk napsal/a:

Elninoslov mohl by jsi i ucit za prachy tady to..
Aj malú násobilku????

Fantasyk napsal/a:

Luxusni, ale pokud to bylo na me tak nejsem manager :-)
To bolo pre elninoslov. On to pochopí. 10citovat
#048136
Fantasyk
Zdravím,
i díky tvému luxusnímu návodu jsem na to nepřišel.
Chtěl jsem tam dodat ještě směny, jelikož potřebuji v DISORDER_A vyhledávat pouze směny 123 resp. větší než 0.
A v DISORDER 0 vyhledávat pouze směny 0.
Strávil jsem nad tím dva dny a na tohle jsem asi vážně trouba 7 .
Přidával jsem tam všechno co mě napadlo, ale totálně jsem selhal..

EDIT:
momentálně jsem udělal:
pojmenoval buňky takhle s názvem SHIFT:
=POSUN(STOPS!$C$1;POZVYHLEDAT(DISORDER_A!$A2;STOPS!$B$2:$B$1998;0);;COUNTIF(STOPS!$B$2:$B$1998;DISORDER_A!$A2))

a do DISORDER_A jsem přidal podmínku ať to vyhledá SHIFT<>0

a stejnak mi to nejede 7

Mohl by jste mi někdo poradit ?

EDIT2:

a pak jsem zkoušel :
=POSUN(PODSKUPINA;;-8)
, ale se stejným výsledkem nevím na které buňce mám stát a jestli to píšu správně

Děkuji mockrát
Příloha: zip48136_report2020_v1.8.zip (306kB, staženo 5x)
citovat
#048151
Fantasyk
Nevíte někdo? 7citovat
#048218
Fantasyk
Zdravím,
i díky tvému luxusnímu návodu jsem na to nepřišel.
Chtěl jsem tam dodat ještě směny, jelikož potřebuji v DISORDER_A vyhledávat pouze směny 123 resp. větší než 0.
A v DISORDER 0 vyhledávat pouze směny 0.
Strávil jsem nad tím dva dny a na tohle jsem asi vážně trouba 7 .
Přidával jsem tam všechno co mě napadlo, ale totálně jsem selhal..

EDIT:
momentálně jsem udělal:
pojmenoval buňky takhle s názvem SHIFT:
=POSUN(STOPS!$C$1;POZVYHLEDAT(DISORDER_A!$A2;STOPS!$B$2:$B$1998;0);;COUNTIF(STOPS!$B$2:$B$1998;DISORDER_A!$A2))

a do DISORDER_A jsem přidal podmínku ať to vyhledá SHIFT<>0

a stejnak mi to nejede 7

Mohl by jste mi někdo poradit ?

EDIT2:

a pak jsem zkoušel :
=POSUN(PODSKUPINA;;-8)
, ale se stejným výsledkem nevím na které buňce mám stát a jestli to píšu správně

Děkuji mockrát
Příloha: zip48218_report2020_v2.2.zip (429kB, staženo 2x)
citovat

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura III

Oblíbený formulář Faktura byl vylepšen a rozšířen. Formulář faktura III
Více se dočtete zde.

Aktivní diskuse

ZOSKUPENIE ÚDAJOV

Daniel_11 • 29.10. 10:10

kopie mezi instancemi Excelu

rinma • 29.10. 9:44

kopie mezi instancemi Excelu

Stalker • 29.10. 8:44

kopie mezi instancemi Excelu

rinma • 28.10. 19:01

kopie mezi instancemi Excelu

rinma • 28.10. 18:58

den hodina minuta sekunda v jednej bunke

lugr • 28.10. 12:50

Kombinace url odkazů

Petan111 • 28.10. 11:46