radioblaster
07.02.10,12:56
Zdravim poradaci,
mam 2 problemy v prilozenej tabulke by som potreboval zistit pocet TYPOV v
stlpci TYP a to tak ze hodnoty ktore sa opakuju bude ratat iba raz.
A druhy ktory mam je ten ze potrebujem kazdy tyzden spocitat hodnoty v stlpci QTY za podmienky ze hodnoty v stlpci Ship to IBM (Date) je BLANK a tuto sumu vlozit do prislusneho riadku tabulky v HARKU1
snad som to napisal zrozumitelne.. :confused:
mam 2 problemy v prilozenej tabulke by som potreboval zistit pocet TYPOV v
stlpci TYP a to tak ze hodnoty ktore sa opakuju bude ratat iba raz.
A druhy ktory mam je ten ze potrebujem kazdy tyzden spocitat hodnoty v stlpci QTY za podmienky ze hodnoty v stlpci Ship to IBM (Date) je BLANK a tuto sumu vlozit do prislusneho riadku tabulky v HARKU1
snad som to napisal zrozumitelne.. :confused:
jojom
07.02.10,14:49
Ten druhý problém je takto: {=SUM((F4:F59)*(J4:J59<>""))} uzavrieť ctrl+Shift+Enter (maticový vzorček). čiastočne.
marjankaj
07.02.10,15:09
Ten druhý problém je takto: {=SUM((F4:F59)*(J4:J59<>""))} uzavrieť ctrl+Shift+Enter (maticový vzorček). čiastočne.
Takto je to jednoduchšie =SUMIF(J4:J59;"";F4:F59)
Takto je to jednoduchšie =SUMIF(J4:J59;"";F4:F59)
radioblaster
07.02.10,16:33
no hej, ale ked tento vzorec pouzijem v tabulke do ktorej to vkladam tak tam bude stale to iste.. Myslel som skor nieco take, ze to bude robit same na zaciatku kazdeho tyzdna
marjankaj
07.02.10,17:13
no hej, ale ked tento vzorec pouzijem v tabulke do ktorej to vkladam tak tam bude stale to iste.. Myslel som skor nieco take, ze to bude robit same na zaciatku kazdeho tyzdna
A čo to má vlastne robiť na začiatku týždňa? Keď sa tá tabuľka nezmení, tak sa nebude meniť ani súčet.
A čo to má vlastne robiť na začiatku týždňa? Keď sa tá tabuľka nezmení, tak sa nebude meniť ani súčet.
radioblaster
07.02.10,19:14
maju sa scitat hodnoty v stlpci QTY ked hodnota v stlpci Ship to IBM (Date) bude BLANK.
priklad:
je pondelok PRVY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B2 = 250, prejde 7 dni je pondelok DRUHY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B3 = 200, atd..
WEEK QTY
1........250
2........200
ano, ked sa tabulka nezmeni, nezmeni sa ani vysledok.
najlepsie by asi bolo nejake makro. Vyhodnoti bunky a hodnotu zapise vzdy o riadok nizie ako tyzden predtym, a toto sa bude musiet kazdy tyzden spustit.
priklad:
je pondelok PRVY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B2 = 250, prejde 7 dni je pondelok DRUHY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B3 = 200, atd..
WEEK QTY
1........250
2........200
ano, ked sa tabulka nezmeni, nezmeni sa ani vysledok.
najlepsie by asi bolo nejake makro. Vyhodnoti bunky a hodnotu zapise vzdy o riadok nizie ako tyzden predtym, a toto sa bude musiet kazdy tyzden spustit.
marjankaj
07.02.10,19:29
maju sa scitat hodnoty v stlpci QTY ked hodnota v stlpci Ship to IBM (Date) bude BLANK.
priklad:
je pondelok PRVY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B2 = 250, prejde 7 dni je pondelok DRUHY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B3 = 200, atd..
WEEK QTY
1........250
2........200
ano, ked sa tabulka nezmeni, nezmeni sa ani vysledok.
najlepsie by asi bolo nejake makro. Vyhodnoti bunky a hodnotu zapise vzdy o riadok nizie ako tyzden predtym, a toto sa bude musiet kazdy tyzden spustit.
Takže neexistuje systém ako sa spätne dopátrať k minulotýždňovým výsledkom(prípadne ešte starším)?
To vyzerá ako "nem dobrý" nápad.:confused:
Ísť by to išlo, ale nejako sa mi to nezdá.
priklad:
je pondelok PRVY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B2 = 250, prejde 7 dni je pondelok DRUHY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B3 = 200, atd..
WEEK QTY
1........250
2........200
ano, ked sa tabulka nezmeni, nezmeni sa ani vysledok.
najlepsie by asi bolo nejake makro. Vyhodnoti bunky a hodnotu zapise vzdy o riadok nizie ako tyzden predtym, a toto sa bude musiet kazdy tyzden spustit.
Takže neexistuje systém ako sa spätne dopátrať k minulotýždňovým výsledkom(prípadne ešte starším)?
To vyzerá ako "nem dobrý" nápad.:confused:
Ísť by to išlo, ale nejako sa mi to nezdá.
radioblaster
07.02.10,20:26
Takže neexistuje systém ako sa spätne dopátrať k minulotýždňovým výsledkom(prípadne ešte starším)?
To vyzerá ako "nem dobrý" nápad.:confused:
Ísť by to išlo, ale nejako sa mi to nezdá.
-no neexistuje..
-preco "nem dobrý" nápad? ma to byt nieco ako historicky stav skladu v jednotlivych tyzdnoch
To vyzerá ako "nem dobrý" nápad.:confused:
Ísť by to išlo, ale nejako sa mi to nezdá.
-no neexistuje..
-preco "nem dobrý" nápad? ma to byt nieco ako historicky stav skladu v jednotlivych tyzdnoch
PaloPa
08.02.10,09:51
maju sa scitat hodnoty v stlpci QTY ked hodnota v stlpci Ship to IBM (Date) bude BLANK.
priklad:
je pondelok PRVY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B2 = 250, prejde 7 dni je pondelok DRUHY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B3 = 200, atd..
WEEK QTY
1........250
2........200
ano, ked sa tabulka nezmeni, nezmeni sa ani vysledok.
najlepsie by asi bolo nejake makro. Vyhodnoti bunky a hodnotu zapise vzdy o riadok nizie ako tyzden predtym, a toto sa bude musiet kazdy tyzden spustit.
A) Počet typov (ak sú v rozsahu B7:B62): {=SUM(IF(LEN(B7:B62);1/COUNTIF($B$7:$B$62;$B$7:$B$62)))}
zadané ako maticový vzorec (CTRL+SHIFT+ENTER)
B) Súčty za jednotlivé týždne
Predpoklady:
1) k číslu týždňa pridať i príslušný rok, aby sa dal vygenerovať prvý (resp i posledný) deň príslušného týždňa ako dátum
napr. takto: =DATEVALUE("01-01-"&$A21)+7*(B21-1)-WEEKDAY(DATEVALUE("01-01-"&$A21);2)+1
2) sumár = celkový súčet - súčet za dátumy menšie ak prvý deň príslušného týždňa.
Viď príklad
Palo
priklad:
je pondelok PRVY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B2 = 250, prejde 7 dni je pondelok DRUHY tyzden v roku 2010 a po scitani bude hodnota v bunke Hárok1!B3 = 200, atd..
WEEK QTY
1........250
2........200
ano, ked sa tabulka nezmeni, nezmeni sa ani vysledok.
najlepsie by asi bolo nejake makro. Vyhodnoti bunky a hodnotu zapise vzdy o riadok nizie ako tyzden predtym, a toto sa bude musiet kazdy tyzden spustit.
A) Počet typov (ak sú v rozsahu B7:B62): {=SUM(IF(LEN(B7:B62);1/COUNTIF($B$7:$B$62;$B$7:$B$62)))}
zadané ako maticový vzorec (CTRL+SHIFT+ENTER)
B) Súčty za jednotlivé týždne
Predpoklady:
1) k číslu týždňa pridať i príslušný rok, aby sa dal vygenerovať prvý (resp i posledný) deň príslušného týždňa ako dátum
napr. takto: =DATEVALUE("01-01-"&$A21)+7*(B21-1)-WEEKDAY(DATEVALUE("01-01-"&$A21);2)+1
2) sumár = celkový súčet - súčet za dátumy menšie ak prvý deň príslušného týždňa.
Viď príklad
Palo
radioblaster
08.02.10,20:18
A) Počet typov (ak sú v rozsahu B7:B62): {=SUM(IF(LEN(B7:B62);1/COUNTIF($B$7:$B$62;$B$7:$B$62)))}
zadané ako maticový vzorec (CTRL+SHIFT+ENTER)
B) Súčty za jednotlivé týždne
Predpoklady:
1) k číslu týždňa pridať i príslušný rok, aby sa dal vygenerovať prvý (resp i posledný) deň príslušného týždňa ako dátum
napr. takto: =DATEVALUE("01-01-"&$A21)+7*(B21-1)-WEEKDAY(DATEVALUE("01-01-"&$A21);2)+1
2) sumár = celkový súčet - súčet za dátumy menšie ak prvý deň príslušného týždňa.
Viď príklad
Palo
:eek::eek::eek::eek::eek:
no ono to asi funguje, ale ja tomu vobec nerozumiem.. :confused::confused::confused:
:(:mee:
zadané ako maticový vzorec (CTRL+SHIFT+ENTER)
B) Súčty za jednotlivé týždne
Predpoklady:
1) k číslu týždňa pridať i príslušný rok, aby sa dal vygenerovať prvý (resp i posledný) deň príslušného týždňa ako dátum
napr. takto: =DATEVALUE("01-01-"&$A21)+7*(B21-1)-WEEKDAY(DATEVALUE("01-01-"&$A21);2)+1
2) sumár = celkový súčet - súčet za dátumy menšie ak prvý deň príslušného týždňa.
Viď príklad
Palo
:eek::eek::eek::eek::eek:
no ono to asi funguje, ale ja tomu vobec nerozumiem.. :confused::confused::confused:
:(:mee:
PaloPa
09.02.10,06:00
:eek::eek::eek::eek::eek:
no ono to asi funguje, ale ja tomu vobec nerozumiem.. :confused::confused::confused:
:(:mee:
Čomu konkrétne :)
Ad: počet typov (vychádzam z tvojho XLSX príkladu):
1) ak by si použil vzorec =COUNTIF($D$7:$D$62;D7), kde D7 = "BPC-A", zistíš, že sa v stĺpci TYP vyskytuje 4x
2) ak urobíš 1/4 dostaneš zlomok, koľko percent predstavuje jeden "BPC-A", t.j 0,25
3) ak tieto zlomky zosúčtuješ, sumár pre 4 rovnaké hodnoty bude 1, tým vlastne zabezpečíš sumár jedinečných hodnôt
4) funkcia LEN() zabezpečuje, aby si odfiltroval prázdne bunky
5) no a array funkcia (maticová funkcia) to vypočíta pre celý stĺpec naraz
Ad: Súčty za jednotlivé týždne
Ak je dátum "Ship to IBM" väčší, ako prvý deň príslušného týždňa, t.j nemohol byť vtedy vyplnený, t.j bol prázdny.
Ak sa od celkového súčtu odpočíta, súčet za dátumy menšie ako príslušný dátum týždňa, dostane sa sumár k danému obdobiu nevyplnených.
Táto časť by možno ešte potrebovala upresnenie, pretože neviem akým spôsobom sa menia základné data (t.j. či sa tam dopĺňajú LEN príslušné dátumy).
Ak sa menia i položky a zaujímali by ťa i "historické data" za príslušný týždeň, vtedy by som doporučil urobiť to ku konkr.dňu makrom, resp. príslušný týždeň, zmeniť zo vzorca na hodnotu.
P.
no ono to asi funguje, ale ja tomu vobec nerozumiem.. :confused::confused::confused:
:(:mee:
Čomu konkrétne :)
Ad: počet typov (vychádzam z tvojho XLSX príkladu):
1) ak by si použil vzorec =COUNTIF($D$7:$D$62;D7), kde D7 = "BPC-A", zistíš, že sa v stĺpci TYP vyskytuje 4x
2) ak urobíš 1/4 dostaneš zlomok, koľko percent predstavuje jeden "BPC-A", t.j 0,25
3) ak tieto zlomky zosúčtuješ, sumár pre 4 rovnaké hodnoty bude 1, tým vlastne zabezpečíš sumár jedinečných hodnôt
4) funkcia LEN() zabezpečuje, aby si odfiltroval prázdne bunky
5) no a array funkcia (maticová funkcia) to vypočíta pre celý stĺpec naraz
Ad: Súčty za jednotlivé týždne
Ak je dátum "Ship to IBM" väčší, ako prvý deň príslušného týždňa, t.j nemohol byť vtedy vyplnený, t.j bol prázdny.
Ak sa od celkového súčtu odpočíta, súčet za dátumy menšie ako príslušný dátum týždňa, dostane sa sumár k danému obdobiu nevyplnených.
Táto časť by možno ešte potrebovala upresnenie, pretože neviem akým spôsobom sa menia základné data (t.j. či sa tam dopĺňajú LEN príslušné dátumy).
Ak sa menia i položky a zaujímali by ťa i "historické data" za príslušný týždeň, vtedy by som doporučil urobiť to ku konkr.dňu makrom, resp. príslušný týždeň, zmeniť zo vzorca na hodnotu.
P.
radioblaster
09.02.10,12:56
Čomu konkrétne :)
Ad: počet typov (vychádzam z tvojho XLSX príkladu):
1) ak by si použil vzorec =COUNTIF($D$7:$D$62;D7), kde D7 = "BPC-A", zistíš, že sa v stĺpci TYP vyskytuje 4x
2) ak urobíš 1/4 dostaneš zlomok, koľko percent predstavuje jeden "BPC-A", t.j 0,25
3) ak tieto zlomky zosúčtuješ, sumár pre 4 rovnaké hodnoty bude 1, tým vlastne zabezpečíš sumár jedinečných hodnôt
4) funkcia LEN() zabezpečuje, aby si odfiltroval prázdne bunky
5) no a array funkcia (maticová funkcia) to vypočíta pre celý stĺpec naraz
Ad: Súčty za jednotlivé týždne
Ak je dátum "Ship to IBM" väčší, ako prvý deň príslušného týždňa, t.j nemohol byť vtedy vyplnený, t.j bol prázdny.
Ak sa od celkového súčtu odpočíta, súčet za dátumy menšie ako príslušný dátum týždňa, dostane sa sumár k danému obdobiu nevyplnených.
Táto časť by možno ešte potrebovala upresnenie, pretože neviem akým spôsobom sa menia základné data (t.j. či sa tam dopĺňajú LEN príslušné dátumy).
Ak sa menia i položky a zaujímali by ťa i "historické data" za príslušný týždeň, vtedy by som doporučil urobiť to ku konkr.dňu makrom, resp. príslušný týždeň, zmeniť zo vzorca na hodnotu.
P.
POCET TYPOV
OK po 2 hodinach sme to s kolegom pochopili.. :D , ale mame dotaz keby ten rozsah nieje pevny tj D7 : D67, ale bol by dany filtrom tabulky? dalo by sa?
Ad: počet typov (vychádzam z tvojho XLSX príkladu):
1) ak by si použil vzorec =COUNTIF($D$7:$D$62;D7), kde D7 = "BPC-A", zistíš, že sa v stĺpci TYP vyskytuje 4x
2) ak urobíš 1/4 dostaneš zlomok, koľko percent predstavuje jeden "BPC-A", t.j 0,25
3) ak tieto zlomky zosúčtuješ, sumár pre 4 rovnaké hodnoty bude 1, tým vlastne zabezpečíš sumár jedinečných hodnôt
4) funkcia LEN() zabezpečuje, aby si odfiltroval prázdne bunky
5) no a array funkcia (maticová funkcia) to vypočíta pre celý stĺpec naraz
Ad: Súčty za jednotlivé týždne
Ak je dátum "Ship to IBM" väčší, ako prvý deň príslušného týždňa, t.j nemohol byť vtedy vyplnený, t.j bol prázdny.
Ak sa od celkového súčtu odpočíta, súčet za dátumy menšie ako príslušný dátum týždňa, dostane sa sumár k danému obdobiu nevyplnených.
Táto časť by možno ešte potrebovala upresnenie, pretože neviem akým spôsobom sa menia základné data (t.j. či sa tam dopĺňajú LEN príslušné dátumy).
Ak sa menia i položky a zaujímali by ťa i "historické data" za príslušný týždeň, vtedy by som doporučil urobiť to ku konkr.dňu makrom, resp. príslušný týždeň, zmeniť zo vzorca na hodnotu.
P.
POCET TYPOV
OK po 2 hodinach sme to s kolegom pochopili.. :D , ale mame dotaz keby ten rozsah nieje pevny tj D7 : D67, ale bol by dany filtrom tabulky? dalo by sa?
PaloPa
09.02.10,14:01
POCET TYPOV
OK po 2 hodinach sme to s kolegom pochopili.. :D , ale mame dotaz keby ten rozsah nieje pevny tj D7 : D67, ale bol by dany filtrom tabulky? dalo by sa?
No jedno z riešení by bolo použiť skrytý stĺpec, v ktorom by bol ten príslušný zlomok (1/X) a niekde v záhlaví pri zapnutom filtri použitá funkcia SUBTOTAL.
Ale páni, nerozmýšľali ste na prehľady použiť kontingenčnú tabuľku?
Robota na dve minúty, možnosť okamžitého usporiadania a filtrovania podľa čohokoľvek bude treba.
P.
OK po 2 hodinach sme to s kolegom pochopili.. :D , ale mame dotaz keby ten rozsah nieje pevny tj D7 : D67, ale bol by dany filtrom tabulky? dalo by sa?
No jedno z riešení by bolo použiť skrytý stĺpec, v ktorom by bol ten príslušný zlomok (1/X) a niekde v záhlaví pri zapnutom filtri použitá funkcia SUBTOTAL.
Ale páni, nerozmýšľali ste na prehľady použiť kontingenčnú tabuľku?
Robota na dve minúty, možnosť okamžitého usporiadania a filtrovania podľa čohokoľvek bude treba.
P.
radioblaster
09.02.10,17:47
No jedno z riešení by bolo použiť skrytý stĺpec, v ktorom by bol ten príslušný zlomok (1/X) a niekde v záhlaví pri zapnutom filtri použitá funkcia SUBTOTAL.
Ale páni, nerozmýšľali ste na prehľady použiť kontingenčnú tabuľku?
Robota na dve minúty, možnosť okamžitého usporiadania a filtrovania podľa čohokoľvek bude treba.
P.
ok, skusime..
no v mojom studiu Excelu som sa este ku kontingecnej tabulke nedostal. Zatial to necham tak ako si nam poradil a casom snad prejdeme aj na nu.
P.S. Vdaka za pomoc! :)
Ale páni, nerozmýšľali ste na prehľady použiť kontingenčnú tabuľku?
Robota na dve minúty, možnosť okamžitého usporiadania a filtrovania podľa čohokoľvek bude treba.
P.
ok, skusime..
no v mojom studiu Excelu som sa este ku kontingecnej tabulke nedostal. Zatial to necham tak ako si nam poradil a casom snad prejdeme aj na nu.
P.S. Vdaka za pomoc! :)
PaloPa
10.02.10,07:13
No, s tým skrytým riadkom by to bolo komplikovanejšie. Bez použitia makra by sa musela použiť komplikovaná kombinácia funkcií.
V tomto prípade je jednoduchšie použiť vlastnú funkciu (makro), tzv. UDF (User Defined Functions):
Function CalcUnique(xRng As String) As Long
' Macro recorded 10.02.2010 by PaloPa, pc-prog.sk
Dim Rng As Range, x As Range
Dim xx As String, i As Long, a
ReDim MyArray(0)
Application.Volatile
Set Rng = Range(xRng) '.SpecialCells(xlCellTypeVisible) - vlastnosť SpecialCells nefunguje pre UDF funkcie
For Each x In Rng
If Not x.EntireRow.Hidden Then 'obskoč skryté riadky
If x.Text <> "" Then 'obskoč prázdne riadky
xx = x.Text & "~"
a = Filter(MyArray, xx)
If UBound(a) = -1 Then
i = i + 1
ReDim Preserve MyArray(i - 1)
MyArray(i - 1) = xx
End If
End If
End If
Next x
CalcUnique = i
End Function
Viď príklad (zobrazenie kódu ALT+F11)
Palo
... s tými KTG nie je nič stratené, stačí kvalitné školenie :)
V tomto prípade je jednoduchšie použiť vlastnú funkciu (makro), tzv. UDF (User Defined Functions):
Function CalcUnique(xRng As String) As Long
' Macro recorded 10.02.2010 by PaloPa, pc-prog.sk
Dim Rng As Range, x As Range
Dim xx As String, i As Long, a
ReDim MyArray(0)
Application.Volatile
Set Rng = Range(xRng) '.SpecialCells(xlCellTypeVisible) - vlastnosť SpecialCells nefunguje pre UDF funkcie
For Each x In Rng
If Not x.EntireRow.Hidden Then 'obskoč skryté riadky
If x.Text <> "" Then 'obskoč prázdne riadky
xx = x.Text & "~"
a = Filter(MyArray, xx)
If UBound(a) = -1 Then
i = i + 1
ReDim Preserve MyArray(i - 1)
MyArray(i - 1) = xx
End If
End If
End If
Next x
CalcUnique = i
End Function
Viď príklad (zobrazenie kódu ALT+F11)
Palo
... s tými KTG nie je nič stratené, stačí kvalitné školenie :)