Excel – Liczenie i sumowanie po fragmencie tekstu (komórki)

Piotr Majcher Excel 10 Comments

W tym odcinku pokazuję jak liczyć oraz sumować warunkowo w sytuacji gdy kryterium dotyczy fragmentu komórki (zaczyna się od, kończy się na, zawiera).

Kryterium można także zdefiniować określając pozycję znaku lub fragmentu tekstu w komórce na przykład komórki, w których drugim znakiem jest „Y” albo trzecim od końca jest „X”.

Aby Excel mógł brać pod uwagę fragment tekstu/wartości można wykorzystać symbole wieloznaczne. Excel obsługuje dwa: gwiazdkę (*) i znak zapytania (?).

Symbole wieloznaczne mogą być wykorzystane także w innych popularnych funkcjach Excela ale miej na uwadze że tylko w niektórych…

Użyta/e funkcja/e: LICZ.JEŻELI, SUMA.JEŻELI

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-869 – Liczenie i sumowanie po fragmencie tekstu (komórki).xlsx

 

Comments 10

  1. Witam, mam prosbe.. potrzebuje pomocy w napisaniu formuly tyle , ze nie mam pomyslu jak to zrobic. Chodzi mi glownie o to, aby po spelnieniu dwoch warunkow formula zaczela wstawiac okreslona wartosc. A najlepiej jesli moznaby polaczyc dwa razy funkcje „vlookup”. Cos typu, szukaj pionowo X w tablicy Y i jesli znajdziesz X wstaw wartosc z kolumny Z, tyle za ja potrzebuje: szukaj pionowo X1 i X2 w tablicy Y i jesli znajdziesz obie wartosci (czyli X1 i X2 z tego samego wiersza beda spelnione) to z tablicy Y ma wstawic wartosc z kolumny Z. Mam nadzieje , ze to co napisalem jest zrozumiale. No chyba , ze wlasnie zle mysle, dlatego nie potrafie napisac tej formuly :/

    1. Post
      Author

      Jeśli nie widzę jaki układ mają dane (jak są zorganizowane) to ciężko cokolwiek pomóc. Podeślij plik z przykładowymi danymi, dokładnie opisz gdzie mają być formuły lub formuła, wpisz oczekiwane wyniki żebym wiedział do czego zmierzamy i opisz z czego te wartości wynikają. Może wówczas będę w stanie pomóc. Jeśli nie to proszę bardzo:
      https://www.youtube.com/playlist?list=PLfGfuCZ0S4hWBe_D-5LpG2rFZkVQiBAiO
      Po obejrzeniu ze zrozumieniem wszystkich filmów z tej playlisty zrobisz wyszukiwania jakiekolwiek sobie wyśnisz!

  2. Dzień dobry.

    Czy zna Pan sposób na przeszukanie w excelu jakiegoś obszaru (powiedzmy obszar = 5 wierszy) i w przypadku znalezienie w tym obszarze niepustych komórek (dowolnej ilości z tych 5) zwrócenie wartości tych niepustych komórek w formie tekstowej do jednej komórki?

    Pozdrawiam.
    Daniel

  3. Witam,
    mam problem z formułą, która pozwoliłaby mi zsumować liczbę zadań, ale z pominięciem zadań zdublowanych (ten sam tekst w komórkach). Mam tabele excela gdzie prowadzony jest tygodniowy przeglad zadań zespołu. Na koniec tygodnia mamy sume wszystkich przypisanych zadań i sume zadań wykonannych. Problem pojawia się kiedy kopiujemy jakies zadanie na inny dzień ze wzgledy na to, że zadanie nie zostalo wykonane, formuła którą stosuję wlicza to zadanie do sumy zadań do wykonania, a nie powinno bo zadanie jest nadal jedno…Potrzebuje formuły, która nie liczyłaby zdublowanych pól tekstowych..Czy mógłby mi Pan pomóc w tym temacie.

  4. Witam.
    Mam pytanie czy jest możliwość aby wyszukać np po fragmencie tekstu ale abym nie musiał w formule wpisywać np „DELL” tylko aby odwołać się do komórki która ma wpisany taki fragment tekstu.
    Pozdrawiam
    Staszek

    1. Post
      Author
  5. Dzień dobry!

    Nie mogę sobie poradzić z jedną małą rzeczą… Stosuję SUMA.JEŻELI dla sprawdzenia kilku warunków:

    =SUMA.WARUNKÓW(Koszty!E:E;Koszty!K:K;”Tak”;Koszty!I:I;”nazwa_1*”;Koszty!M:M;C1966)

    gdzie sumuję wartości z kolumny E z arkusza Koszty, jeśli w arkuszu o nazwie Koszty w kolumnie K jest warunek „Tak”, w kolumnie I jest warunek zaczynający się od „nazwa_1*” i w kolumnie M jest wartość tekstowa z arkusza bieżącego. Wszystko działa poprawnie od lat 🙂 Teraz jednak mam potrzebę sprawdzenia dwóch wartości w kolumnie I. Inaczej – zsumować jeśli w kolumnie I jest tekst zaczynający się od „nazwa_1*” albo od „nazwa_2*”. Nie zastąpię „nazwa_1*” i „nazwa_2*” częścią wspólną „nazwa*” bo to tylko przykładowe wartości (rzeczywiste dane nie mają wspólnej części), poza tym wtedy by mi też sumowało „nazwa_3*” itd. a tego nie chcę. Na piechotę można zrobić po prostu sumę tych dwóch formuł z początku mojego wpisu – ale brzydko mi to wygląda. Trochę próbowałem szperać w sieci i zastosować złapanie tych dwóch wartości w klamry jak niżej:
    =SUMA.WARUNKÓW(Koszty!E:E;Koszty!K:K;”Tak”;Koszty!I:I;{„nazwa_1*”;”nazwa_2*”};Koszty!M:M;C1966)
    ale tak zbudowana formuła sprawdza tylko pierwszą z dwóch wartości i jak jest nazwa_1 to sumuje a nazwa_2 już nie – i na odwrót jeśli zmienię kolejność w klamrach. Jak ją zapisać, aby sprawdziła cały zbiór?

    1. Post
      Author
  6. Odpowiedź na zagadkę:
    1. Funkcja zwróci liczbę firm, których nazwa ma 4 znaki
    2. Funkcja zwróci liczbę firm, których nazwa ma 5 bądź więcej znaków

    Czy udało mi się rozwiązać zagadkę? 😉

    1. Post
      Author

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *