Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Formuła w Excell - wyszukiwanie danych

23 Lut 2009 21:24 4553 25
  • Poziom 9  
    Będę wdzięczny jeżeli ktoś wie :)

    Mam bazę danych wyciąganą z Outlooka. Dane zawierają
    1) nadawcę wiadomości -kolumna 1,
    2) temat wiadomości - kolumna 2
    3) Datę otrzymania wiadomości - kolumna 3
    4) Kategoria wiadomości (np"Kategoria Żółty") - kolumna 4

    Poszukuje formuły, która będzie w stanie określić na które wpisy z bazy nie otrzymano odpowiedzi.

    Schemat wygląda tak:
    a) otrzymuję maila
    b) jeżeli nie znam odpowiedzi oznaczam go kategorią żółty (kolumna 4) i wysyłam do innej osoby celem konsultacji
    c) otrzymuję od niej odpowiedź (bądź nie)

    Próbowałem z formułą wyszukaj pionowo ale coś mi nie działa :)
    Będę wdzięczny za pomoc.

    :D
  • Pomocny post
    Moderator Programowanie
    Nie wiemy gdzie zapisywana jest informacja: odpowiedź (podpunkt 'c')
    Gdzie umieszczone mają być wyniki? W osobnym arkuszu?
    Najlepiej wrzuć jakiś przykład z fikcyjnymi (ustawa) danymi, ale w układzie jaki masz w arkuszu. Zaznacz, w którym miejscu masz błędy.
  • Poziom 9  
    Odpowiedź jest zapisywana w tej samej bazie w kolumnie 1. Wyniki mogą być w tym samym arkuszu albo i innym :)

    Załączam plik
  • Pomocny post
    Moderator Programowanie
    No, ale te punkty za transfer...
    Czyli: Jeżeli w kolumnie D tekst zawiera wyraz: "Żółty" i w kolumnie E jest w tym wierszu pusta komórka, to wyświetlać komunikat o braku odpowiedzi ew. formatowanie warunkowe w 1-wszej kolumnie?
  • Poziom 9  
    No właśnie nie :)

    Chodzi o to aby w kolumnie E wyświetlała się odpowiedź na pytanie " czy odpowiedziano"

    W pliku odpowiedziano to
    Adam Kowalski - błąd podczas tworzenia bo poniżej jest Czesław Miś - RE: Błąd podczas tworzenia

    A nie odpowiedziano na
    Grzegorz Rydz - Nie wiem jak bo poniżej nie ma takiej wiadomości :)

    Za punkty Sorry :)
  • Pomocny post
    Moderator Programowanie
    Pytanie dodatkowe:
    Czy jeżeli dostaniesz odpowiedź tekst w kolumnie 'D' jest zmieniany? Bo w przypadku Adama Kowalskiego nadal jest "Kategoria Żółty"
    Może właśnie temat zrobić kluczem? Jeżeli tekst tematu wiadomości rozpoczyna się do "RE:" to ten sam tekst bez "RE:" będzie pytaniem, na które już odpowiedziano?
  • Poziom 9  
    Nie jest zmieniany (Kategoria żółty jest na stałe - oznacza, że właśnie te rekordy mnie interesują czy jest na nie odpowiedź)

    Co do RE "Też nad tym myślałem ale :)

    Pytanie to treść wiadomości bez RE (ale możliwe i z RE) np. dostajesz pytanie od Olgierd Łukaszenko (wiersz 22) odpisujesz mu, on odpisuje Ci zadając nowe pytanie (RE wiersz 10) Wysyłasz je dalej (bo nie znasz odpowiedzi (kategoria żółty) i czekasz na odpowiedź (wiersz 23)

    Odpowiedź na pytanie to
    np. wiersz 14, 15, 19 (zaczynają się od RE, ODP, FW)

    Mam nadzieję, że chociaż trochę jaśniej :)
  • Pomocny post
    Moderator Programowanie
    W załączniku rozwiązanie dla "RE: " (zastosowałem kolumnę pomocniczą). W dalszym ciągu należy użyć funkcji: LUB dla "ODP: " i "FW: ", ale to już nie dzisiaj. A może sam już sobie poradzisz...

    Edit:
    Dzięki za pkt. nie spodziewałem się...

    Edit2:
    Myślę, że prościej będzie zrobić dodatkowe kolumny dla dalszych obliczeń, przesunąć je w prawo wgłąb arkusza i w kolumnie E połączyć tylko wyniki. W przeciwnym wypadku zrobi nam się niezwykle długa formuła...
    Można też policzyć długość ciągu, w którym nie ma "RE" lub "ODP" lub "FW" i sprawdzić na tej podstawie pozostałe funkcją WYSZUKAJ.PIONOWO od prawej strony... Ale to jutro.
  • Poziom 9  
    Chyba sobie sam nie poradzę. Nie spodziewałem się, że ta formuła będzie tak długa :)

    Co do liczby kolumn to nie ma problemu

    Mam jeszcze kilka pytań:)
    a) podana przez Ciebie formuła nie "zadziałała" przy wierszu 9 (pytanie) odpowiedź jest w wierszu 18
    b) czy można zmodyfikować tak formułę aby wyszukiwanie odbywało się tylko do rekordów oznaczonych jako kategoria żółty? - bo teraz oznaczone jest jako "tAK" rekord 22 - a tymczasem ten rekord nie powinien podlegać formule (a rekord w wierszu 10 - oznaczony na żółto tak)
  • Pomocny post
    Moderator Programowanie
    ad a. Trzeba postawić inny warunek.
    ad b. nie ma problemu.
    Pytanie:
    Zapis w wierszu 23:
    -jest to Twoja odpowiedź, na którą może być nst. pytanie?
    W tym wypadku w A23 będzie Twój adres i w D23 tekst: Kategoria Żółty"?

    W B18 na końcu jest spacja (usunąłem ją), czy to przypadek?

    Edit:
    Tak "na roboczo".
  • Poziom 9  
    Trochę to zamotane ale chodzi o to, że :)
    1) otrzymuje pytanie od Olgierd Łukaszenko (temat: Pytanie) - wiersz 22
    2) Odpowiadam
    3) Olgierd Łukaszenko ma wątpliwośc co do odpowiedzi - (temat: RE: Pytanie) wiersz 10
    4) Nie znam odpowiedzi - wysyłam do konsultacji (oznaczenie na kategoria żółty)
    5) Przychodzi odpowiedź od wsparcie.c(malpa)in.pl - warunek jest spełniony

    Czyli w wierszu nr 23 nie ma być oznaczenia Kategoria żółty

    Co do spacji to przypadek (będę formatował komórki przez formułę usunięcia zbędnych spacji)


    PS. w kolumnie "i" sprawdzenie czy pierwsze 10 znaków = "wsparcie.c". Zmień na inny, jeśli trzeba

    Teraz ja mam pytanie? Kolumna I zawiera dane nadawcy (jeżeli miałbym listę takich osób w innym arkuszu od których oczekuję odpowiedzi to czy to ułatwi sprawę?)
  • Pomocny post
    Moderator Programowanie
    Nie. Żeby wszystko działało muszę być ustalone, sztywne zasady. Im więcej warunków i danych do sprawdzenia, tym większe prawdopodobieństwo błędów.

    W wierszu 23 chodzi o to, co opisujesz w p.4
    Przyjąłem, że nadawcą pytania jesteś Ty. Temat rozpoczyna się od "RE: ", oznaczasz go jako "Kategoria Żółty" i jeśli pojawi się odpowiedź z identycznym tekstem w temacie - zadziała formuła.
    Wydaje mi się, że należałoby jeszcze sprawdzać daty. W przypadku dłuższej serii pytanie-odpowiedź-pytanie-(...) z identycznym tekstem tematu Excel uzna, że odpowiedź już nastąpiła (znajdzie wcześniejszy rekord odpowiadający warunkom formuły) Przetestuj i daj znać, pojawię się po południu...
  • Poziom 9  
    Ja przyjąłem inaczej, iż nadawcą pytania jest nadal człowiek z wiersza nr 10 i to obok jego nazwiska postawię "Kategoria Żółty"

    Z datami dobry pomysł - rozumiem, że ma się opierać na założeniu że data odpowiedzi ma mieć wartość większą niż pytanie.

    PS. Wkleiłem xls Coś mi nie działa do końca prawidłowo :)

    PPS. Jak ustawić prawidłowo tą prowizję za plik? Wybieram opcję "Rezygnuję z własnej prowizji"
  • Pomocny post
    Moderator Programowanie
    Ad P.S. Tekstowy format komórki

    Wymyśliłem coś takiego:
    Przyjąłem, że kolumna C zawsze będzie posortowana malejąco (od najwcześniejszej daty). W związku z tym:
    a) odpowiedź zawsze będzie niżej w arkuszu niż pytanie (z reguły pytanie jest wcześniej niż odpowiedź :-)) i tu odpada nam problem serii pytań i odpowiedzi z identycznym tematem i sprawdzanie dat.
    b) aby punkt a) mógł funkcjonować: w kol. F, G, H, I zmieniłem notację zakresów na względną. Funkcja WYSZUKAJ.PIONOWO będzie działać na zakresie od wiersza stanowiącego kryteria wyszukiwania +1 w dół. Pozostaje Ci tylko ustalić granicę (w przykładzie 100 wierszy).


    W dalszym ciągu w przykładzie przyjmuję, że adres w A23 to użytkownik arkusza.

    Teraz pewnie można by trochę to wszystko uprościć...
  • Poziom 9  
    Jest prawie super :)

    Tylko, że nie za bardzo Cię rozumiem co oznacza "przyjmuję, że adres w A23 to użytkownik arkusza"
    Ja traktuję pozycję 23 jak każdą inną. Dlatego wyszukiwanie powinno wskazać tak (w pozycji E10) bo odpowiedź jest w wierszu 23 na pytanie zadane w wierszu 10 :)

    I jeszcze jedno to pewnie będzie dla Ciebie pestką :)

    W kolumnie D chciałbym zsumować ile żółtych (pewnie funkcja licz.jeżeli + wyszukaj.tekst) ale składnia mi nie wychodzi :(
  • Pomocny post
    Moderator Programowanie
    Chodziło mi o coś takiego:
    Rekord zawierający w temacie "RE:" może być pytaniem oczekującym na odpowiedź tylko wtedy, jeśli został wysłany przeze mnie, jako użytkownika programu (w przykładzie pierwsze 10 znaków adresu e-mail) w każdym innym przypadku jest odpowiedzią (także moją).
    Decyduje o tym kolumna D i tekst: "Kategoria Żółta" i w tym momencie, masz rację, to wszystko jedno... Ale wykluczamy pomyłkę.

    Edit:
    Na sumowanie nie mam pomysłu. "Moja" standardowa formuła:
    =LICZ.JEŻELI(D2:D23;(WYSZUKAJ("Żółty";D2:D23)))
    nie działa w wierszach gdzie jest więcej kategorii. Zwraca 8 zamiast 10...
  • Poziom 9  
    Tylko, że w chwili obecnej w wierszu nr 10 brak jest spełnienia warunku - brak wyniku "tak" :(
    Dla mnie to za trudne :(

    Adres e-mail, który jest w przykładzie może być jedynie jednym z wielu (chodzi o to, że odpowiedź może przyjść nie skrzynki imiennej (wtedy ADAM KOWALSKI) ale z grupowych (nie mają czasem nazwy) stąd też wsparcie.c(malpa)in.pl ale może być i wsparcie.d(malpa)ele.pl.

    Baza to wynik tylko otrzymanych (czyli nie będzie tam żadnego wpisu jako użytkownika programu)
  • Pomocny post
    Moderator Programowanie
    Jeżeli przyjmiemy ostatnią opcję: Odpowiedź musi być niżej w arkuszu niż pytanie i rezygnujemy za sprawdzania adresu e-mail, to w i2 wklej:
    =JEŻELI(CZY.BŁĄD(JEŻELI(J2=WYSZUKAJ.PIONOWO(J2;B3:B103;1;0);"tak";""));"";JEŻELI(J2=WYSZUKAJ.PIONOWO(J2;B3:B103;1;0);"tak";""))
    i przeciągnij w dół.
  • Pomocny post
    Poziom 15  
    Witam.
    może przetestuj moja skróconą wersję skoroszytu bez pomocniczych kolumn. powprowadzaj dane jak Ci pasuje i oceń czy wszystko dobrze sprawdza.
  • Poziom 9  
    Perelka - A może wiesz jak W kolumnie D zsumować ile żółtych?
  • Pomocny post
    Poziom 15  
    Właśnie nad tym pracuję...

    Oto wynik pracy.

    Tylko jakiego masz Office?

    Bo ja robię to w 2007 i jak zapisuję w trybie zgodności to mam info że nie wszystkie formuły będą prawidłowo odczytywane...


    A tak przy okazji... jak reszta? Działa prawidłowo?
  • Poziom 9  
    No właśnie - ja pracuje na Excell 2003 i formuła
    =_xlfn.IFERROR(SZUKAJ.TEKST("żółty";d2);"")
    zwraca mi błąd #nazwa?
  • Pomocny post
    Moderator Programowanie
    >Perelka: zamień JEŻELI.BŁĄD na JEŻELI(CZY.BŁĄD i przy zapisywaniu w trybie zgodności powinno być OK.

    >marcin_cesarz:
    W arkuszu kol. Perelka w F2 wklej i przeciągnij w dół:
    =JEŻELI(CZY.BŁĄD(SZUKAJ.TEKST("żółty";D2));"";SZUKAJ.TEKST("żółty";D2))
  • Poziom 9  
    JEST MIODZIO :)
    Wszystkim bardzo dziękuję
  • Poziom 15  
    adamas_nt - dzięki za podpowiedź, nie mam nigdzie zainstalowanego Office 2003 więc nie pamiętam jakich formuł tam nie ma. Informacja o niezgodności pokazuje tylko obszar formuł które nie są zgodne a nie konkretne funkcje...

    Ciekawe czy jest gdzieś jakaś lista wszystkich funkcji z podziałem na występowanie w kolejnych wersjach Office?
  • Pomocny post
    Moderator Programowanie
    >Perelka:
    Listę znajdziesz w pomocy. Najszybciej klikając tu:
    Formuła w Excell - wyszukiwanie danych