Excel_vyhledávání největší a nejmenší hodnoty

Programy pro práci v kanceláři (Word, Excel, Access…=>Office)

Moderátor: Mods_senior

regpp
nováček
Příspěvky: 8
Registrován: únor 18
Pohlaví: Nespecifikováno
Stav:
Offline

Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod regpp » 21 úno 2018 09:55

Zdravím všechny.

potřeboval bych pomoci, prosím, s mým problémem. Přikládám zde soubor pro lepší pochopení problému.

je zde tabulka se jmény a příjmením. ID je jen spojení jména a příjmení. Každé ID vlastní zvíře. A ke každému zvířeti mám 3 sloupce dat. Data1, Data2, Data3. Potřebují nějakou funkcí (nejlépe), nebo makrem (pokud by to fakt funkcí nešlo - makra neumím, byla by pro mě těžká budoucí úprava) zařídit, aby se mi do tabulek (viz soubor) vypsaly 3 hodnoty které jsou největší ve sloupci Data1, Data2, Data3, ID a zvíře ke kterému tyto data patří a stejně tak 3 nejmenší hodnoty ze sloupce Data1, Data2, Data3, ID a zvíře.

Věřím, že vyrobený vzorový soubor problém dostatečně popíše. Je v něm i řešení vyrobeně manuálně, aby bylo jasné, jak má výstup vypadat. Originální data jsou obrovská tabulka s řádově 35000 řádky a se slopci Data1, Data2, Data3. Potřebuji vždy zjistit, které 3 výrobky (ve vzorových datech zvíře) má největší propad ve výrobě (záporné hodnoty) a zároveň, kterým třem výrobkům se dařilo (kladné hodnoty). ID slouží pouze pro přesnější určení výrobku.

Pokud by byla nějaká nejasnost v zadání, rád vysvětlím lépe.

Díky moc za pomoc.

Robert
Přílohy
vzor_zvířata.xlsx
(13.41 KiB) Staženo 27 x

Reklama
Uživatelský avatar
vonv
Level 1
Level 1
Příspěvky: 93
Registrován: leden 08
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod vonv » 21 úno 2018 13:50

v podstatě můžeš pro každou tvoji tabulku pro Data 1,Data 2 a Data 3 (max a min) použít dotaz, ve kterém si nastavíš filtrování (>=0 nebo <0) a seřazení podle velikosti. pak si jen dotazy obnovíš a aktualizují se ti data.

v menu je to DATA - Z tabulky nebo z oblasti.
diky všem za rady

regpp
nováček
Příspěvky: 8
Registrován: únor 18
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod regpp » 21 úno 2018 13:59

Mohl by jsi, prosím, tento nápad udělat v vzorovém souboru a nahrát tady? Podíval bych se na to. Nevím, jak se nastavují dotazy.

Díky moc

Uživatelský avatar
vonv
Level 1
Level 1
Příspěvky: 93
Registrován: leden 08
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod vonv » 21 úno 2018 14:03

no ono ve vzorovem souboru toho moc neuvidis > vyhodí ti to tabulku podle tvých potřeb
zkusím ti udělat příklad s videem s postupem pro jednu tabulku a hodím to sem

Dodatečně přidáno po 26 minutách 38 vteřinách:
přikládám video. pro každou tabulku si pak jen upravíš, které sloupce tam chceš (data1, data2 atd.) a upravíš si jejich filtrování a případně řazení a také jejich umístění na nový list nebo do stávajícího listu. Je tam vidět i aktualizace dat, pokud se hodnoty změní.

Video mi sem nejde vložit, tak si ho si stáhni zde (47 MB a cca za 24 hod. ho smažu):
https://www.dropbox.com/s/gzffq64sf0tu2 ... x.avi?dl=0

Dodatečně přidáno po 1 hodině 32 minutách 26 vteřinách:
tady ještě vzorový soubor.
Přílohy
zvířata.xlsx
(36.53 KiB) Staženo 26 x
diky všem za rady

regpp
nováček
Příspěvky: 8
Registrován: únor 18
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod regpp » 22 úno 2018 07:57

Ahoj,

díky moc za krásně vysvětlené řešení! :). Vyzkouším to. Akorád to není přesně to, co jsem čekal.

Představ si toto. Každý měsíc dostanu nový sloupec dat (Data1 -> leden, Data2-> unor). Mám soubor (veliký - 50Mb+) ve kterém mám všechna data vložena. Jeden měsíc má sloupec Data třeba 35k buňek, druhý měsíc má sloupec data2 třeba 41k buňek. Nemohu sloupce v originálním souboru seřadit, protože se mi vždy seřadí jen jeden sloupec (ostatní logicky nemohou, protože by nesouhlasilo ID). Tudíž potřebuji přidaný sloupec nějakou funkcí projet, a najít 10 (100, 200...) největších hodnot a 10 (100, 200..) nejmenších hodnot. Tyto hodnoty si potom zapisuji do jiného souboru (toto již umím). Tebou popsaný postup by znamenal každý měsic vytvářet tabulku, mazat nepotřebné sloupce (je jich v originálním souboru mraky) a poté vytvořit malé tabulky, které se mi zobrazí v druhém souboru. Neexistuje jenom prostě nějaká funkce, která proběhne sloupec dat a nalezne v něm a seřadí 10 největších hodnot a naopak 10 nejnižsích hodnot, které mi poté kdekoliv vypíše?

Díky moc :).

Tebou popsané řešení vyzkouším také na originálních datech a uvidíme. Nevýhoda je, že řešení by mělo být co nejvíce "user friendly"... Protože to nebudu zkoumat ani tak já, jako spíše můj vedoucí. A ten by rád viděl sloupec Leden a pod ním 10 výrobků ze ztrátou a naopak s nárustem. Musel bych mu tedy každý měsíc podle tvého postupu všechno nachystat. Kdyby existovala funkce, tak by se jen aktualizovala tabulka a funkce by mi potřebné informace našla.

Robert

Uživatelský avatar
vonv
Level 1
Level 1
Příspěvky: 93
Registrován: leden 08
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod vonv » 22 úno 2018 09:55

pochopil jsem dobře, že každý měsíc dostaneš nová data a pro každý měsíc děláš toto vyhodnocení?

pokud ano, dotazy, jak jsem popsal, to řeší celkem dobře.
mimochodem, zkus si dopsat data - další řádky do zdrojové tabulky a aktualizovat dotaz - uvidíš, že i nově dopsané řádky se vyhodnocují.

Pokud by to bylo jinak, zkopírovat/upravit dotazy každý měsíc pro nová data zabere řádově minuty. navíc není problém napsat makro s formulářem, který ty dotazy upraví pro nová data, kdyby obsluha měla problém s jejich úpravou.

můžeš zkusit funkci LARGE / SMALL nebo RANK, ale to jsem nezkoumal, jak pak najít ID a zvíře, plus je tam komplikace v případě opakujících se hodnot.
diky všem za rady

regpp
nováček
Příspěvky: 8
Registrován: únor 18
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod regpp » 22 úno 2018 11:07

Ahoj,

za každý měsíc přibyde nový sloupec. Nyní jsou ve vzoru 3 sloupce - Data1, Data2, Data3. Další měsíc bude sloupec Data4, Další Data5. Takže se nebudou přidávat hodnoty k již existujícím sloupcům, ale budou se vytvářet nové (resp jen naplňovat. Mám sloupce připravené a každý měsíc do nich pomocí makra narhaji data.) Zkoušel jsem to a časově to náročné není. Ale to už bych mohl rovnou vzít naimportované data, seřadit a zkopírovat prvních 20 hodnot a posledních 20 hodnot. Cílem bylo, aby se po naimportování dat do sloupců, automaticky vyplnily tabulky (jak mám ve vzorovém příkladu 3 prázdné tabulky, tak aby se do buňek buď vepsala funkce, nebo pomocí makra se hodnodty do tabulek nahrály).

Funkci Large, short jsem zkoušel asi hodinu zpět. Perfektně mi vyselektuje prvních 20 hodnot (mám v každé buňce příkaz a parametr volím 1, 2,3... prostě kolik hodnot chci). Ale přesně jak píšete, nedokázal jsem spolehlivně přiřadit výrobek a ID. nebyl by nápad jak toto vyřešit? Jak naplnit sloupec 10 daty kladnými a 10 daty zápornými vím. Ale jak k těmto hodnotám přiřadit správné ID a výrobek již ne. Hodnoty se pravděpodobně opakovat můžou. Je to 35000 hodnot. Zpravidla kolem 0 (0,001, 0,0214, -0,0004) apod... takže spoléhat se na to, toto číslo je unikátní a tudíž se podle něj řídit jako podle kliče nemůžeme.

Díky,

Robert

Uživatelský avatar
vonv
Level 1
Level 1
Příspěvky: 93
Registrován: leden 08
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod vonv » 22 úno 2018 15:06

takto to ale v původním zadání nebylo :-)
funkce mě nenapadá. dá se to řešit dotazy, jak jsem nastínil nebo krkolomně i kontingenční tabulkou. a samozřejmě makrem.
editace dotazu pro libovolný sloupec je rychlá a není složitá - na to že se to bude dělat jednou měsíčně to za to stojí i někoho naučit.
diky všem za rady

Uživatelský avatar
elninoslov
Level 2.5
Level 2.5
Příspěvky: 366
Registrován: červen 13
Pohlaví: Muž
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod elninoslov » 22 úno 2018 23:58

Riešiteľné to maticovým vzorcom je, ale o nasadení na 40K riadkov x veľa stĺpcov x 200 nadsadených riadkov do každej tabuľky, Excel bude kolabovať. Keď tak, tak si skúste na jednom 40K stĺpci a jednej výsledkovej tabuľke, ako to bude s rýchlosťou. Prípadne, ak vravíte, že importujete makrom, tak si to celé ošefujte rovno pri tom importovacom makre. Napríklad vložte tieto vzorce a prepíšte hodnotu hodnotou, aby viac k výpočtu nedochádzalo.
Přílohy
vzor_zvířata.xlsx
(16.12 KiB) Staženo 21 x

regpp
nováček
Příspěvky: 8
Registrován: únor 18
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod regpp » 23 úno 2018 08:17

Zdravím všechny,

díky moc za reakce.

elninoslov: Díky, vyzkouším vzore aplikovat na mé data. Co se týče úpravy maker, tak makro vyrobil nějaký externista který už tu není a osttaní makra neumí... resp, upravím si třeba cestu, nebo nějakou maličkost. Ale dopsat nějakoou takovou funkci jakou potřebuju neumím.

Myslel jsem, že bude existovat nějaka triviální funkce, která by udělala přesne to co potřebuji. Zdá se, že neexistuje. bude tím pádem pravděpodobně nejjednodušší, vložit top 20 a wors 20 dat ručně... Ještě vyzkouším funkci od elninoslov na velkém množstí dat a uvidím.

Kdyby někoho napadl ještě nejaký jiný způsob (věděl by třeba o funkci, která by popisovaný problém řešila) bylo by to super.

Díky.

Robert

Uživatelský avatar
elninoslov
Level 2.5
Level 2.5
Příspěvky: 366
Registrován: červen 13
Pohlaví: Muž
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod elninoslov » 24 úno 2018 20:11

Tak zmente citlivé údaje, a vložte sem ten súbor s makrom s reálnym počtom, formátom a rozmiestnením dát. Priložte aj príkladný zdroj (súbor, stránku,...) odkiaľ importujete data. A možno sa prijateľného riešenia dočkáte.

A navyše ste myslím stále neupresnil, čo treba vlastne robiť s tými duplami, ak máme napr. čísla 1, 2, 3, 4, 3, 3, 5, a zadáme 4 najmenšie, tak výsledok bude:
a) 1, 2, 3, 3
b) 1, 2, 3, 3, 3, 4

regpp
nováček
Příspěvky: 8
Registrován: únor 18
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Excel_vyhledávání největší a nejmenší hodnoty

Příspěvekod regpp » 26 úno 2018 07:49

Zdravim elninoslov.

Díky za reakci. Zdrojové data jsou obrovské... Excelovský soubor má (jen ten kde již jsou data naimportovaná) 120Mb. Zdrojové soubory jsou kolem 220mb+... Navíc změnit data za fiktivní by zabralo 10x déle, než po celou dobu existenci firmy kopírovat top 10 dat ručně...

Pokud existuje nějaká funkce (formula, příkaz), která dokáže ze sloupce dat najít 10 největších a 10 nejnižsích, tak na to není potřeba dávat pravé data. Pokud taková funkce neexistuje a bylo by třeba psát složité makro (pro mě do budoucna absolutbně needitovatelné), bude lepší prostě top 10 zkopírovat ručně...

Co se týče duplicit, potřebuju jenom vypsat 10 nejvyšších. Jakmile tam bude více stejných hodnot, klidně at se vypíše jedna z nich... to je jedno. Výstup bude, že těchto 10 výsledků je špatných. Pokud mě budou zajímat všechny (nebo 100, 200) podívám se už do zdrojových dat. Výstup z tvého příkladu : 1, 2, 3, 4, 3, 3, 5, by byl 1,2,3,3 (v případě 4 nejmenších hodnot). V případě pěti hodnot: 1, 2, 3, 3, 3 ...

výše jsem již psal, že pomocí funkce large a small jde 10 hodnot v pohodě najít. Ale problém je již s přiřazením typu výrobku...

Nechce se mi věřit, že neexistuje funkce, pro najití 10 nejvyšších a 10 nejnižších hodnot ve sloupci... :X na netu jsem samozřejmě hledal, ale (možná špatně) nic nenašel, kdyby to bylo něco jako =TOP(A:A, 10) zadám oblast (celý sloupec A a 10 hodnot) =WORST(A:A, 10) a to samé s nejnižsíma. z vaší zkušenosti, neznáte nějakóu podobnou funkci?

Díky.

Robert


  • Mohlo by vás zajímat
    Odpovědi
    Zobrazení
    Poslední příspěvek
  • Google vyhledávání-reCaptcha Příloha(y)
    od ski1961 » 23 kvě 2023 16:58 » v Internet a internetové prohlížeče
    4
    1905
    od kecalek Zobrazit poslední příspěvek
    24 kvě 2023 14:59
  • Vyhledávání z adresní řádky - chyba (Chrome) Příloha(y)
    od pikaso.andreas » 23 říj 2023 14:34 » v Internet a internetové prohlížeče
    1
    2275
    od rhsCZ Zobrazit poslední příspěvek
    25 říj 2023 19:55
  • Nastavení měřítka obrazovky na jiné, než předdefinované hodnoty Příloha(y)
    od Grander » 29 čer 2023 17:34 » v Windows 11, 10, 8...
    0
    1050
    od Grander Zobrazit poslední příspěvek
    29 čer 2023 17:34
  • Excel a OneDrive
    od sginfo » 11 zář 2023 15:28 » v Kancelářské balíky
    16
    5984
    od mirekol Zobrazit poslední příspěvek
    20 říj 2023 08:31
  • excel-posun makra
    od actionboy » 12 bře 2024 18:59 » v Kancelářské balíky
    1
    260
    od Grimm Zobrazit poslední příspěvek
    12 bře 2024 21:43

Zpět na “Kancelářské balíky”

Kdo je online

Uživatelé prohlížející si toto fórum: Žádní registrovaní uživatelé a 6 hostů