Dostałem zapytanie jak wylosować 2 pozycje z 5 elementów bez powtórzeń. Znalazłem 2 rozwiązania (oba będą działać też dla innej ilości elementów). Oba na początek korzystając z funkcji LOS
Jak wylosować pozycje z listy bez powtórzeń — widzowie #8
Chodzi o to, żeby obok listy elementów ustawić wynik funkcji LOS, czyli losową liczbę od 0 do 1.
Przykładowe wyniki funkcji LOS dla listy 5 elementów
Teraz w pierwszym rozwiązaniu kopiujesz wynik i sortujesz go po wielkości liczb (czy rosnąco czy malejąco nie ma znaczenia). Uzyskujesz listę elementów ustawioną losowo.
Rozwiązanie 1 — posortowane elementy przy czym wyniki funkcji LOS już się zmieniły
Teraz żeby wybrać 2 elementy korzystasz z funkcji INDEKS, której podajesz zakres listy i numery wierszy, które chcesz pobrać:
=INDEKS($E$2:$E$6; WIERSZ(A1))
Rozwiązanie 1 — Wynik z funkcją INDEKS i WIERSZ
Numer wiersza możesz podać tak jak tu w przykładzie odwołując się do pierwszej komórki (później będzie się ono zmieniać wraz z przeciąganiem formuły w dół).
Drugie rozwiązanie wykorzystuje trudniejszą formułę, ale za to jest dynamiczne, czyli zmienia się wraz ze zmianą wyników funkcji LOS (pierwsze jest stabilne dopóki ponownie nie posortujesz listy).
Zaczynamy od znalezienia k‑tego największego elementu za pomocą funkcji MAX.K, czyli najpierw 1 największy, potem 2 itd.
=MAX.K($A$2:$A$6;WIERSZ(A1))
Ponownie korzystam z odwołania do wiersza by dynamicznie wybierać elementu po kolei.
Następnie trzeba znaleźć pozycję tego elementu, czyli funkcja PODAJ.POZYCJĘ będzie potrzebna. 1 argument to znaleziona k‑ta największa wartość, 2 to zakres przeszukiwania, a 3 mówi o tym, że dopasowanie ma być dokładne (ustawiony na 0).
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Jak zliczyć wystąpienia 47 w komórkach, którą są i tekstem i liczbami?
Dostałem zapytanie żeby zliczyć komórki, gdzie wystąpiła liczba 47. Dane w komórkach mogą wyglądać tak:
Licz wystąpienia w komórkach, nie ważne czy tekst czy liczba — widzowie #7
Przykładowe dane
Czyli czasami 47 jest sama, czasami na początku tekstu, a czasem w środku. Jak w takim razie to policzyć, skoro funkcja LICZ.JEŻELI nie zadziała.
Postanowiłem wykorzystać funkcję SZUKAJ.TEKST. Początkowo myślałem, że będę musiał zapisać daną jako tekst, czyli "47" (47 w cudzysłowach), ale okazało się to zbędne. Funkcja SZUKAJ.TEKST odnajdywała 47 nie ważne jak była traktowana przez Excel. Czy komórka była wyrównana do prawej i oznaczała liczbę, czy do lewej i oznaczała tekst.
Teraz miałem liczby, gdy SZUKAJ.TEKST znalazło 47 i błąd #ARG! gdy nie znalazło
{1;1;#ARG!;5}
Potrzebowałem wiedzieć tylko czy liczba występuje, dlatego wykorzystałem funkcję CZY.LICZBA. Teraz miałem wartości PRAWDA i FAŁSZ :
{PRAWDA;PRAWDA;FAŁSZ;PRAWDA}
Niestety tych wartości nie da się łatwo zsumować. Trzeba je zamienić na 1 i 0 przez prostą operację matematyczną np: 2 minusy przed funkcją CZY.LICZBA.
Jak uzyskałem 1 i 0 wystarczyło je zsumować, czyli końcowa formuła wygląda tak:
=SUMA(–CZY.LICZBA(SZUKAJ.TEKST(D5;B1:B4)))
Pamiętaj ją zatwierdzić Ctrl + Shift + Enter , bo to formuła tablicowa.
Wynik działania formuły
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Jak zamienić wybraną liczbę, ale tylko po 3 myślniku?
Dziś chcemy zamieć 2 cyfrową liczbę znajdującą się po 2 myślnik . Ponieważ Mike Girvin, ani ja nie znamy sposoby, by w Excelu znaleźć 3 wystąpienie danego znaku do tego problemu trzeba podejść w inny sposób. W Excelu jest funkcja, która umożliwia podmianę konkretnego wystąpienia danego znaku.
Zamieniamy liczbę 11, na 13, ale tylko po 3 myślniku (wskazany na niebiosko)
Zamiana liczby po 3 myślniku sztuczki #26
Wystarczy, że zamienimy ten 3 myślnik na znak, który na pewno nie wystąpi w szukanej komórce np:
=PODSTAW(A5;"-";"#";3)
Podstawiany unikatowy znak za 3 myślnik
Teraz mając tak podmieniony znak możemy znaleźć jego dokładną pozycję, a dokładniej chodzi nam o pozycję o 1 znak dalej, dlatego to wyniku dodamy jeszcze 1.
=SZUKAJ.TEKST("#";PODSTAW(A5;"-";"#";3))+1
Znajdujemy pozycję 3 myślnika + 1 przez podstawiony znak
następnie chcemy sprawdzić czy 2 cyfrowa liczba znajdująca się w znalezionym miejscu jest liczbą którą chcemy zmienić, więc potrzebujemy ją wyciąć
uzyskany wynik jest liczbą traktowaną jak tekst i za nim go przyrównamy potrzeba zamienić naszą liczbę-warunek na tekst łącząc go z pustym ciągiem znaków
$J$1&""
Sprawdzamy, czy po 3 myślniku jest szukana wartość
Jeśli liczba się zgadza to należy dokonać zastąpienia,
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Jak znaleźć największą wartość znajdującą się po wybranym fragmencie tekstu?
Jeśli chcesz znaleźć komórkę gdzie po wyszukiwanym fragmencie tekstu jest największa liczba potrzebujesz wykonać kilka kroków.
Znajdź największą liczbę za fragmentem tekstu — sztuczki #25
Na czerwono szukany tekst, a na zielono wynik końcowego wyszukania
Najpierw trzeba znaleźć wszystkie wystąpienia szukanego fragmentu tekstu — funkcja SZUKAJ.TEKST, niestety zwraca ona błąd #ARG! jeśli nie znajdzie fragmentu. W naszych obliczeniach będzie to stanowić problem, więc należy dodatkowo wykorzystać funkcję CZY.LICZBA, żeby zamienić wyniki na wartości logiczne PRAWDA (gdy znalazł fragment) i FAŁSZ (gdy nie znalazł)
=CZY.LICZBA(SZUKAJ.TEKST(D2;A2:A10))
W naszym przykładzie mamy zawsze liczbę 2 cyfrową po prawej stronie, więc wyciągamy ją funkcją
=PRAWY(A2:A10;2)
Wynik działania funkcji PRAWY — znajduje liczby i traktuje je jak tekst
Teraz wystarczy zwykłe mnożenie matematyczne, by zamienić wartości logiczne i liczby traktowane jako tekst, na liczby tam gdzie są fragmenty szukanego tekstu i zera gdzie nie ma.
a później szukamy jej pozycji, czyli funkcja PODAJ.POZYCJĘ wykorzystująca jeszcze raz wcześniejsze obliczenia i pamiętając, że szukamy dokładnego porównania (zero jako ostatni argument):
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Jak znaleźć wartość skojarzoną z ostatnim wystąpieniem fragmentu tekstu?
Jeśli chcesz wyszukać fragment tekstu w komórce korzystasz z funkcji SZUKAJ.TEKST Zwraca ona pozycję miejsce, gdzie znalazła początek szukanego tekstu (pozycję pierwszego znaku) albo jeśli nie znajdzie fragmentu błąd — #ARG!
Szukamy wartości w zielonym na podstawie tekstu w czerwonym
Wyszukaj ostatni fragmentu tekstu za pomocą WYSZUKAJ i SZUKAJ.TEKST — sztuczki #24
Jeśli wykorzystasz funkcję SZUKAJ.TEKST na kolumnie z tablicy dostaniesz ciąg wyników {#ARG!;#ARG!;#ARG!;1;1;1;1;#ARG!;#ARG!} ostatnie miejsce, gdzie występuje liczba jest pozycją szukaną przez nas.
=SZUKAJ.TEKST(D2;A2:A10)
Możemy do tego wykorzystać funkcję WYSZUKAJ. Należy przy tym pamiętać, że działa ona zawsze na zasadzie przybliżenia, czyli jeśli ustawie wyszukiwany parametr na 2 do potęgi 15 uzyskam wynik (32 768) większy od największej ilości znaków jakie może znaleźć się w komórce.
Tym samym przybliżone wyszukiwanie zwróci nam ostatnią pozycję gdzie była liczba:
Warto jeszcze zaznaczyć wektor wynikowy, żeby dostać od razu pożądany wynik.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
