Sumowanie po 3 ostatnich cyfrach ID

Jak sumować zyski przyjmując jako kryterium 3 ostatnie cyfry ID?

Wydawałoby się, że żeby sumować po 3 ostatnich liczbach ID wystarczy skorzystać z funkcji SUMA.JEŻELI i sprytnego łączenia symbolu wieloznacznego "*" z kryterium 3 ostatnich liczb ID.

=SUMA.JEŻELI(B2:B8;"*"&D2;A2:A8)

Niestety takie połączenie nie działa na liczbach.

Sumowanie po 3 ostatnich cyfrach ID — sztuczki #12

Ponieważ jednak chodzi o sprawdzenie 3 ostatnich cyfr, to łatwo jest je wyciągnąć korzystając z funkcji PRAWY. Można nawet przeprowadzić tą operację na całym przeszukiwanym zakresie:

PRAWY(B2:B8;3)

Tabela, z której chcemy wyciągnąć sumę

Warto tutaj zwrócić uwagę na to, że wynikiem nie są liczby tylko tekst. Możesz to zobaczyć zaznaczając całą formułę i naciskając F9

{"696";"943";"869";"696";"875";"398";"696"}

Podwójne cudzysłowy dają znać, że Excel traktuje te liczby jak tekst, dlatego przy sprawdzaniu kryterium trzeba je najpierw zamienić z liczby na tekst. Najłatwiej to zrobić łącząc ją z pustym ciągiem znaków, czyli z dwoma podwójnymi cudzysłowami:

D2&""

przyrównanie tych wartości daje wynik w postaci tabeli wartości PRAWDA i FAŁSZ, w zależności, czy są takie same, czy różne.

Dalej jednak nie można skorzystać z funkcji SUMA.JEŻELI, gdyż nie przyjmie ona tablicy jako parametru. W takiej sytuacji można wykorzystać funkcję SUMA.ILOCZYNÓW. Najpierw przemnożyć tabelę prawd i fałszów przez wartości ilości, a następnie zsumować. Tylko zanim się do tego zabierzesz najpierw wartości PRAWDA i FAŁSZ trzeba zamienić na 1 i 0. Najprościej i najszybciej to osiągnąć stawiając przed tablicą dwa znaki minus.

=–(PRAWY(B2:B8;3)=D2&"")

Formuła zwróci wtedy ciąg wartości 1 i 0

{1;0;0;1;0;0;1}

1 — tam, gdzie była PRAWDA
2 — tam, gdzie był FAŁSZ

Końcowa funkcja będzie miała taki wygląd:

=SUMA.ILOCZYNÓW(A2:A8;–(PRAWY(B2:B8;3)=D2&""))

P.S.

Wpis na podstawie Excel Magic Trick 992

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.