GoldenLine
Zaloguj się
Tomasz K.

Tomasz K. Specjalista ds.
Logistyki

Temat: Suma.Jeżeli + Fragment.tekstu

Proszę o informację czemu nie mogę połączyć dwóch formuł jakimi są suma.jeżeli oraz fragment tekstu.
Załóżmy ze ten sam produkt jest produkowany pod dwoma różnymi kodami
np. komórka A1 - 0_87888D
oraz
komórka A2 - 1_87888D

Chcę z sumować ilości z tych kodów tak aby Excel dał mi sumę tych dwóch kodów ponieważ od 3-go do 6go znaku są identyczne.

=SUMA.JEŻELI($A$1:$A$2;(FRAGMENT.TEKSTU(A1;3;6));$B$1:$B$2)

Gdzie w komórkach B1 oraz B2 są jakieś ilości.

Funkcja ma mieć zastosowanie w dużej bazie danych gdzie znajdują się przypadki gdzie dany produkt jest produkowany pod dwoma kodami lecz definitywnie jest to ten sam produkt.
Link do wypowiedziLink
Tomasz Zarzyka

Tomasz Zarzyka X-mart, właściciel

Temat: Suma.Jeżeli + Fragment.tekstu

Suma.jeżeli raczej do tego się nie nadaje.
Tak na szybko przychodzi mi do głowy zastosowanie kolumny pomocniczej z fragmentem tekstu, następnie wykonanie suma.warunków. Wtedy zadziała.

Pamiętaj, że formuły tego typu jak suma.warunków obciążają system - więc po przeliczeniu zamień na wartości albo wykonuj operację za pomocą kodu VBA.
Link do wypowiedziLink
Artur D.

Artur D. Solution Architect,
Atos IT Services Sp.
z o.o.

Temat: Suma.Jeżeli + Fragment.tekstu

Suma.jeżeli nie da rady. Jak już to SUMPRODUCT z małą modyfikacją - nazwa szukanego produktu w kolumnie D

=SUMPRODUCT((INDEX(MID(A1:A2;3;6);)=D1)*B1:B2)

=SUMA.ILOCZYNÓW((INDEKS(FRAGMENT.TEKSTU(A1:A2;3;6);)=D1)*B1:B2)
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Suma.Jeżeli + Fragment.tekstu



=SUMIF(A1:A3,"*87888D",B1:B3)

lub


=SUMIF(A1:A3,"*"&D1,B1:B3)

gdzie D1:


= MID(A1,2,6)

lub


=SUMIF(A1:A3,"*"&MID(A1,3,6),B1:B3)

lub


=SUM((MID(A1:A3,3,6)="87888D")*(B1:B3))

i CSE
lub


=SUM(IF(MID(A1:A3,3,6)="87888D",B1:B3,""))

i CSETen post został edytowany przez Autora dnia 09.07.15 o godzinie 17:29
Link do wypowiedziLink
Bogdan Gilarski

Bogdan Gilarski www.excelperfect.pl
Perfect And
Practical

Temat: Suma.Jeżeli + Fragment.tekstu

Można wprost, bez pomocniczych 
=SUMA.JEŻELI(A1:A2;"*8788*";B1:B2)
Link do wypowiedziLink
Tomasz Zarzyka

Tomasz Zarzyka X-mart, właściciel

Temat: Suma.Jeżeli + Fragment.tekstu

reasumując wszystkie wypowiedzi mi zadziałała następująca składnia przy zachowaniu zmiennego odniesienia:
=SUMA.JEŻELI($A$1:$A$2;"*"&FRAGMENT.TEKSTU(A1;3;6)&"*";$B$1:$B$2)
Link do wypowiedziLink
Tomasz K.

Tomasz K. Specjalista ds.
Logistyki

Temat: Suma.Jeżeli + Fragment.tekstu

Tomasz Z.:
reasumując wszystkie wypowiedzi mi zadziałała następująca składnia przy zachowaniu zmiennego odniesienia:
=SUMA.JEŻELI($A$1:$A$2;"*"&FRAGMENT.TEKSTU(A1;3;6)&"*";$B$1:$B$2)


Z góry dziękuję za pomoc. Najbardziej chodziło mi o połączenie tych dwóch formuł co w Pańskim przypadku Panie Tomaszu działa bez zarzutów. Ja szedłem dobrą drogą lecz muszę sformułować formułę tak jak Pan powyżej.
Jeszcze raz dzięki.
Link do wypowiedziLink
Tomasz Zarzyka

Tomasz Zarzyka X-mart, właściciel

Temat: Suma.Jeżeli + Fragment.tekstu

ok, super.
niech Pan jeszcze spróbuje następującą składnię:
=SUMA.JEŻELI(A:A;"*"&FRAGMENT.TEKSTU(A2;3;6)&"*";B:B)
Wtedy ma Pan pewność, że wszystkie wiersze zostaną uwzględnione - pozostaje tylko kwestia przeliczania.
Niech Pan jeszcze zwróci uwagę na długość fragmentu tekstu - 6 oznacza długość tekstu, a nie tekst od 3 znaku do 6. W Pana formule było co innego, niż Pan napisał.
powodzenia
Link do wypowiedziLink
Tomasz K.

Tomasz K. Specjalista ds.
Logistyki

Temat: Suma.Jeżeli + Fragment.tekstu

Witam,

Odświeżam temat ponieważ dostrzegłem mały błąd;/

Kod styczeń suma
1_7205D5900G 1 3
1_7205D5900G43 1 2
1_7205D5900G43B 1 1

W kolumnie SUMA mam taką formułe:
=SUMA.JEŻELI($A2:$A4;"*"&FRAGMENT.TEKSTU($A2;3;30)&"*";B2:B4)

Czemu w poszczególnych sumach nie posiadam wyniku "1"
Przecież powyższe 3 kody różnią się ostatnimi znakami.
Użyłem liczby 30 w przypadku długości testu ponieważ kody mogą zdarzyć się dłuższe więc chcąc uniknąć błedu wpisałem ilość która na pewno nie wystąpi.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Suma.Jeżeli + Fragment.tekstu

Tomasz K.:
Witam,

Odświeżam temat ponieważ dostrzegłem mały błąd;/
W kolumnie SUMA mam taką formułe:
=SUMA.JEŻELI($A2:$A4;"*"&FRAGMENT.TEKSTU($A2;3;30)&"*";B2:B4)
to nie błąd tylko niezrozumienie działania wildcards.

edit:
If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text.Ten post został edytowany przez Autora dnia 15.09.15 o godzinie 12:07
Link do wypowiedziLink
Magda H.

Magda H. niezależny

Temat: Suma.Jeżeli + Fragment.tekstu

Dzień dobry,
próbuję napisać formułę, która będzie sumowała wg fragmentu tekstu, ale tylko dodatnie liczby albo ujemne.

W kolumnie A mam numery kont księgowych o powtarzalnych początkach np. 200-00-00-1234, 200-00-00-4567, 201-00-00-9876, 201-00-2365. W kolumnie B mam liczby (salda) dodatnie lub ujemne.
Chcę sumować kolumnę B po pierwszych 9 znakach z numeru konta w kolumnie A, ale osobno liczby ujemne i liczby dodatnie.

Napisałam takie formuły:
=SUMA.WARUNKÓW(B:B;A:A;"200-00-00";B:B;">0")
i

=SUMA.WARUNKÓW(B:B;A:A;"200-00-00";B:B;"<0")

Działają tylko jeśli dla numeru np. 200-00-00 występują wyłącznie salda ujemne albo salda dodatnie. Gdy pojawiają się i takie, i takie formuła ich nie dzieli tylko sumuje wszystkie w tej formule z ">0" . Ta z "<0" daje wynik zerowy. I jeszcze te formuły nie przeliczają po zmianie wartości bazowych w kolumnie B.

Jak to obejść. Proszę o pomoc.
Link do wypowiedziLink
Marzanna Szulta

Marzanna Szulta właściciel, Usługi
Informatyczne
SZULTASET

Temat: Suma.Jeżeli + Fragment.tekstu

Trochę to dziwne, jeżeli czasem pokazuje dobrze. Moim zdaniem błąd jest w kryterium pierwszego warunku na wyszukiwanie numerów kont. Te formuły powinny wyglądać tak:
=SUMA.WARUNKÓW(B:B;A:A;"200-00-00*";B:B;">0")
oraz
=SUMA.WARUNKÓW(B:B;A:A;"200-00-00*";B:B;"<0")

I proponuję sprawdzić, czy włączone jest przeliczanie automatyczne, a nie ręczne (Opcje --> Formuły) -
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Suma.jeżeli




Wyślij zaproszenie do
Anuluj