Formuła w Excell - wyszukiwanie danych
marcin_cesarz - 23 Lut 2009 22:24
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.
adamas_nt - 23 Lut 2009 22:37
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.
marcin_cesarz - 23 Lut 2009 23:22
Odpowiedź jest zapisywana w tej samej bazie w kolumnie 1. Wyniki mogą być w tym samym arkuszu albo i innym
Załączam plik
adamas_nt - 23 Lut 2009 23:25
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?
marcin_cesarz - 23 Lut 2009 23:36
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
adamas_nt - 24 Lut 2009 00:01
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?
marcin_cesarz - 24 Lut 2009 00:16
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
adamas_nt - 24 Lut 2009 00:49
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.
marcin_cesarz - 24 Lut 2009 10:24
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)
adamas_nt - 24 Lut 2009 11:04
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".
marcin_cesarz - 24 Lut 2009 11:46
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(małpa)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ę?)
adamas_nt - 24 Lut 2009 12:39
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...
marcin_cesarz - 24 Lut 2009 16:55
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"
adamas_nt - 24 Lut 2009 17:35
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ć...
marcin_cesarz - 24 Lut 2009 18:00
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
adamas_nt - 24 Lut 2009 18:16
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...
marcin_cesarz - 24 Lut 2009 18:42
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(małpa)in.pl ale może być i wsparcie.d(małpa)ele.pl.
Baza to wynik tylko otrzymanych (czyli nie będzie tam żadnego wpisu jako użytkownika programu)
adamas_nt - 24 Lut 2009 19:00
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ół.
Perelka - 24 Lut 2009 19:21
Witam.
może przetestuj moja skróconą wersję skoroszytu bez pomocniczych kolumn. powprowadzaj dane jak Ci pasuje i oceń czy wszystko dobrze sprawdza.
marcin_cesarz - 24 Lut 2009 19:29
Perelka - A może wiesz jak W kolumnie D zsumować ile żółtych?
Perelka - 24 Lut 2009 19:41
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?
marcin_cesarz - 24 Lut 2009 22:44
No właśnie - ja pracuje na Excell 2003 i formuła
=_xlfn.IFERROR(SZUKAJ.TEKST("żółty";d2);"")
zwraca mi błąd #nazwa?
adamas_nt - 24 Lut 2009 22:50
>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))
marcin_cesarz - 24 Lut 2009 23:24
JEST MIODZIO
Wszystkim bardzo dziękuję
Perelka - 25 Lut 2009 12:50
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?
adamas_nt - 25 Lut 2009 13:23
>Perelka:
Listę znajdziesz w pomocy. Najszybciej klikając tu:
