Jak wylosować pozycje z listy bez powtórzeń — widzowie #8

Jak wylosować pozycje z listy bez powtórzeń?

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

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

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

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).

=PODAJ.POZYCJĘ(MAX.K($A$2:$A$6;WIERSZ(A1));$A$2:$A$6;0)

Teraz wystarczy wykorzystać funkcję INDEKS podobnie jak w 1 przykładzie i uzyskujemy formułę:

=INDEKS($B$2:$B$6;PODAJ.POZYCJĘ(MAX.K($A$2:$A$6;WIERSZ(A1));$A$2:$A$6;0))

Rozwiązanie 2 - Wynik i formułą

Rozwiązanie 2 — Wynik i formułą

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Jak wylosować pozycje z listy bez powtórzeń — widzowie #8

Licz wystąpienia w komórkach, nie ważne czy tekst czy liczba — widzowie #7

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

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

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Licz wystąpienia w komórkach, nie ważne czy tekst czy liczba — widzowie #7

Zamiana liczby po 3 myślniku sztuczki #26

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)

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

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

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ąć

=FRAGMENT.TEKSTU(A5;SZUKAJ.TEKST("#";PODSTAW(A5;"-";"#";3))+1;2)

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ść

Sprawdzamy, czy po 3 myślniku jest szukana wartość

Jeśli liczba się zgadza to należy dokonać zastąpienia,

=ZASTĄP(A5;SZUKAJ.TEKST("#";PODSTAW(A5;"-";"#";3))+1;2;$J$2)

a jeśli nie to należy podać stary kod, daje to w sumie warunki do funkcji JEŻELI, co całościowo daje formułę:

=JEŻELI(FRAGMENT.TEKSTU(A5;SZUKAJ.TEKST("#";PODSTAW(A5;"-";"#";3))+1;2)=$J$1&"";ZASTĄP(A5;SZUKAJ.TEKST("#";PODSTAW(A5;"-";"#";3))+1;2;$J$2);A5)

Wynik końcowy działania formuły

Wynik końcowy działania formuły

P.S.

Wpis na podstawie Excel Magic Trick 1002

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Zamiana liczby po 3 myślniku sztuczki #26

Znajdź największą liczbę za fragmentem tekstu — sztuczki #25

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

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

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.

=CZY.LICZBA(SZUKAJ.TEKST(D2;A2:A10))*PRAWY(A2:A10;2)

Wynik działania przemnożenia funkcji PRAWY i SZUKAJ.TEKST

Wynik działania przemnożenia funkcji PRAWY i SZUKAJ.TEKST

Następnie wyciągamy maksymalną liczbę

=MAX(CZY.LICZBA(SZUKAJ.TEKST(D2;A2:A10))*PRAWY(A2:A10;2))

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):

=PODAJ.POZYCJĘ(MAX(CZY.LICZBA(SZUKAJ.TEKST(D2;A2:A10))*PRAWY(A2:A10;2));SZUKAJ.TEKST(D2;A2:A10)*PRAWY(A2:A10;2);0)

teraz już można wyciągnąć szukaną wartość za pomocą formuły

=INDEKS(B2:B10;PODAJ.POZYCJĘ(MAX(CZY.LICZBA(SZUKAJ.TEKST(D2;A2:A10))*PRAWY(A2:A10;2));SZUKAJ.TEKST(D2;A2:A10)*PRAWY(A2:A10;2);0))

Pamiętaj, żeby zatwierdzić ją Ctrl + Shift + Enter

P.S.

Wpis na podstawie Excel Magic Trick 1001

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Znajdź największą liczbę za fragmentem tekstu — sztuczki #25

Wyszukaj ostatni fragmentu tekstu za pomocą WYSZUKAJ i SZUKAJ.TEKST — sztuczki #24

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

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.

=WYSZUKAJ(255;SZUKAJ.TEKST(D2;A2:A10);B2:B10)

Formuła końcowa i wynik jej działania

Formuła końcowa i wynik jej działania

P.S.

Wpis na podstawie Excel Magic Trick 1000

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Wyszukaj ostatni fragmentu tekstu za pomocą WYSZUKAJ i SZUKAJ.TEKST — sztuczki #24