GoldenLine
Zaloguj się
Janusz K.

Janusz K. Ekspert rozwoju i
przyszłości firm,
struktur, systemów
or...

Temat: Budowanie formuł

Tu zgłaszaj problemy, jakie masz z:
- tworzeniem formuł,
- nadawaniem nazw komórkom i obszarom,
- komunikatami o błędach,
- pracą z tablicami dwuwymiarowymi,
- przeliczeniami arkusza

Nie zapomnij podać wersji programu - to ułatwi znalezienie lekarstwa.
Link do wypowiedziLink
Grzegorz R.

Grzegorz R. od 1994 w logistyce

Temat: Budowanie formuł

Szukam metod zamiany tabelę przestawną na listę.

Chodzi o działanie odwrotne do tworzenia tabeli przestawnej w sytuacji gdy liczby w tabeli reprezentują pojedyncze wartości z listy.
Często dysponuję danymi w postaci listy (kilkaset i więcej wierszy), analizuję je i zmieniam w strukturze tabeli i w takie formie mam wynik, który muszę spowrotem przekształcić w listę.
Obecnie mozolnie powielam kolumny nagłówkowe do kolejnych wierszy listy (ich struktura się czasem zmienia) a następnie wyszukiwaniem wybieram wartości.

Może ktoś ma lepsze pomysły..
Link do wypowiedziLink
Janusz K.

Janusz K. Ekspert rozwoju i
przyszłości firm,
struktur, systemów
or...

Temat: Budowanie formuł

Spróbuj inaczej sformułować problem... :-)
Link do wypowiedziLink

konto usunięte

Temat: Budowanie formuł

dwuklik w wartość obliczoną

powinna pojawić sie lista wartości budującą daną wartość o ile nie soztało to wyłączone w tabeli przestawnej
Link do wypowiedziLink
Grzegorz R.

Grzegorz R. od 1994 w logistyce

Temat: Budowanie formuł

Janusz K.:
Spróbuj inaczej sformułować problem... :-)

Czyli niejasne... :)

Firmowy system IT posługuje się danymi w strukturze listy, np. z następującymi nagłówkami:
oddział/rodzaj serwisu/rodzaj cennika/strefa/przedział wagowy/stawka.
Mamy 11 oddziałów, kilka rodzajów serwisu, kilka rodzajów cenników, itd. Stawki są analizowane i aktualizowana jest ich wartość.
Analiza jest wykonywana w tabelach przestawnych, a aktualizacja w tabelach excela powstałych poprzez skopiowanie tabel przestawnych, aby można było dokonywać zmian. Zadanie polega na tym, aby z tabeli powstałej w wyniku utworzyć na powrót listę (właściwie jej fragment) o strukturze listy pierwotnej.

Np. tabelę stawek:

strefa 1 2 3 4
przedział wagowy
do 50 kg 150 250 350 450
do 100 kg 1100 2100 3100 4100
do 200 kg 1200 2200 3200 4200

chcę przekształcić w listę:

strefa przedział wagowy stawka
1 50 150
1 100 1100
1 200 1200
2 50 250
2 100 2100
2 200 2200
itd.

Obecnie duże struktury buduję poprzez mozolne budowanie pierwszych kolumn (dużo czasu zajmuje mi odtworzenie struktury... EUREKA!!! przecież mogę użyć struktury źródłowej). Wyszukiwanie to pestka.

Dziękuję uprzejmie za pomoc :):). Jak widać warto poprosić :):)

Ale czasami struktura też się zmienia i zadanie warto by i tak zautomatyzować :)
Link do wypowiedziLink

konto usunięte

Temat: Budowanie formuł

nie masz dostępu do danych źródłowych tego zestawienia?
Link do wypowiedziLink
Grzegorz R.

Grzegorz R. od 1994 w logistyce

Temat: Budowanie formuł

Przemysław R.:
nie masz dostępu do danych źródłowych tego zestawienia?
Mam dostęp, właśnie z tych danych robię tabelę przestawną.
Później kopiuję ją jako wartości w kolejnym arkuszu i zmieniam liczby.
A na koniec chcę mieć te liczby spowrotem w postaci identycznej jak dane źródłowe.
Link do wypowiedziLink

konto usunięte

Temat: Budowanie formuł

to się tak nie da
tabela przestawna z definicji robi jakieś kalkulacje np. licza rekody, sumuje itp, itd, zresztą jest wiele grupowań w związku z czym trudno osiągnąć efekt którego oczekujesz makrem uniwersalnym

najlepiej pracuje sie właśnie z danymi źródłowymi np. za pomocą SQL-a
Link do wypowiedziLink

konto usunięte

Temat: Budowanie formuł

EUREKA!!! przecież mogę użyć struktury źródłowej). Wyszukiwanie to pestka.

Dziękuję uprzejmie za pomoc :):). Jak widać warto poprosić :):)

Ale czasami struktura też się zmienia i zadanie warto by i tak zautomatyzować :)

Witaj,

Faktycznie skomplikowane zadanie ale apropos owej Eureki ;) nie bardzo wiem jak Ci pomóc w Excelu, ale kojarzę, że system controllingowy jednego z moich klientów, właśnie EURECA się nazywający tego typu operacje pozwala przeprowadzać. Czy akurat takie jak Cię interesują to nie wiem dokładnie, ale jak chcesz mogę zapytać i przesłac info na priv.

Pozdr.

Bartek
Link do wypowiedziLink
Grzegorz R.

Grzegorz R. od 1994 w logistyce

Temat: Budowanie formuł

Bardzo dziękuję za zaangażowanie.
Problemik nieduży, a i rozwiązanie proste.
System kontrolingowy to jednak zbyt duży arsenał do takiego drobiazgu.
Pozdrawiam
Grzegorz
Link do wypowiedziLink
Aneta Ł.

Aneta Ł. Specjalista ds.
controllingu -
ukierunkowana na
rozwój:)

Temat: Budowanie formuł

Owszem, można by napisać makro,
w którym można by najpierw kazać excelowi zliczyć ilość niepustych komórek ze skopiowanej tabeli przestawnej jako wklej specjalnie np w kolumnie A,
potem wypełnić te elementy, które są puste ( jak na przykład gdy dane są zgrupowane) wartością powyżej dla wszystkich niezbędnych wierszy i kolumn.

I mamy bazę prawie źródłową ( bo uproszczoną, gdyż tabelka przestawna jeżeli coś się powtarzało to zsumowała wartości,chyba że nie ma takich powtórek) po ręcznej zmianie wartości:)

Jeżeli dobrze zrozumiałam problem. :)
Link do wypowiedziLink
Grzegorz R.

Grzegorz R. od 1994 w logistyce

Temat: Budowanie formuł

Właśnie o to chodzi.
Muszę się jeszcze nauczyć pisania makr.
Na razie tylko rejestruję (czasem).
Póki co rozpisuję to jak układ liczbowy o zmiennej podstawie na każdej pozycji, a później liczby zamieniam na wartości z nagłówków.

Przeczytałem i chyba rozumiem.
Ciekawe, czy ktoś jeszcze? :):)
Link do wypowiedziLink

konto usunięte

Temat: Budowanie formuł

Witam,
załóżmy, że dane są w zakresie A3:E5, wtedy da się problem "ujarzmić" formułkami typu:

1 kolumna. =JEŻELI(ZAOKR.DÓŁ((WIERSZ()-6)/4;0)>ILE.NIEPUSTYCH($A$1:$A$8);"";ZAOKR.DÓŁ((WIERSZ()-6)/4;0)) (formułka od A10 w dół)

2 kolumna. =JEŻELI(A10<>"";INDEKS($A$3:$A$5;A10);"") (formułka od B10 w dół)

3 kolumna. =JEŻELI(A10<>"";INDEKS($B$3:$E$5;A10;LICZ.JEŻELI($A$10:A10;A10));"") (formułka od C10 w dół)

Pozdrawiam, w razie problemów zapraszam do kontaktu na prv.Darek J. edytował(a) ten post dnia 21.02.10 o godzinie 12:26
Link do wypowiedziLink
Robert Tymiński

Robert Tymiński Logistyka

Temat: Budowanie formuł

Witam,
Pomoże ktoś chciałem zrobić sobie plik z awizacjami dostaw do magazynu który pokazywał by mi wszystkie rozbieżności dotyczące czasu podstawienia się samochodu. A mianowicie
B1Data awizacji C1 Godz. Awizacji D1 Okienko( i tu bym chciał mieć trzy okienka jedno pomiędzy 06:00 a 10:00 i 10:00 a 14:00 i trzecie 14:00 a 18:00 E1 Dostawca F1 Ilość palet G1 Data złożenia dokumentów H1 Przyjazd [złożenie dokumentów] I1 Rozładunek [start] J1 Rozładunek [FINISH] K1 Rozbieżności ( by pokazywało albo 1 albo 0 jeśli była by jaka kolwiek różnica pomiędzy albo datą albo godziną w przyjeździe ) L1 Czas opóźnienia ( a tu by pokazywało czas jaki jest dany samochód spóźniony bądź przyjechał za wcześnie ) Jeśli napisałem coś niejasno sprecyzuję i jeszcze raz proszę o pomoc.
Link do wypowiedziLink
Zdzisław Sołtys

Zdzisław Sołtys Emeryt

Temat: Budowanie formuł

Jest to dość skomplikowane ale spróbuję się z tym zmierzyć.
Proszę przesłać na: soltys.z@wp.pl przykładowy plik (tabelkę) a ja popracuję nad wykończeniem.

Dziś (7.08) zrobiłem coś takiego:

Zrób prostą tabelkę:
Kolumna B - ustaw format data: (Formatuj komórki > Data > 2003-03-14);
Kolumna C – ustaw format czas (Formatuj komórki > Czas > 13:30);
Kolumna D – Wprowadź poprawność danych ( Dane > Sprawdzenie poprawności > Lista > w okienku źródło wpisz: 6:00-10:00;10:00-14:00;14:00-18:00 i zatwierdź OK);
Kolumna G – format jak a kol. B;
Kolumna H, I, J – format jak w kol C;
Kolumna K – wpisz formułę: =JEŻELI(B2+C2<>G2+H2;1;0)
Kolumna L – ustaw format: (Formatuj komórki > Niestandardowe > w okienku Typ wpisz: [g]:mm i zatwierdź. Excel będzie wyświetlał powyżej 24 godzin
Kolumna L – wpisz formułę: =JEŻELI((G2+H2)-(B2+C2)>0;(G2+H2)-(B2+C2);TEKST((B2+C2)-(G2+H2);"-[g]:mm"))
Lub formułę: =(G2+H2)-(B2+C2) i koniecznie zmień system daty:
Narzędzia > Opcje > Przeliczanie > odhacz System daty 1904 i zatwierdź OK.;
Excel będzie liczył czas ujemny. Ale uważaj bo zmiana systemu daty spowoduje zmianę wszystkich wpisanych wcześniej dat o 4 lata.
Jeżeli potrzeba prześlę przykładowy plik.
Mam Excel 2003 i w nim to robiłem.Zdzisław Sołtys edytował(a) ten post dnia 07.08.11 o godzinie 11:35
Link do wypowiedziLink
Jakub Jastrzębski

Jakub Jastrzębski ...robi to co lubi

Temat: Budowanie formuł

I ja chciałbym skorzystać z Waszej wiedzy.
Mam problem ze zbudowaniem formuł dla takiej tabeli:

A B A B A B A B A B
1 -1 -2 -1 -1 -1 0 0 0 -2 0
2 -2 0 -2 0 -2 0 0 0 -2 2
3 0 3 3 0 3 3 -3 0 -3 0
4 4 0 0 0 0 0 4 4 0 8
5 0 0 0 -5 0 -10 -5 0 0 0
6 0 6 0 6 -12 -6 0 0 -12 0
S 1 7 0 0 -12 -13 -4 4 -19 10
Σ 51 107 0 0 -162 -163 -54 54 -219 110
1p 10 8 10 10 8 10 10 6 12 20
2p 22 20 10 8 18 20 14 24 18 12
3ka 0 24 0 9 0 0 36 3 3 18
MS 0 0 0 0 0 0 0 0 0 0
DS. 0 40 0 0 0 0 0 0 0 0
F30 0 51 0 74 46 0 45 66 44 0
K50 0 74 0 0 0 0 58 70 0 0
G100 0 0 0 0 0 0 0 0 0 0
SZ 46 28 23 17 26 19 42 27 18 13
S 78 245 43 118 98 49 205 196 95 63
R 129 352 43 118 -64 -114 151 250 -124 173
Lok 2 1 2 1 1 2 2 1 2 1

1. Formuła która zlicza ilość „1” w wierszu „Lok” tylko dla kolumn „A”
2. Formuła która wyszukuje najwyższą wartość w wierszu „R” tylko w kolumnach „A”
3. Formuła która sumuje wartości w wierszu „R” tylko w kolumnach „A”

Czy jest taka możliwość aby Excel zliczał ilość komórek bez konkretnego zadania logicznego (np. ilość komórek z wypełnieniem czerwonym lub w których wartości mają dodany jakiś znak czy cokolwiek innego..)? :)
Pozdrawiam i dziękuję za wszelką pomoc.
Kuba
Link do wypowiedziLink
Jakub Jastrzębski

Jakub Jastrzębski ...robi to co lubi

Temat: Budowanie formuł

Mimo moich starań nie wyraziłem się jasno. :) Trudność w budowaniu tej formuły jest taka, że co chwilę przybywają kolejne kolumny „A” i „B”. Oczywiście gdyby tak nie było wystarczyłyby proste JEŻELI, SUMA czy MAX ale w tym wypadku chciałbym aby nie trzeba było przebudowywać formuły za każdym razem gdy przybywają kolejne kolumny. :)

Prawie pomogła mi formuła WYSZUKAJ.POZIOMO ale szuka tylko do znalezienia pierwszej odpowiadającej wartości. Czy jest jakiś sposób aby szukała dalej lub czy jest jakaś inna formuła która będzie bardziej odpowiednia?

Pozdrawiam
Link do wypowiedziLink
Ryszard G.

Ryszard G. .

Temat: Budowanie formuł

Hej,
jeżeli wiesze w obliczeniach są stałe (np. 'Lok' jest zawsze w wierszu 21), to formuła dla
1. Formuła która zlicza ilość „1” w wierszu „Lok” tylko dla kolumn „A”

jest
=SUMA.ILOCZYNÓW(($1:$1="A")*($21:$21=1))

Jeżeli trzeba ustalić numer wiersza, w którym w kolumnie A jest 'Lok', to formuła:
=SUMA.ILOCZYNÓW((1:1="A")*(ADR.POŚR(PODAJ.POZYCJĘ("Lok";$A:$A;0) &":"& 

PODAJ.POZYCJĘ("Lok";$A:$A;0))=1))

Punkt 3 zrobisz analogicznie.
Punkt 2 zrobisz podobnie ale przy pomocy funkcji MAX zatwierdzanej tablicowo.Ryszard G. edytował(a) ten post dnia 08.01.12 o godzinie 16:26
Link do wypowiedziLink
Jakub Jastrzębski

Jakub Jastrzębski ...robi to co lubi

Temat: Budowanie formuł

Właśnie uświadomiłem sobie jak mało wiem o Excelu. :)
Baaardzo dziękuję za pomoc!!
Link do wypowiedziLink
Jakub Jastrzębski

Jakub Jastrzębski ...robi to co lubi

Temat: Budowanie formuł

Nie wszystko mi się zgadza. :)
SUMA.ILOCZYNÓW w tym wypadku zlicza ilość kolumn które mają dwie wartości - "A" i "1" w konkretnych wierszach.
Czy na pewno za pomocą tej formuły da się policzyć sumę wartości z wiersza "R" dla kolumn "A"?
(W tym wypadku nie liczę sztuki wyników ale ich sumę)
Wydaje mi się, że ta formuła nie będzie też miała zastosowania dla wyszukania najwyższej wartości z wiersza "R" dla kolumn "A", ponieważ w tym wypadku muszę wyszukać tylko jeden wynik w kolumnach "A".

Chyba że się mylę.. :)

Pozdrawiam
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Przeciąganie formuł




Wyślij zaproszenie do
Anuluj