Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 64418 Skopiuj do schowka Excel traktuje różne stringi jak takie same (SUMA.JEŻELI)
Autor Wiadomość
kemot7 
świeżak


Posty: 4
Wysłany: 13-03-2019, 15:20   Excel traktuje różne stringi jak takie same (SUMA.JEŻELI)

Dzień dobry

Napotkałem na dziwny problem, z którym nie potrafię sobie poradzić.
Korzystając z funkcji SUMA.JEŻELI oraz SUMA.WARUNKÓW Excel gubi się w rozróżnianiu komórek przechowujących konta bankowe. Jeśli pierwsze 15 cyfr (przechowywanych jako teks) jest takie same to traktuje wartości jako identyczne, bez względu na dalsza zawartość.

Przykładowo:
49105014161000019708203502 100,00 zł Janek
49105014161000019701742531 200,00 zł Ola
49105014161000019708203502 500,00 zł Janek

Przy podliczeniu ile wpłacił Janek, (SUMA.JEŻELI) uzyskuję wynik 800 (oczywiście kryterium jest nr konta a nie imię)

Dopiera zmiana cyfry na 15 pozycji (ostatnie 0 przed 1970xxx) powoduje, że stringi rozpoznawane są jako różne.

Tak samo zachowuje się SUMA.WARUNKÓW.

Pozdrawiam
Tomek
ID posta: 364130 Skopiuj do schowka
 
 
Marecki 
Excel Expert



Wersja: Win Office 2019
Pomógł: 2079 razy
Posty: 6858
Wysłany: 13-03-2019, 15:41   

kemot7, przeczytaj, tam znajdziesz rozwiązanie -> http://www.excelforum.pl/...ach-vt33685.htm
_________________
Hardware - ta część komputera, którą można kopnąć kiedy software przestanie funkcjonować.

Szkolenia z Excela , FB
Office 2019 Professional Plus , Windows 10 x64
Pozdrawiam, były mkkk23 teraz Marecki.
ID posta: 364134 Skopiuj do schowka
 
 
Waldek 
ExcelSpec


Pomógł: 119 razy
Posty: 574
Wysłany: 13-03-2019, 17:55   

Dziwne, bo w pomocy: https://support.office.com/pl-pl/article/licz-jeżeli-funkcja-e0de10c6-f885-4e71-abb4-1f464816df34 jest napisane:
Cytat:
"Funkcja LICZ.JEŻELI zwraca niepoprawne wyniki w razie użycia jej do dopasowania ciągów mających więcej niż 255 znaków.
Do dopasowania ciągów mających więcej niż 255 znaków należy użyć funkcji ZŁĄCZ.TEKSTY lub operatora łączenia (&). Na przykład: =LICZ.JEŻELI(A2:A5;"długi ciąg"&"inny długi ciąg")."

a tym tekstom daleko do 255 znaków. Pomoc dotyczy Excel 2007 lub wyższy.
  
ID posta: 364149 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1225 razy
Posty: 4202
Wysłany: 13-03-2019, 18:43   

Tu problem polega na tym, że wartość kryterium choć formalnie jest tekstem, jeśli ma postać liczby jest konwertowana na liczbę, więc nie może mieć więcej niż 15 cyfr. Żeby zapobiec tej konwersji można dopisać do niej z przodu lub z tyłu gwiazdkę (symbol zastępczy), ale wtedy dopasowanie będzie to musiało uwzględniać (oprócz dokładnego dopasowania trzeba brać pod uwagę liczby z ewentualnymi dodatkami z przodu lub z tyłu).

Długie_ciągi_cyfr.xlsx
Pobierz Plik ściągnięto 9 raz(y) 9.15 KB

ID posta: 364154 Skopiuj do schowka
 
 
ąćęłńóś
ExcelSpec


Pomógł: 185 razy
Posty: 838
Wysłany: 13-03-2019, 18:50   

kemot7 napisał/a:
Jeśli pierwsze 15 cyfr (przechowywanych jako teks)

Z podwójnym apostrofem ... ' ... na początku tych "liczb" próbowałeś ?
  
ID posta: 364156 Skopiuj do schowka
 
 
Waldek 
ExcelSpec


Pomógł: 119 razy
Posty: 574
Wysłany: 13-03-2019, 19:04   

Maciej Gonet napisał/a:
Tu problem polega na tym, że wartość kryterium choć formalnie jest tekstem, jeśli ma postać liczby jest konwertowana na liczbę, ...

W takim razie dobrze by było gdyby numery kont były ze spacjami w środku numeru, tak jak zwykle się je podaje.

PS
Ta formuła ma błąd bo ze spacjami i apostrofem też konwertuje na liczbę. Gwiazdka przed numerem działa.
  
ID posta: 364158 Skopiuj do schowka
 
 
Marecki 
Excel Expert



Wersja: Win Office 2019
Pomógł: 2079 razy
Posty: 6858
Wysłany: 13-03-2019, 20:02   

W załączniku są 3 rozwiązania:
1. SUMA.ILOCZYNÓW
2. Power Query
3. Tabela przestawna

konto.xlsx
Pobierz Plik ściągnięto 3 raz(y) 21.04 KB

_________________
Hardware - ta część komputera, którą można kopnąć kiedy software przestanie funkcjonować.

Szkolenia z Excela , FB
Office 2019 Professional Plus , Windows 10 x64
Pozdrawiam, były mkkk23 teraz Marecki.
ID posta: 364161 Skopiuj do schowka
 
 
ąćęłńóś
ExcelSpec


Pomógł: 185 razy
Posty: 838
Wysłany: 13-03-2019, 20:51   

A ja bych to, panie dziejku, zapostrofił, hej, ... z lewa ... z prawa ... ode środecka i kaj tam popadnie i tyla ... :->

Liczbo-Teksty.jpg
Plik ściągnięto 14 raz(y) 29.43 KB

ID posta: 364165 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1229 razy
Posty: 4255
Wysłany: 13-03-2019, 23:52   

Waldek napisał/a:

PS
Ta formuła ma błąd bo ze spacjami i apostrofem też konwertuje na liczbę. Gwiazdka przed numerem działa.

Ja sprawdziłem. Bez spacji ale z 2 apostrofami działa bezbłędnie - COUNTIF I SUMIF
ID posta: 364173 Skopiuj do schowka
 
 
Waldek 
ExcelSpec


Pomógł: 119 razy
Posty: 574
Wysłany: 14-03-2019, 00:13   

Taka formuła jak poniżej, czyli ZŁĄCZ.TEKST i "*" (gwiazdka) też działa:
Kod:
=SUMA.JEŻELI(A1:A3;ZŁĄCZ.TEKST("*";A1);E1:E3)

Chodzi o przykład z pierwszego postu:
Cytat:
Przykładowo:
49105014161000019708203502 100,00 zł Janek
49105014161000019701742531 200,00 zł Ola
49105014161000019708203502 500,00 zł Janek

gdy numery są w A1:A3 a kwoty w E1:E3.
ID posta: 364174 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1229 razy
Posty: 4255
Wysłany: 14-03-2019, 00:38   

Cytowałem
Cytat:

Ta formuła ma błąd bo ze spacjami i apostrofem też konwertuje na liczbę. Gwiazdka przed numerem działa.

Ale nie odnosiłem się do "Gwiazdka przed numerem działa" bo Maciej dołączył plik, a w nim widać, ze działa.

Co prawda w pliku gwiazda jest na końcu ale przecież Maciej napisał wyraźnie
Cytat:

można dopisać do niej z przodu lub z tyłu gwiazdkę
  
ID posta: 364176 Skopiuj do schowka
 
 
Waldek 
ExcelSpec


Pomógł: 119 razy
Posty: 574
Wysłany: 14-03-2019, 01:39   

Przyznaję się, że nie oglądałem wcześniej pliku Macieja Goneta, a z tekstu wynikało, że chodzi o wstawienie gwiazdki do numeru bez zmiany formuły. Co ciekawe wystarczy wstawić gwiazdkę tylko do numeru z kryterium. Dopisałem taki przykład poniżej.

Długie_ciągi_cyfr (3).xlsx
Pobierz Plik ściągnięto 4 raz(y) 10.93 KB

ID posta: 364181 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1229 razy
Posty: 4255
Wysłany: 14-03-2019, 02:24   

Waldek napisał/a:
Co ciekawe wystarczy wstawić gwiazdkę tylko do numeru z kryterium.

Przecież to jest normalne. Gwiazdka w danych ma znaczenie samego siebie. To nie oznacza dowolnego znaku. Po prostu 1 zwykły znak tak, jak a, b, c, ..., z czy inne. Tak samo możesz wstawić a i masz:

a49105014161000019708203502
a49105014161000019701742531
a49105014161000019708203502


Teraz to LICZ.JEŻELI już "łyka" normalnie, bo w komórkach są prawdziwe teksty takie same jak "Janek" czy "Jolka" czy "sklep 1"
ID posta: 364182 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1229 razy
Posty: 4255
Wysłany: 14-03-2019, 03:31   

Aha, przeoczyłem gwiazdkę w criteria pochodzącą z A5. Ale jeśli kopiujemy formułę - E5 =COUNTIF(A$5:A$7;A5) w dół to E6, E7 już nie są dobre - mają być E6 = 1, E7 = 2, a są E6 = 2, E7 = 2.

Żeby można kopiować w dól i mieć poprawne wyniki to wszystkie A5, A6 i A7 muszą mieć gwiazdkę. I właśnie w tym przypadku można zastąpić gwiazdkę dowolną literą i wychodzi to samo
ID posta: 364183 Skopiuj do schowka
 
 
kemot7 
świeżak


Posty: 4
Wysłany: 14-03-2019, 08:28   

Bardzo dziękuję wszystkim za pomoc i cenne sugestie.
Myślałem początkowo, żeby podzielić konto na 2 części i tak porównywać, ale widzę, że są znacznie prostsze rozwiązania
ID posta: 364191 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe