MS Excel

Ahoj, Dobrý den,

Prosím o pomoc se vzorcem. Potřeboval bych vzorec do sloupce Y. Má to počítat kolik máme pokrytí jednotlivých dílů.

Jednodušší, to bude asi v ukázce v příloze, ale zkusím vysvětlit.

První řádek mám počet pracovních dní v týdnu. Třetí řádek požadavky, pátý kvantita po spotřebě. Představoval bych si to, že pokud ve třetím řádku (Daného materiálu) najde mínus, tak sečte všechny pracovní dny ve dny, kdy bylo pokrytí. Pro týden kde je mínus spočítá průměrné požadavky na jeden den a podělí tím zůstatek z předchozího týdne.

Zkoušel jsem nějak využít POZVYHLEDAT(PRAVDA;D5:W5<0;0) ale nevím jak dál.

Děkuji moc za případnou pomoc

Zaslat odpověď >

Nosal(15.10.2024 14:24)#056888

Vyřešil jsem to pomocí když, ale možná bych byl radši za nějakou jednodušší variantu.

=KDYŽ(X3="W2"; D1+(D5/(E3/E1));
KDYŽ(X3="W3"; D1+E1+(E5/(F3/F1));
KDYŽ(X3="W4"; D1+E1+F1+(F5/(G3/G1));
KDYŽ(X3="W5"; D1+E1+F1+G1+(G5/(H3/H1));
KDYŽ(X3="W6"; D1+E1+F1+G1+H1+(H5/(I3/I1));
KDYŽ(X3="W7"; D1+E1+F1+G1+H1+I1+(I5/(J3/J1));
KDYŽ(X3="W8"; D1+E1+F1+G1+H1+I1+J1+(J5/(K3/K1));
KDYŽ(X3="W9"; D1+E1+F1+G1+H1+I1+J1+K1+(K5/(L3/L1));
KDYŽ(X3="W10"; D1+E1+F1+G1+H1+I1+J1+K1+L1+(L5/(M3/M1));
KDYŽ(X3="W11"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+(M5/(N3/N1));
KDYŽ(X3="W12"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+N1+(N5/(O3/O1));
KDYŽ(X3="W13"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+N1+O1+(O5/(P3/P1));
KDYŽ(X3="W14"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+N1+O1+P1+(P5/(Q3/Q1));
KDYŽ(X3="W15"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+N1+O1+P1+Q1+(Q5/(R3/R1));
KDYŽ(X3="W16"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+N1+O1+P1+Q1+R1+(R5/(S3/S1));
KDYŽ(X3="W17"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+N1+O1+P1+Q1+R1+S1+(S5/(T3/T1));
KDYŽ(X3="W18"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+N1+O1+P1+Q1+R1+S1+T1+(T5/(U3/U1));
KDYŽ(X3="W19"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+N1+O1+P1+Q1+R1+S1+T1+U1+(U5/(V3/V1));
KDYŽ(X3="W20"; D1+E1+F1+G1+H1+I1+J1+K1+L1+M1+N1+O1+P1+Q1+R1+S1+T1+U1+V1+(V5/(W3/W1));
"N/A")))))))))))))))))))citovat

elninoslov(16.10.2024 0:18)#056889

Snáď som to pochopil. Ak nemáte funkciu LET, neviem preklad do CZ, dá sa to aj bez nej.
=IFNA(LET(IDX;MATCH(TRUE;D5:W5<0;0);SUM(OFFSET(C1;;;;IDX))+(INDEX(C5:V5;IDX)/(INDEX(D3:W3;IDX)/INDEX(D1:W1;IDX))));"N/A")
=IFNA(LET(IDX;POZVYHLEDAT(PRAVDA;D5:W5<0;0);SUMA(POSUN(C1;;;;IDX))+(INDEX(C5:V5;IDX)/(INDEX(D3:W3;IDX)/INDEX(D1:W1;IDX))));"N/A")citovat

lubo(16.10.2024 10:51)#056890

Vůbec to nechápu, popis a přiložený vzorec moc dohromady nejdou...

Funkce xlookup vrací odkaz...

=LET(
x; XLOOKUP( PRAVDA;
$D$5:$W$5 < 0; $D$1:$W$1;
0; 0);
sum; SUMA($D$1:x; -x);
_x5; POSUN(x; 4; -1);
_y3; POSUN(x; 2; );
IFERROR(sum + _x5 / (_y3 / x); "N/A")
)citovat

Nosal(16.10.2024 13:03)#056891

Díky moc všem.

Vzorec od elninoslav funguje.

Od lubo taky, ale jen pro první řádek, potřebuju vzorec stahovat dolů na další řádky a v těch posunech se ztrácím, takže nevím jak zafixovat.citovat

lubo(16.10.2024 13:26)#056892

Jak jsem už psal, popis požadavku nic moc. Co znamená stahovat na další řádky? Na řádek 4 nebo 5 ????

Obecně:

1. Asi nevíte, jaký je rozdíl mezi absolutním a relativním odkazem. Znak $ v odkazu $D$1 znamená absolutní odkaz na buňku D1, tj. při kopírování vzorce odkazuje stále na stejnou buňku. Pokud to chcete kopírovat jinam, tak odkaz změňte na relativni => znaky $ v odkazu smažte.

2. Ve funkci let můžete záměnou posledního parametru sledovat výpočet.

3. Není dobré používat vzorce, kterým nerozumíte.citovat

Nosal(17.10.2024 6:46)#056893

Vím, že popis je špatný, proto jsem ukázal jak jsem to řešil pomocí IF. Každý materiál má své čtyři řádky, proto vzorec vždycky vložím do čtyř řádků, pak označím čtyři řádky a stáhnu dolů.

Vím jaký je rozdíl mezi absolutním a relativním odkazem. Relativní odkaz na D1 mi nepomůže, tam musí být absolutní, protože první řádek se nikdy měnit nebude, s tím se bude počítat vždy.

Vím, že není dobré používat vzore, kterým nerozumím. Avšak bude to lepší, než to počítat ručně pro 2000 materiálů.

V příloze přikládám soubor, kde je další materiál, jsou tam všechny tři vzorce. A fakt nevím co tam upravit, abych ho mohl používat do dalších buněk.

Příloha: xlsx

56893_wall_cz_help_edit.xlsx (14kB, staženo 3x)

citovat

elninoslov(18.10.2024 0:04)#056896

V lubovom vzorci predsa stačí zmeniť čísla v OFFSET-och (POSUN), lebo je to tam napevno:
OFFSET(x; 4; -1) --> OFFSET(x; ROW(A4); -1)
OFFSET(x; 2; ) --> OFFSET(x; ROW(A2); )
Ale ak nechcete vždy robiť štvoricu vzorcov (4 riadky materiálu), dá sa urobiť taký naťahovací, ktorý bude stačiť ťahať myšou, a on bude reagovať na násobky 4. Potrebujete mať teda rovnaký výsledok vo všetkých 4 riadkoch materiálu? Ak nie, v ktorom zo 4?citovat

lubo(18.10.2024 17:51)#056899

Ok, už je to jasnější.

dvě verze, v obou se musí nastvit informace o poloze.
1) Musí se ručně nastavit, kde je odpovídající pole
Requirements,
v rámci skupiny lze kopírovat
=LET(
_Requir; $B$7;
_zaklRadek; ŘÁDEK(_Requir) - 1;
_WD; $D$1:$W$1;
_WN; SLOUPCE(_WD);
x; XLOOKUP(
PRAVDA;
POSUN(_Requir; 2; 2; 1; _WN) < 0;
_WD;
0;
0
);
sum; SUMA($D$1:x; -x);
_x5; POSUN(x; _zaklRadek + 2; -1);
_y3; POSUN(x; _zaklRadek; );
IFERROR(sum + _x5 / (_y3 / x); "N/A")
)

2) stačí ukázat aktuální řádek, pak je možné kopírovat přes všechny skupiny (pokud se zachová struktura)

=LET(
pozice; A5;
aktKey; POSUN(
pozice;
0;
2 - SLOUPEC(pozice)
);
Key; POSUN(
aktKey;
-MIN(3; ŘÁDEK(aktKey) - 1);
0;
4
);
_Requir; XLOOKUP($B$3; Key; Key);
_zaklRadek; ŘÁDEK(_Requir) - 1;
_WD; $D$1:$W$1;
_WN; SLOUPCE(_WD);
x; XLOOKUP(
PRAVDA;
POSUN(_Requir; 2; 2; 1; _WN) < 0;
_WD;
0;
0
);
sum; SUMA($D$1:x; -x);
_x5; POSUN(x; _zaklRadek + 2; -1);
_y3; POSUN(x; _zaklRadek; );
IFERROR(sum + _x5 / (_y3 / x); "N/A")
)

Verze s liší jen úvodem, ve druhé se hledá první řádek skupiny.

+ pokud přibudou další Wxx, musí se upravit proměnná _WD. (ručně nebo pomocí xlookup...)citovat

Téma: Pomoc se vzorcem

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje