EXCEL - Jak zastosować formułę dla 2 niezależnych warunków w 3 arkuszach?

Witam,

Poniżej zamieszczam opis problemu - nie wiem jak to zrobić, jak również czy powinna być formuła czy raczej skrypt - proszę o pomoc.

Informacja ogólna - w pliku są 3 zakładki: Arkusz1, Arkusz2, Arkusz3

----------
Arkusz1
----------
1) jest 26 pozycji z wartościami liczbowymi czytanymi poziomo
2) w kolumnie "D" dane się zmieniają (wartości jedynie rosną - nie maleją)

----------
Arkusz2
----------
1) tabela warunkowa (czytana oczywiście poziomo)
2) istotne jest to, że kolumny "C" (warunek 1) oraz "D" (warunek 2) są niezależne od siebie.
Przykład:
a) jeżeli zostanie spełniony tylko "warunek 1" - ma się pojawić napis "OK" w "Arkusz3"
b) jeżeli zostanie spełniony tylko "warunek 2" - ma się pojawić napis "OK" w "Arkusz3"
c) jeżeli zostaną spełnione (niezależnie od siebie) "oba warunki" - mają się pojawić 2 napisy "OK" w "Arkusz3"
3) w przyszłości dane w tabeli warunkowej mogą się zmienić

----------
Arkusz3
----------
1) w kolumnie "C" oraz "D" pojawia się napis "OK" (po spełnieniu warunku z tabeli warunkowej)
2) jeżeli pojawi się napis "OK" (po spełnieniu warunku), to nie może on już zniknąć


Podsumowując: tabela warunkowa (Arkusz2) sprawdza wszystkie 26 pozycji z "Arkusz1" i po spełnieniu warunku, umieszcza na "Arkusz3" (w odpowiedniej komórce) napis "OK".

Z góry dziękuję za pomoc.

Cytat:

jeżeli zostaną spełnione (niezależnie od siebie) "oba warunki"

Co masz na myśli mówiąc 'niezależnie od siebie'?

1) Czy wartości mają być uwzględniane "tak jak widać", czyli po uwzględnieniu zastosowanego formatowania, czy według faktycznej zawartości komórki?
Chodzi np. o komórkę Arkusz3!D9 w przykładowym pliku. Wg wartości Arkusz1!F9 warunek nie jest spełniony, ale byłby spełniony gdyby wartość była zaokrąglona do liczby całkowitej. W przykładzie podano, że warunek jest spełniony.
2) Co w przypadku gdy wartość z Arkusza1 nie mieści się w zakresie podanym w Arkuszu2? Np. Arkusz1!B23 = 2, a wartości w Arkuszu2 zaczynają się od 3.

W tym akapicie powtórzyłem to co napisałem wcześniej (nadmiar słów dla większej jasności) - cytat:

Przykład:
a) jeżeli zostanie spełniony tylko "warunek 1" - ma się pojawić napis "OK" w "Arkusz3"
b) jeżeli zostanie spełniony tylko "warunek 2" - ma się pojawić napis "OK" w "Arkusz3"
c) jeżeli zostaną spełnione (niezależnie od siebie) "oba warunki" - mają się pojawić 2 napisy "OK" w "Arkusz3"

Witam.
Cos w tym stylu?

[ Do Maciej Gonet ]

ad.1 Przepraszam, wkradły się gdzieniegdzie drobne błędy, ale to jest tylko przykładowy arkusz. Założenia są dosyć sztywne, tak więc trzymamy się sztywno tabeli warunkowej.

ad.2 Jeżeli wartość jest poza zakresem zdefiniowanym w tabeli warunkowej (np. 2) to w "Arkusz3" nie pojawia się napis "OK".

Na moje pierwsze pytanie nie dostałem zadowalającej odpowiedzi, pytałem o wartości w Arkuszu1, a nie w tabeli warunkowej. Wobec tego przyjąłem, że odnosimy się do faktyczych wartości zawartych w Arkuszu1 ignorując ewentualne formatowanie.
Propozycja rozwiązania w załączniku. W skoroszycie muszą być włączone obliczenia iteracyjne. Arkusz w wersji co najmniej 2007 ze względu na funkcję JEŻELI.BŁĄD. W Arkuszu3 jest zdefiniowana komórka o nazwie RESET. Gdy ta komórka ma wartość 0 komunikaty OK, który pojawiły się w tabelce są przechowywane niezależnie od przyszłych zmian w Arkuszu1. Gdy RESET ustawimy na wartość niezerową np. 1, komunikaty OK są aktualizowane wg bieżącego stanu Arkusza1.
Pozdrowienia.

[ Do Maciej Gonet ]

Wygląda na to, że rozwiązanie jest właściwe.

Mam jeszcze 2 pytania:

1) Czy istnieje metoda, która pozwala zamienić zawartość komórki "OK" (obecnie w komórce jest formuła) na postać "OK" (bez formuły). Wiem, że można wykonać polecenie "copy", a następnie "wklej specjalnie jako wartość", ale szukam innego rozwiązania, nie polegającego na "copy" i "wklej". Napisz proszę czy jest inna metoda zamiany komórki.

2) Czy jak mam już 2 x "OK" w obu komórkach, to czy można stworzyć formułę (która będzie działać), która zrobi taką np. rzecz: "jeżeli w obu komórkach "C5" i "D5" jest "OK" to wstaw w komórce "H5" napis "SUPER". Czy możesz wrzucić drobny przykład takiego efektu?

Dzień dobry jeszcze raz,
Ad 1) Można, tylko po co?
Ręcznie można oprócz metody Kopiuj/Wklej specjalnie/Wartość zastosować sekwencję F2+F9, czyli przywołać do edycji i zamienić formułę na wartość.
Można też automatycznie - napisać procedurę obsługi zdarzenia Change, która będzie nadzorować zakres komórek, i jeżeli pojawi się tam OK, zastępować wynik formuły stałą. Wtedy nie byłyby potrzebne odwołania cykliczne i zmienna RESET, ale arkusz stałby się arkuszem do jednorazowego użytku. Czy tak byłoby wygodniej? Raczej wątpię.
Ad 2) Można wstawić prostą formułę zwykłą:

lub tablicową (ctrl+shift+enter)

i przeciągnąć w dół.
Pozdrowienia.

Pośpieszyłem się z podziękowaniami

Wrzucam w załączniku plik "proba2A.xls" - jest to wyczyszczony z liczb "Arkusz1", jak również ustawiona jest komenda RESET na wartość "zero".

Gdy wpisuję w "Arkusz1" w komórkę "B5" pierwszą wartość np. 5 to w "Arkusz3" w komórce "D5" pojawia się napis "OK" mimo, że nie spełnia warunku opisanego w "tabeli warunkowej" (Arkusz2).

Czy można to poprawić, aby nie było takich przekłamań?

Dobry wieczór,
Gdy zaczyna Pan wpisywać dane do komórki B5 np. 5 to komórka F5 jest pusta, a to Excel interpretuje tak jakby zawierała 0, a więc warunek jest spełniony, stąd pojawia się OK. Dlatego w czasie wprowadzania początkowych danych zmienna RESET powinna być ustawiona na 1, aby tymczasowe wartości nie zostały zapamiętane. Dopiero gdy tabelka w Arkuszu1 będzie zawierać wszystkie dane początkowe należy ustawić RESET na 0 i od tego momentu rozpocząć "czuwanie" arkusza. Chyba, że z założenia komórki w kolumnie F mogą być puste i nie oznacza to zera, to wtedy do formuł trzeba by dodać dodatkowy warunek, ale takiego wariantu w przykładzie nie było, a były nawet wartości ujemne, więc nie wiem czy to jest potrzebne.
Pozdrowienia.

Tak, ma Pan rację - wytłumaczenie jest logiczne.
Wrzucając temat z przykładowym plikiem nie przewidziałem takiego efektu.

Mówiąc krótko - "Arkusz1" musi być pusty (bez liczb), a w "Arkusz3" raz spełniony warunek wrzuca napis "OK", który już zostaje w komórce na stałe (komenda RESET=0).

Czy mógłbym prosić o modyfikację według tych założeń?
Z góry dziękuję.

Dzień dobry,
Nie rozumiem co miałbym modyfikować. Tak jak napisałem, gdy arkusz jest czyszczony przed wprowadzeniem nowych danych trzeba ustawić RESET na wartość różną od 0 i wprowadzać dane nie przejmując się tym co się pojawia w Arkuszu3. Gdy wszystkie dane będą wprowadzone wtedy dopiero ustawić RESET na 0.
Czy chodzi o to, że już w stanie aktywności w Arkuszu1 mogą być puste komórki, które znaczą coś innego niż zero?
Pozdrowienia

napisałeś: "Czy chodzi o to, że już w stanie aktywności w Arkuszu1 mogą być puste komórki, które znaczą coś innego niż zero?"

Taka sytuacja również może mieć miejsce, a poza tym rozwiązanie ma być AUTOMATEM, gdzie nie musimy dodatkowo ingerować ręcznie. W przypadku większej ilości uruchomionych arkuszy o pomyłkę nie trudno.

Dalej albo ja nie rozumiem, o co Panu chodzi, albo Pan nie rozumie jak to może działać. Ten arkusz musi mieć dwie fazy działania: inicjację i czuwanie. Inicjacja polega na usunięciu starych wartości i wpisaniu nowych startowych. Nie da się wprowadzić wszystkich wartości jednocześnie, więc po to jest ta zmienna RESET, aby odróżnić fazę inicjacji od fazy czuwania. Gdyby zmienna RESET nie istniała lub miała stałą wartość arkusz nie mógłby działać, bo albo nie blokowałby tych komunikatów OK, albo nie dawałby się w ogóle zresetować.

Rozumiem, że to rozwiązanie ma swoje "ograniczenia i wymogi". Dziękuję za pomoc.

W takim razie wrócę do mojego pierwszego postu i spytam, czy powinna to być formuła czy raczej skrypt. Wygląda na to, że formuła ma swoje "wymogi" - nie może być AUTOMATEM.

Czy jest ktoś na forum, kto może napisać SKRYPT, który będzie AUTOMATEM?

Formuła jak i skrypt jest zawsze automatem - chodzi o to by dobrze zrozumieć problem.

Panie Macieju, (Chyba o to chodzi) Pan doda na wstępie swojej wynikowej formuły jeszcze jeden warunek: że jeżeli w arkuszu1 wartość w kolumnie B lub kolumnie D lub w kolumnie F jest pusta ("") niech wynik będzie pusty - nie wykona się sprawdzenie. (Muszą być wszystkie dane w konkretnym wierszu w arkuszu1 by zacząć sprawdzać te dwa warunki z wcześniejszych postów).
I "klient" będzie zadowolony

PS.
A na marginesie nigdy nie odważyłem się w danej formule odwoływać się do komórki w której jest ona zapisana. Dziwi mnie że tu excel nie zgłasza odwołań cyklicznych.

Dodane po chwili
------------------------------------------------
Właściwie można to zrobić jeszcze inaczej - pojedynczym ORAZ.
Pozwoliłem sobie troszeczkę zmienić formułę pana Macieja "zabezpieczając" ją przed pustą komórką.

Czy o to chodziło autorowi topiku?


Mam nadzieje że w kolumnie D nie pojawią się litery (bo Autor o tym nie
wspomniał). - wtedy też trzeba by było się przed tym zabezpieczyć.

Sprawdziłem zmodyfikowaną formułę i jak na razie nie ma przekłamań.

Ps. też na to wpadłem i wpisałem na próbę literę (nastąpiło przekłamanie). Czy może Pan to jakoś zabezpieczyć i wrzucić plik dla "potomnych"

Dodano po 32 [minuty]:

Jest jeszcze potrzeba zabezpieczenia przed wartością ujemną (jest przekłamanie).
Wartość ujemna nie spełnia warunku.

Dzień dobry,
Dodałem zabezpieczenie przed liczbami ujemnymi w kolumnie F i literami w kolumnie D w Arkuszu1, zabezpieczenie przed pustymi komórkami dopisał już wcześniej kolega. Natomiast 0 wpisane jawnie w kolumnie D będzie spełniać warunek (tak ma być?).
Ale w dalszym ciągu, żeby skasować ustawienia OK w Arkuszu3 trzeba na chwilę ustawić RESET na wartość <>0. Tego nie da się obejść, chyba że przez usunięcie formuł i wstawienie ich na nowo.
Panie Marku,
Ta blokada wartości OK może działać tylko dzięki odwołaniom cyklicznym. Żeby Excel się nie buntował, trzeba w opcjach włączyć obliczenia iteracyjne. W przeciwnym razie oczywiście będzie sygnalizacja odwołania cyklicznego.
Pozdrowienia

Maciej Gonet napisał:

Ta blokada wartości OK może działać tylko dzięki odwołaniom cyklicznym. Żeby Excel się nie buntował, trzeba w opcjach włączyć obliczenia iteracyjne. W przeciwnym razie oczywiście będzie sygnalizacja odwołania cyklicznego.

Dzięki za informację. Nigdy wcześniej nie wpadłem na to by w taki sposób to wykorzystać. Pochylam głowę przed mistrzem w wyrazie uznania (niestety nie ma takiego emotikona).

Dziękuję za pomoc. Zamykam temat.