< návrat zpět

MS Excel


Téma: Pro fajnšmekry, urgent help, dynamický rozsah rss

Zaslal/a 4.8.2023 20:01

ahojte,

mám na vás prosbičku, takovou malou jobovku. Potřebuju najít minimální a maximální hodnotu v dynamické oblasti a vrátit číslo logeru z oblasti vyhodnocení!M3:ASS, které odpovídá minimální hodnotě / maximální hodnotě. Např. najde minimální hodnotu v dynamické oblasti 42,7 na logerech 52, 63 a maximální hodnotu v dynamické oblasti 53,6 na logeru 67, pak zápis do buňky úvod!J2 by byl "52,63 / 67".

Jak je definovaná dynamická oblast? vždycky hledáme min max hodnotu v oblasti vyhodnocení!M4:AS1000. Tzn. od řádku 4, ale může to být i jiný řádek, to je dáno buňkou úvod!B2. Tuto hodnotu najdeme v oblasti vyhodnocení!A6:A1000 a na řádku, kde tuto hodnotu najdeme, tak hledám v oblasti M:AS100 min/max. Např. když hodnotu úvod!B2 najdu na řádku 35, tak pak hledám min/max v oblasti M35:AS1000.

V buňce úvod!I2 jsem použil jeden vzorec na nalezení min/max oblasti v dynamické oblasti, ale už nedokážu v buňce úvod!J2 k tomu přiřadit čísla logerů.

Dokáže někdo pomoct?

Dalibor

Příloha: xlsx55281_help.xlsx (500kB, staženo 4x)
Zaslat odpověď >

#055282
elninoslov
Nemám Excel s funkciou UNIQUE, a ani ju v tomto prípade netreba. Za predpokladu, že sú dáta v A:A zoradené, by som rozsah oblasti dát určil pomocou MIN, MAX a OFFSET. Potom už iba maticový COUNTIF a TEXTJOIN.

A ešte vysvetlite ten "Čas stabilizace" a "Nejistota měření teplota"

Skontrolujte popis a prílohu. Vami udávané príklady (42,7) sa tam nenachádzajú.

Oblasť dát "DATA":
=OFFSET(Vyhodnocení!$M$1:$AS$1;MATCH(Úvod!$B$2;Vyhodnocení!$A:$A;0)-1;;MATCH(MAX(Vyhodnocení!$A:$A);Vyhodnocení!$A:$A;0)-MATCH(Úvod!$B$2;Vyhodnocení!$A:$A;0)+1)
=POSUN(Vyhodnocení!$M$1:$AS$1;POZVYHLEDAT(Úvod!$B$2;Vyhodnocení!$A:$A;0)-1;;POZVYHLEDAT(MAX(Vyhodnocení!$A:$A);Vyhodnocení!$A:$A;0)-POZVYHLEDAT(Úvod!$B$2;Vyhodnocení!$A:$A;0)+1)

MIN / MAX:
=MIN(DATA)&" / "&MAX(DATA)
SONDA:
=TEXTJOIN(",";TRUE;IF(COUNTIF(OFFSET(DATA;;COLUMN(Vyhodnocení!$M$1:$AS$1)-13;;1);MIN(DATA))>0;Vyhodnocení!$M$3:$AS$3;""))&" / "&TEXTJOIN(",";TRUE;IF(COUNTIF(OFFSET(DATA;;COLUMN(Vyhodnocení!$M$1:$AS$1)-13;;1);MAX(DATA))>0;Vyhodnocení!$M$3:$AS$3;""))
=TEXTJOIN(",";PRAVDA;KDYŽ(COUNTIF(POSUN(DATA;;SLOUPEC(Vyhodnocení!$M$1:$AS$1)-13;;1);MIN(DATA))>0;Vyhodnocení!$M$3:$AS$3;""))&" / "&TEXTJOIN(",";PRAVDA;KDYŽ(COUNTIF(POSUN(DATA;;SLOUPEC(Vyhodnocení!$M$1:$AS$1)-13;;1);MAX(DATA))>0;Vyhodnocení!$M$3:$AS$3;""))
Příloha: xlsx55282_55281_help.xlsx (500kB, staženo 1x)
citovat
#055283
avatar
ahoj, na tebe je vždycky spolehnutí 10 , děkuji. příklady 42,7 byly jen příklady, asi v tabulce ani nejsou.
Za normálních okolností se ke zjištěnému min/max odečítá/přičítá nejistota měření, takže když najde min 49,7, tak od této hodnoty ještě odečte nastavenou nejistotu měření a zobrazí v mém případě v buňce úvod!I2 před lomítkem. V tabulce na listu vyhodnocení samozřejmě hledáme původní minimální hodnotu.

čas stabilizace je ten časový posun. Pokud bude čas stabilizace třeba 15 minut, tak k času, co je v buňce vyhodnocení!A6 (pozor začínám od řádku 6, řádek 4 a 5 mě v tomto výpočtu nezajímají) musíš přičíst 15 minut, najít jeho pozici (nebo nejližší) v oblasti vyhodnocení!A6:A1000 a od tohoto řádku směrem dolů hledat min a max hodnotu v oblasti M?:AS1000 , kde otazník je nově nalezený dynamický řádek na základě časového posunu.

Zkouším tvůj příklad a nějak se mi zdá, že maximální hodnota je pořád stejná bez ohledu na časový posun (55,4)

elninoslov napsal/a:

Nemám Excel s funkciou UNIQUE, a ani ju v tomto prípade netreba. Za predpokladu, že sú dáta v A:A zoradené, by som rozsah oblasti dát určil pomocou MIN, MAX a OFFSET. Potom už iba maticový COUNTIF a TEXTJOIN.

A ešte vysvetlite ten "Čas stabilizace" a "Nejistota měření teplota"

Skontrolujte popis a prílohu. Vami udávané príklady (42,7) sa tam nenachádzajú.

Oblasť dát "DATA":
=OFFSET(Vyhodnocení!$M$1:$AS$1;MATCH(Úvod!$B$2;Vyhodnocení!$A:$A;0)-1;;MATCH(MAX(Vyhodnocení!$A:$A);Vyhodnocení!$A:$A;0)-MATCH(Úvod!$B$2;Vyhodnocení!$A:$A;0)+1)
=POSUN(Vyhodnocení!$M$1:$AS$1;POZVYHLEDAT(Úvod!$B$2;Vyhodnocení!$A:$A;0)-1;;POZVYHLEDAT(MAX(Vyhodnocení!$A:$A);Vyhodnocení!$A:$A;0)-POZVYHLEDAT(Úvod!$B$2;Vyhodnocení!$A:$A;0)+1)
MIN / MAX:
=MIN(DATA)&" / "&MAX(DATA)
SONDA:
=TEXTJOIN(",";TRUE;IF(COUNTIF(OFFSET(DATA;;COLUMN(Vyhodnocení!$M$1:$AS$1)-13;;1);MIN(DATA))>0;Vyhodnocení!$M$3:$AS$3;""))&" / "&TEXTJOIN(",";TRUE;IF(COUNTIF(OFFSET(DATA;;COLUMN(Vyhodnocení!$M$1:$AS$1)-13;;1);MAX(DATA))>0;Vyhodnocení!$M$3:$AS$3;""))
=TEXTJOIN(",";PRAVDA;KDYŽ(COUNTIF(POSUN(DATA;;SLOUPEC(Vyhodnocení!$M$1:$AS$1)-13;;1);MIN(DATA))>0;Vyhodnocení!$M$3:$AS$3;""))&" / "&TEXTJOIN(",";PRAVDA;KDYŽ(COUNTIF(POSUN(DATA;;SLOUPEC(Vyhodnocení!$M$1:$AS$1)-13;;1);MAX(DATA))>0;Vyhodnocení!$M$3:$AS$3;""))Příloha: 55282_55281_help.xlsx (500kB, staženo 1x)
citovat
#055284
elninoslov
Po započítaní stabilizácie 0:30 a začiatku v A6 je oblasť DATA M35:AS186 a MAX je 51,6. A tá nastala na riadkoch 91-130 sond 51 a 53. Teda všetky posuvy stabilizácie, ktoré nastavia začiatok oblasti DATA na riadky od 35-130, budú mať rovnaké MAX. Až keď nastavíme stabilizáciu na 2:06 (alebo nastavíme začiatok na A101 a stabilizáciu na 0:30) sa dostaneme na iné MAX a to 51,5.

Ale inak je tam dosť skreslení. Tie dáta ťaháte pomocou vzorcov z listov sond, ale nie presne v daný čas a približne -> menší nájdený. Nie každá sonda loguje v rovnaký čas. Potom to skresľujete pripočítaním stabilizácie, kde sa hľadá tiež približný menší čas (neviem odkiaľ sú tie časy v Vyhodnocení!A:A, každopádne logicky nekorešpondujú s časmi a hodnotami v listoch sond). Potom sa to ešte znepresňuje pomocou "nejistotaT". Takže MIN a MAX je deformované. A navyše pri výpise názvov sond sa nezohľadňuje deformované MIN MAX. A logicky ani nemôže, lebo by mohla nastať situácia, keď by sa deformovaný (dopočítaný) MIN/MAX reálne v sondách nenachádzal, a zoznam sond by bol prázdny. Teda zoznam sond nezodpovedá MIN/MAX.
Ale možno nad tým len veľa uvažujem, takto na noc ... 5

Konkrétny príklad:
Začíname na A101, stab. 0:30, neistota 0,5 : MAX je 52 - v oblasti sa ale vôbec nenachádza.

Ale bez tej korekčnej (no zároveň deformujúcej) stabilizácie a neistoty, to nebude zodpovedať tiež. Viď vyššie, ale aj prípad, keď posunutý čas má oveľa bližšie napr. väčšiemu maximu o riadok/sekundu nižšie, pričom sa bude ale počítať údaj oveľa ďalej od nameranej hodnoty (čas - stabilizácia).

Múdre hlavy, kde ste? Na pive? 9

Idem spať...
Příloha: xlsx55284_55281_help.xlsx (80kB, staženo 4x)
citovat
#055285
avatar
jaaaaj, ty jsi mě pěkně zamotal 10 10 10 10 , nic vlastně není reálné, hahhahha 3 .
Je to tak jak píšeš. Jednotlivé datalogery jsou stažené do listů. Problém je, že každý dataloger je nastartovaný v jiný čas (ano, doporučoval jsem dal odložený start, pak by to mělo stejný čas, ale firma nechce). Navíc čas datalogerů pak nesedí s časem zařízení, vůči kterému se to porovnáná. Takže všechno je to tak nějak v mezi prostoru. Ale vzhledem k tomu, že na to, aby se změnila teplota potřebuješ řádově minuty, tak se to snažím sladit na nějaké sekundy plus mínus. Ano, někdy tam může být případ, že zrovna když se to časově láme tak přeskočí teplota o desetinu, ale to už bych se z toho asi musel zbláznit, vůbec netuším, jak bych mohl toto ukočívat, když máš defacto 30 různých záznamů a "různých" časech. To bych pak musel dávat nějaké korekce času třeba ke každému datalogeru, ale tam je třeba 20tis záznamů/datalgoger a už dnes má ten soubor třeba 100mb, tak pak by to bylo zase třeba od 15mb víc.

Ale i tak díky za pomoc, jdu to vyzkoušet 1 1 1citovat
#055286
elninoslov
Raz som sa pokúšal niečo trošku podobné spáchať známemu do práce na uľahčenie identifikácie, kedy vznikol problém. Tam sa jednalo o 100-200 GB takýchto logov. To bolo pár desiatok senzorov na jednom mieste, tých miest desiatky po celom svete, a vždy asi do 5 druhov úplne iných dát a s iným časovaním. Každú sekundu, každé 3, akokoľvek rýchlo sa len dalo a nepravideľne, každých približne 5 s ale iba v noci, atď. Zariadenia spolupracovali interkontinentálne. On mal spätne aj rok dozadu zisťovať, kedy sa začali kumulovať odchýlky, ktoré nakoniec po roku viedli spolu k problému. Presnosť na 5-6 desatín. To bolo nevyriešiteľné. Nakoniec sme to vzdali.
Ale myslím, že sme vtedy došli k záveru, že sme považovali všetky hodnoty za stálu pokiaľ neprišlo k zmene. Vtedy sme v PowerQuery vypĺňali hodnoty smerom nadol po sekundách predchádzajúcou hodnotou. Takže by to asi mohlo byť, tak ako to máte - nájde prvý menší čas. Aspoň to. Ešte skúsim porozmýšľať, ale nič nesľubujem.citovat
#055287
avatar
ověřuju, že to funguje skvěle a jednoduše a upřímně přiznávám, že se mám ještě hodně co učit, kloubouk dolů mistře 10 10 10citovat
#055288
avatar
Vyplňování dat z listů pomocí vzorců, navíc s nepřímým dotazem spotřebovává hodně prostoru i času při přepočtu.

Použij např. powerquery k načtení dat, tam můžeš navíc zavést časové korekce pro každý loger.

K nalezení dynamické oblasti je zpravidla jednodušší (a snad i přehlednější) použití funkce XLOOKUP, pokud je k dispozici). Podobně, při hledání sloupců/řádků oblasti, které splňují podmínku je užitečná funkce součin.matic:

I2:
=LET(
data;XLOOKUP(B2+B1;Vyhodnocení!A4:A1000;Vyhodnocení!M4:M1000;Vyhodnocení!M4;-1):Vyhodnocení!AS1000;
TEXTJOIN(" / ";NEPRAVDA;MIN(data) - nejistotaT;MAX(data) + nejistotaT))


(XLOOKUP vrací odkaz)

J2:
=LET(
data;XLOOKUP(B2+B1;Vyhodnocení!A4:A1000;Vyhodnocení!M4:M1000;Vyhodnocení!M4;-1):Vyhodnocení!AS1000;
logery;Vyhodnocení!M3:AS3;
Sl;SEQUENCE(1;ŘÁDKY(data);1;0);
minL;TEXTJOIN(",";;FILTER(logery;SOUČIN.MATIC(Sl;--(data=MIN(data)))));
maxL;TEXTJOIN(",";;FILTER(logery;SOUČIN.MATIC(Sl;--(data=MAX(data)))));
TEXTJOIN(" / ";;minL;maxL))
citovat

Uživatelské menu

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

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

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

Aktivní diskuse

rozpis zápasů hráčů pro utkání dvou týmů

Anonym • 25.5. 11:54

Hromadné přepsání

elninoslov • 24.5. 10:29

Hromadné přepsání

Nomi • 24.5. 8:32

Index - každý rok začít od 1 Power Query

Alfan • 23.5. 9:17

nepřímý odkaz

elninoslov • 23.5. 0:00

nepřímý odkaz

bordov • 22.5. 18:35

vrácené hodnoty

Anonym • 22.5. 15:46