Jak połączyć funkcje AND (ORAZ), OR (LUB) z funkcją ARRAYFORMULA?

Problem, który trafił się koledze. Nie chciał pokazać pliku źródłowego, więc musiałem sam zbadać na czym polega to, że ARRAYFORMULA – czyli funkcja przekształcająca zwykłe formuły w formuły tablicowe, nie chce działać zgodnie z przewidywaniami, gdy do środka zapakuje się funkcje OR, AND i im podobne (XOR). Znaczy się – nie generuje całej kolumny wyników.

Zobacz plik z przykładem.

Załóżmy, że używamy funkcji OR do wskazania liczb mniejszych od 10 lub większych od 50. Jak spełniają warunek to piszemy OK, a jak nie to WCALE NIE OK. Standardowo formuła tablicowa będzie wyglądać tak:

=ArrayFormula(if(or(A3:A<10;A3:A>50);"OK";"WCALE NIE OK"))

I nie zadziała dla całej kolumny A. Wyświetli tylko jedną wartość w pierwszej z komórek.

Jak sprawić by uzyskać porządany efekt (czyli obsłużenie całej kolumny)?

Musimy zastąpić funkcję logiczną działaniem.

Przypomnijmy sobie, że wartość TRUE (PRAWDA) odpowiada wartości 1, a wartość FALSE (FAŁSZ) odpowiada 0. I możemy je podstawiać do działań.

Jeśli wpiszemy w arkusz =TRUE+TRUE to uzyskamy wynik 2.

Jeśli wpiszemy w arkusz =(7<10)+(7>50) to uzyskamy wynik 1.

Podobnie możemy testować i dodawać np.

=(A2="BŁĄD")+(A2="")

I zapakować to w funkcję if, która powie co zrobić jeśli uzyskamy wartość 1 (w tym wypadku nie ma możliwośći uzyskania wartości 2, bo komórka A2 nie może być równoczesnie pusta i zawierać słowa błąd.

=if((A2="BŁĄD")+(A2="")=1;"zrób to co trzeba zrobić z pustymi lub błędnymi komórkami";"zrób co innego)

A teraz wystarczy, że wprowadzimy tu adresy i napiszemy

=ArrayFormula((A3:A<10)+(A3:A>50)) to uzyskamy ciąg wyników dodawania.

I możemy to też opakować w funkcję if.

Co zrobić z AND?

Tu działamy analogicznie tylko, że mnożymy warunki:

=true*true daje 1, ale

=true*false , =false*true ,=false*false dają 0

Dlaczego to trzeba obchodzić?

Dlatego, że połączenie ARRAYFORMULA i funkcji AND i OR sprawia, że sprawdzają one cały wskazany zakres.

Wpisanie =arrayformula(or(a3:a100>10)) sprawi, że przetestowany zostanie cały zakres A3:A100 i w tym wypadku jeśli pojawi się w tym zakresie choć jedna liczba większa od 10 to uzyskamy tylko wartość TRUE (PRAWDA) wpisaną w bieżącą komórkę.

Jak zrobić ciąg 100 komórek liczb losowych od 1 do 100?

Użyłem funkcji RANDARRAY, która generuje tablicę liczb losowych o zadanej wielkości (wiersze;kolumny). Połączonej z ARRAYFORMULA, mnożeniem i wyciąganiem części całkowitej (żeby nie mieć ułamków).

=ArrayFormula(int(randarray(100;1)*100))