Fórum pro uživatele kancelářského balíku OpenOffice | LibreOffice
 

#1 5. 2. 2022 15:10:24

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

Dobrý den,
obracím se na zkušenější uživatele se žádostí o pomoc.
Mám 2 listy (1. seznam materiálu a množství; 2. ceník dodavatele).

Do 1. listu potřebuji dohledat z ceníku dodavatele množství a cenu).
K dohledání cen jsem využíval funkci VLOOKUP($A2&$B2;...). Množstevní limity dodavatele jsem si pamatoval a požadované množství podle toho upravil.   

Bohužel, dodavatel množstevní limity změnil a nevím, jak dohledat, jaké je minimální množství a na jaké větší množství je již sleva.
Např. potřebuji objednat na různé termíny: 5, 21, 150 a 1000 ks.
V ceníku dodavatele jsou limity: 10, 100 a 500 ks.
Děkuji.

Editoval Eddie60 (3. 4. 2022 15:16:46)

Offline

#2 7. 2. 2022 10:12:58

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

Dobrý den. Záleží na tom zda chcete množstevní limity jako informaci, nebo automatickou změnu zadaného množství na nějaký nejbližší limit. Případně ještě je možnost omezení zadání množství pouze ve výši limitů. Také záleží na tom jak jsou v ceníku dodavatele limity zadané - v jedné buňce, ve více buňkách, v řádku, ve sloupci ...

Jako informaci si množstevní limity můžete vyhledat do nějaké vedlejší buňky pomocí vlookup ... (myslím že "optické" vyhodnocení bude tady nejefektivnější), limity vyhledáte stejně jako tu cenu.

Nejbližší vyšší limit nalezenete pomocí funkcí INDEX(oblast;1;MATCH(kriterium;oblast;1)), podmínkou je uvedení jednotlivých limitů do jednotlivých buněk a seřazení od nejmenšího k největšímu (v tomto případě v řádku)
Nejbližší nižší limit pak INDEX(oblast;1;MATCH(kriterium;oblast;1)-1)

Omezení zadání pouze limitů můžete provést pomocí data - platnost, ale to asi nebude moc praktické.

Editoval ludviktrnka (7. 2. 2022 10:15:57)


LibreOffice 5.4.

Offline

#3 7. 2. 2022 20:57:47

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

Dobrý den,
děkuji za odpověď. Dané řešení jsem si vyzkoušel a pro jeden materiál funguje s tím, že dohledávám pořadí (číslo) řádku v oblast1 místo sloupce:
=INDEX(oblast1;MATCH(Množství;oblast2;1);2)
oblast1 = ceník dodavatele
oblast2 = část sloupce pro danou položku (např. mater_1) s množstvím v ceníku dodavatele (2. sloupec)

Seznam materiálu s množstvím
Materiál     Množství (ks)
mater_1        1
mater_1        10
mater_1        25
mater_1        150
mater_2        26   

V ceníku dodavatele jsou ceny dle množství:
mater_1        10    16,00
mater_1        30    15,00
mater_1        100    14,00
mater_1        1000     8,00
mater_2        50    335,00
mater_2        500    95,00
atd.

Data jsou ve sloupcích: materiál - množství - cena a jsou řazeny vzestupně dle 1. materiál 2. množství
Počet řádků pro jednotlivý materiál se liší = oblast2 je proměnlivá (1-10 řádků).

Druhů materiálu máme několik tisíc a v ceníku dodavatele je cca 15 tisíc řádků.
Lze definovat oblast2 pomocí funkce?

Editoval Eddie60 (7. 2. 2022 20:59:01)

Offline

#4 7. 2. 2022 22:42:12

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

Zkuste si prohlídnout tento soubor: https://drive.google.com/open?id=10jc2h … p=drive_fs


Není to úplně elegantní. Ve sloupci "K" je matice (ukládá se trojhmatem CTRL+SHIFT+ENTR) a ve skutečnosti bude mít 15tisíc řádků přičemž ty údaje o limitech budou na stjném místě jako v ceníku tedy např. řádky 11356 až 11360 :-). Ve sloupci C jsou pak tyto údeje zkonsolidovány pomocí platnosti dat, kdy tato funkce umí seřadit hodnoty. To by šlo také zobrazovat pomocí funkce SMALL, záleží na uspořádání. Dojít na cenu za kus je pak řešeno poměrně složitě pomocí proměnných adres a funkce INDIRECT. Určitě by se to dalo ale více zkomprimovat aby nebylo tolik pomocných sloupců.

EDIT: Ve sloupci "F" je zjišťován poslední řádek daného materiálu také pomocí matice, musí se ukládat trojhmatem a potom rozkopírovat (né táhnout, tažením se roztahuje matice, což je v tomto případě nežádoucí)

Editoval ludviktrnka (7. 2. 2022 22:47:03)


LibreOffice 5.4.

Offline

#5 8. 2. 2022 18:33:10

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

Dobrý den, řešení vypadá zajímavě.
Počet sloupců mi nevadí, buď se dají skrýt nebo přesunout na konec.
Soubor jsem si uložil a pokusil se vzorce aplikovat na část orig. dat.
Začínám se učit "abecedu".
Vzorec na dohledání 1. řádku s položkou (sloupec E) funguje bez problémů. Ve sloupci F ("posledni_radek") mi ve vzorci buď zmizí složené závorky a pak se vždy zobrazí číslo posledního řádku ceníku na všech řádcích nebo se ukazuje vzorec jako text. Zatím jsem nepřišel na příčinu.
Musím nastudovat adresování, konsolidaci dat,...
Můžete, prosím, upřesnit pořadí zpracování tabulky? Stačí mi pořadí sloupců.
Děkuji.

Offline

#6 8. 2. 2022 18:52:35

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

add sloupec F: jde o maticový vzorec (jinak bohužel poslední výskyt nelze zjistit). Napíšete základní vzorec tzn.: =MAX(IF($N$1:$N$7=A2;ROW($N$1:$N$7);0)) (resp. vlastní adresy) a poté stisknete CTRL+SHIFT+ENTER tím vzorci řeknete že je maticový, což se vyznačí tak, že se celý vzorec uzavře do složených závorek (ty závorky nepíšete, doplní se samy). V tomto případě je výsledkem jediná hodnota, tedy ne matice jako třeba ve sloupci K. Do dalších buněk vzorec dostanete tak že jej uložíte do ctrl+c a dále vložíte ctrl+V. Nesmí se táhnout "za křížek".
Na pořadí sloupců nezáleží, ale jako první se zjišťuje první a poslední výskyt daného materiálu, tedy číslo řádku (sloupce E a F). Pak se vytvoří text adresy oblasti (sloupce G a H). Pak se v dané oblasti nalezne množství nejbližší nižší než zadané množství (sloupec I, fce MATCH)(tzn. např pro 29 najde 10, pro 32 najde 30, pro 9 nenalezne nic, proto se uvažuje první hodnota (funkce IFNA(...;1). Následně se z vedlejšího sloupce pomocí INDEX odečte cena (sloupec D)

Sloupc K je jen pomocný, výsledkem této matice je pouze seznam limitů pro materiál zadaný v buňce C1

Sloupce C je pouze informativní, pod tlačítkem zobrazuje pouze limity vygenerované ve sloupci K, tedy vztažené k materiálu v C1. Pokud by položek ve sloupci A bylo nějaké rozumné množství (třeba do 50), tak by asi šlo nachystat 50 matic (sloupec K) asi někde na pomocném listu a pak by mohla být nabídka limitů na každém řádku pro každý zadaný materiál.

Editoval ludviktrnka (8. 2. 2022 22:34:09)


LibreOffice 5.4.

Offline

#7 9. 2. 2022 00:01:59

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

Díky Vám jsem si vytvořil stejnou tabulku a postupně zkopíroval vzorce dle doporučení.
Vše funguje jako ta Vaše.
Nerozumím zcela způsobu adresovaní oblasti (sloupce G a H): "R"&E2&"C15:R"&F2&"C15"
V uvozovkách je přece text.řetězec do kterého vkládám výsledek (čísla) v buňkách E2 a F2.
Předpokládal jsem, že je mohu měnit. Pak to ale nefunguje.
Cena za kus (sloupec D) funguje, pokud je ceník ve stejném listu.
=INDEX(INDIRECT(H2;0);I2;1)
Pokud jsem ceník přesunul do jiného listu a upravil sloupce E a F, pak řádek_ceny_dle_množství (sloupec I) je vždy 1 a cena se nedohledá.
Lze k nalezené ceně z ceníku jednoduše doplnit i množství, ke kterému se cena váže?
VLOOKUP mi nefunguje, protože ceny jsou seřazeny sestupně.
Množství v ceníku je minimální množství, pro které cena platí. Nemohu koupit menší množství.
Děkuji za Vaši ochotu a trpělivost.

Offline

#8 9. 2. 2022 08:46:08

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

pro případ dalšího listu je potřeba vzorce tvořící adresy upravit např. takto: ="cenik!R"&E2&"C2:R"&F2&"C2" (název listu ukončený vykřičníkem; písmeno R značící řádek (row); číslo řádku (odkazem na buňku E2);písmeno C zančící sloupec (column); číslo sloupce - na externím listu to bude asi 2 a v dalším vzorci 3). Ve sloupci H tedy ="cenik!R"&E2&"C3:R"&F2&"C3". Tohle je jiný způsob zadání adresy. Např. buňka D8 na listu "list2" bude touto formou zapsána list2!R8C4.


Vlastně by to šlo i klasicky. (Abych vám trochu zamotal hlavu :-) pak bude vzorec vypadat takto: ="cenik!B"&E2&":B"&F2 a takto: ="cenik!C"&E2&":C"&F2 (množství je ve sloupci B, cena je ve sloupci C). Ve funkci INDIRECT(...;1) je pak potřeba mít druhý parametr nastaven na "1". Asi to bude takto pro vás jednoduší. Systém zápisu typu "R1C1" tady nemá vlastně úplně opodstatnění, protože sloupce známe předem (bral jsem to řešení ze nějakého svého souboru, kde byly i sloupce proměnlivé, takže to bylo potřeba, u vás to bylo sice fukční ale zbytečné)


Pokud budete chtít doplnit "nalezené množství, které je pro zadané množství určující" pak stačí ve sloupci H upravit adresu na celou "oblast" tedy cenik!BxCy (x a y jsou nalezené řádky) a pomocí funkce INDEX si na příslušném řádku zobrazíte jak ono množství INDEX(oblast;řádek;1) a dále cenu INDEX(oblast;řádek;2).

Editoval ludviktrnka (9. 2. 2022 09:14:13)


LibreOffice 5.4.

Offline

#9 9. 2. 2022 09:09:12

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

a zde je tedy upravená verze. Ceník v samostatném listu. Vzorce upraveny do klasické podoby typ "A1". Na listu "pom" matice limitů pro každý jednotivý vyplněný materiál a na řádcích k zadanému materiálu pak pomocí platnosti dat výběr limitů (pouze informační). Přidán sloupec se zobrazením nalezeného limitu.


https://drive.google.com/open?id=10k9fq … p=drive_fs


LibreOffice 5.4.

Offline

#10 9. 2. 2022 21:45:32

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 38

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

Tak to je nyní perfektní. Velmi mi to pomůže. Nyní jsem schopen dohledat vyšší/nižší množstevní limit.
Musím si jen dávat pozor na zdroje dat (xls, xlsx v různých verzích a kódování). Pak vzorce fungují bez problémů.
Na závěr bych Vám rád poděkoval za pomoc a trpělivost. Toto fórum znám více jak 10 let a vždy jsem řešení daného problému našel, někdy sice jen částečně, ale to stačilo. Nyní jsem byl v koncích.
Toto téma lze uzavřít.

Offline

#11 10. 2. 2022 16:47:34

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

Zeditujte svůj úvodní dotaz - do názvu připište "VYŘEŠENO".


LibreOffice 5.4.

Offline

#12 11. 2. 2022 13:23:56

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Jak dohledat menší/větší množství než je uvedeno v buňce? - VYŘEŠENO

omyl - měl jsem nějaký nápad, ale je to blbost

Editoval ludviktrnka (11. 2. 2022 13:26:51)


LibreOffice 5.4.

Offline

Zápatí