Kontrola sumy wartości, aby nie przekroczyła budżetu – widzowie #27

Jak kontrolować budżet, żeby nie przekroczył wyznaczonej wartości?


Kontrola sumy wartości, aby nie przekroczyła budżetu – widzowie #27/span>

Kontrola sumy wartości, aby nie przekroczyła budżetu - widzowie #27

Jeśli chcesz aby Excel mówił Ci kiedy suma kosztów przekroczy budżet to może wykorzystać do tego opcję Walidacji danych.

Zaznaczasz obszar sumowania kosztów i wstawiasz przykładową formułę w opcję Poprawność danych:

=SUMA(D$2:D$6)<=A$2

Teraz jeśli przekroczysz budżet Excel nie pozwoli Ci wstawić wartości, która spowoduje przekroczenie sumy, dopóki nie zmniejszysz kosztów tak, aby suma mieściła się w budżecie.

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 – Kontrola sumy wartości, aby nie przekroczyła budżetu – widzowie #27

Automatyczna tabela pomocniczej z tabeli głównej z dynamicznym kryterium – widzowie #26

Jak stworzyć tabelę pomocniczą, która będzie wyciągać automatycznie dane z tabeli głównej na podstawie kryterium?


Automatyczna tabela pomocniczej z tabeli głównej z dynamicznym kryterium – widzowie #26

Automatyczna tabela pomocniczej z tabeli głównej z dynamicznym kryterium - widzowie #26

W filmie Excel – Automatyczne wypełniana tabela pomocniczej z tabeli głównej z 1 kryterium – widzowie #25

wykorzystaliśmy formułę do pobierania danych z tabeli głównej do tabeli pomocniczej przy założeniu 1 kryterium

=JEŻELI.BŁĄD(INDEKS(Tabela13[#Dane];MIN.K(JEŻELI($A$5:$A$20=$G$2;WIERSZ(Fabryka)-WIERSZ($A$5)+1);ILE.WIERSZY($F$5:$F5));LICZBA.KOLUMN($A$5:A$5));””)

a co w sytuacji gdy chcemy, żeby nasze kryterium było dynamiczne?

Potrzebujemy zmodyfikować test logiczny w funkcji JEŻELI $A$5:$A$20=$G$2
tak, żeby przesuwał się po kolumnach tabeli głównej w zależności od kryterium jakie wybierzemy.

Nowy test logiczny będzie wyglądał tak:

PRZESUNIĘCIE($A$5:$A$20;0;PODAJ.POZYCJĘ($G$1;Tabela1[#Nagłówki];0)-1)=$G$2

wykorzystujemy funkcję PRZESUNIĘCIE do przesuwania się od pierwszej kolumny ($A$5:$A$20).
Drugi parametr (0) mówi nam, że nie chcemy się ruszać z pozycji startowej jeśli chodzi o wiersze.

Trzeci parametr (PODAJ.POZYCJĘ($G$1;Tabela1[#Nagłówki];0)-1) podaje nam o ile kolumn chcemy się przesunąć w zależności od rodzaju kryterium (nagłówka, dla którego ustaliliśmy kryterium).
Po prostu szukamy, go, a właściwie jego pozycji w nagłówkach tabeli. Potrzebujemy tutaj funkcji PODAJ.POZYCJĘ i przeszukiwania dokładnego.
Ważne, że od wyniku funkcji PODAJ.POZYCJĘ potrzebujemy odjęć jedynkę ponieważ, jeśli kryterium jest z 1 kolumny nie chcemy się przesuwać (0 kolumn), jeśli z 2 kolumny to chcemy się przesunąć o 1 kolumnę itd.

Po skorygowaniu formuły wygląda ona tak:

=JEŻELI.BŁĄD(INDEKS(Tabela1;MIN.K(JEŻELI(PRZESUNIĘCIE($A$5:$A$21;0;PODAJ.POZYCJĘ($G$1;Tabela1[#Nagłówki];0)-1)=$G$2;WIERSZ($C$5:$C$21)-WIERSZ($A$5)+1);ILE.WIERSZY($F$5:$F5));LICZBA.KOLUMN($A$5:A$5));””)

i w zależności od dynamicznego kryterium daje odpowiednie wyniki.

Do stworzenia dynamicznego kryterium przydadzą Ci się informacje z filmów:

Dynamiczna zmiana listy rozwijanej na podstawie innej listy – porada #83

Wyszukanie unikalnych nazw do dynamicznej listy rozwijanej z walidacją danych – sztuczki #47

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 – Automatyczna tabela pomocniczej z tabeli głównej z dynamicznym kryterium – widzowie #26

Automatyczne wypełniana tabela pomocniczej z tabeli głównej z 1 kryterium – widzowie #25

Jak stworzyć tabelę pomocniczą, która będzie wyciągać automatycznie dane z tabeli głównej na podstawie kryterium?


Automatyczne wypełniana tabela pomocniczej z tabeli głównej z 1 kryterium – widzowie #25

WAutomatyczne wypełniana tabela pomocniczej z tabeli głównej z 1 kryterium - widzowie #25

Żeby stworzyć tabelę pomocniczą, która wypełnia się sama na podstawie tabeli głównej i 1 kryterium trzeba zbudować taką formułę.

=JEŻELI.BŁĄD(INDEKS(Tabela13[#Dane];MIN.K(JEŻELI(Fabryka=$G$2;WIERSZ(Fabryka)-WIERSZ($A$5)+1);ILE.WIERSZY($F$5:$F5));LICZBA.KOLUMN($A$5:A$5));””)

Rdzeniem tej formuły jest funkcja INDEKS której podajemy parametry, które pozwolą mam odnaleźć odpowiednią komórkę w tabeli głównej.

Pierwszy argument (Tabela13[#Dane]) to tabela główna.
Trzeci argument (LICZBA.KOLUMN($A$5:A$5)) prosto mówi, z której kolumny pobierać dane przez prosty rozrost obszaru kolumn przy przeciąganiu formuł w prawo w tabeli pomocniczej.

Drugi parametr (MIN.K(JEŻELI(Fabryka=$G$2;WIERSZ(Fabryka)-WIERSZ($A$5)+1);ILE.WIERSZY($F$5:$F5))) jest najbardziej skomplikowany.

Przede wszystkim sprawdzamy w nim kryterium (Fabryka=$G$2) a następnie za pomocą funkcji JEŻELI tworzymy z tego sprawdzenia tablicę relatywnych pozycji wierszy, gdzie kryterium jest spełnione i wartości FAŁSZ w wierszach, gdzie nie jest spełnione.

Następnie z tej tablicy wybieramy po kolei wiersze od najmniejszych do coraz większych wraz z przesuwaniem się w dół w tabeli pomocniczej. Zapewnia nam to funkcja MIN.K i odwołanie do liczenia ile wierszy w dół już zeszliśmy w tabeli pomocniczej ILE.WIERSZY($F$5:$F5) ważne tu jest całkowite zalokowanie pierwszego fragmentu zakresu a drugiego pozostawionego swobodnego przy kopiowaniu w dół, że ta wartość rosła co 1 z każdym kolejnym wierszem.

Ponieważ łatwo trafimy na sytuację, gdzie liczba wierszy w kolumnie pomocniczej będzie większa niż liczba wierszy spełniająca warunek kryterium dlatego potrzebujemy jeszcze sobie poradzić z błędem za pomocą funkcji JEŻELI.BŁĄD i wstawienia pustego ciągu znaków w takiej sytuacji „”

Na koniec trzeba pamiętać o zatwierdzeniu formuły kombinacją klawiszy Ctrl + Shift + Enter bo zawiera obliczenia tablicowe i skopiowaniu jej na wszystkie komórki tabeli pomocniczej.

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 – Automatyczne wypełniana tabela pomocniczej z tabeli głównej z 1 kryterium – widzowie #25

Wykres przedziału czynszu od do w różnych miejscowościach – widzowie #24

Jak stworzyć wykres pokazujący przedział od maksimum do minimum?


Wykres przedziału czynszu od do w różnych miejscowościach – widzowie #24

Wykres przedziału czynszu od do w różnych miejscowościach - widzowie #24

Dostałem zapytanie jak stworzyć wykres, który przedstawiałby przedział wartości czynszu od do w różnych miejscowościach.

Znalazłem bardzo proste rozwiązanie, które dobrze się prezentowało, ale najpierw trzeba było przygotować dane, bo format wejściowy wyglądał tak:

3,60-5,30

czyli min i max oddzielone myślnikiem.

Czyli trzeba było z tekstu (wyrównany do lewej) wyciągnąć wartości liczbowe.

Minimum wyciągnąłem formułą:

=LEWY(B3;ZNAJDŹ(„-„;B3)-1)+0

Czyli wyciągnąłem wszystkie znaki od lewej przed myślnikiem, a następnie dodałem zero, by zamienić tekst na liczbę.

Bardzo podobnie wyciągnąłem maksimum:

=PRAWY(B3;DŁ(B3)-ZNAJDŹ(„-„;B3))+0

Tym razem wyciągałem wszystkie znaki od prawej po myślniku i dodałem zero by zamienić tekst na liczbę.

Teraz wystarczyło zaznaczyć dane liczbowe z nagłówkami i wstawić wykres liniowy najlepiej ze znacznikami.

Kolejnym krokiem było dodanie linii (karta Narzędzia wykresów – Układ – opcja linie maks-min).

Po tym zostały tylko czynności upiększające – usunięcie legendy oraz linii z serii, oraz dopracowanie kolorystyki, wielkości znaczników i linii maks-min.

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 – Wykres przedziału czynszu od do w różnych miejscowościach – widzowie #24

Kryterium pierwszego dnia miesiąca bez jedynki – porada #95

Czy „cze2013” równa się 2013-06-01?


Kryterium pierwszego dnia miesiąca bez jedynki – porada #95

Kryterium pierwszego dnia miesiąca bez jedynki - porada #95

Odkryłem, że Excel może traktować zapis miesiąc rok np: „cze2013” traktować jako ważną datę pierwszego dnia miesiąca.
Np: jeśli wpiszesz takie kryterium w funkcji LICZ.JEŻELI to ta funkcja będzie liczyć wszystkie wystąpienia 2013-06-01.

Dodatkowo funkcja NR.SER.OST.DN.MIES przyjmie argument „cze2013” i na jego podstawie zwróci datę 2013-06-30.

Ale jeśli spróbujemy prostego porównania to nie zadziała. Formuła:

=”cze2013″=2013-06-01

zwróci FAŁSZ

Chodzi o to, że Excel porównuje tu tekst i liczbę, a to nigdy nie będzie prawdą, więc potrzebujemy zmienić np: tekst na liczbę (w tej sytuacji jest to prostsze). Wystarczy np: dodać zero do naszego tekstu

=(„cze2013”)+0=2013-06-01

tym razem formuła zwróci wartość PRAWDA.

Czyli w odpowiednich sytuacjach zapis miesiąc rok („cze2013”) można w Excelu przyrównać do pierwszego dnia miesiąca.

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Kryterium pierwszego dnia miesiąca bez jedynki – porada #95