Rozwiązania Excel wyjaśnione dla różnych wersji

Program Excel znacznie się rozwinął w ciągu ostatniej dekady, a każda wersja zawiera różne rozwiązania wielu problemów.

Ostatnio rozwiązywałem kwestię daty w tabeli. Potrzebujesz formuły, aby warunkowo znaleźć najbliższą datę pasującą do dwóch innych kryteriów z tabeli. Rozwiązanie zależy od używanej przeze mnie wersji programu Excel.

W ciągu ostatnich dziesięciu lat Excel zmienił się diametralnie. Teraz bardziej niż kiedykolwiek wersja oprogramowania określa, co możesz zrobić i jak łatwe jest rozwiązanie.

Harmonogram, z którym pracuję, jest pokazany w Rysunek 01.

Muszę znać datę następnej rewizji dla każdego projektu. Oznacza to, że potrzebuję najbliższej daty w kolumnie D, ponieważ w kolumnie E. jest pusta komórka.

Mój raport jest wyświetlany w formacie Rysunek 02. Kolumny mają cztery różne rozwiązania. Wiersz 1 zawiera wersje, z którymi współpracuje. Ocena 2 opisuje rozwiązanie.

Wszystkie formuły pochodzą z klasy 4 w Rysunek 02 pojawia się w Rysunek 03.

Wszystkie formuły wiersza 4 zostały skopiowane. Będziesz musiał się odnieść Rysunek 03 Wyjaśniam również te formuły. Formuły są dość złożone i więcej szczegółów przedstawię w dołączonym filmie do tego artykułu.

Formuła z komórki K9 nie została jeszcze wyświetlona i zostanie sfinalizowana.

Aby zdefiniować lub zwrócić pustą komórkę, możesz użyć podwójnych cudzysłowów w następujący sposób: „”.

Wszystkie wersje – tablica – kolumna H.

Formuły tablicowe są bardziej złożone niż większość formuł. Formuły tablicowe mogą współpracować z zakresami w taki sam sposób, jak standardowe formuły dla pojedynczych komórek. Aby wprowadzić tablice, musisz użyć specjalnego zestawu klawiatury.

Przytrzymaj klawisze Ctrl i Shift, a następnie naciśnij klawisz Enter. Spowoduje to umieszczenie nawiasów klamrowych (nawiasów klamrowych) wokół wzoru. Możesz to zobaczyć w pierwszej wersji wymienionej w Rysunek 03.

Ta formuła tablicowa najpierw oblicza pierwszą funkcję JEŻELI. Porównuje to każdą komórkę w zakresie A2: A13 z kodem projektu w komórce G4. Jeśli komórka jest zgodna z kodem projektu, przechodzi do następnej funkcji JEŻELI i szuka w odpowiednim wierszu w zakresie E2: 13 pustej komórki.

Jeśli znajdzie pustą komórkę, zwraca odpowiedni wiersz z D2: D13. Jeśli którykolwiek z testów ma wartość FALSE, zwracana jest pusta komórka.

Funkcje JEŻELI łącznie zapewniają zakres dat i pustych komórek dla funkcji MIN (która zwraca wartość minimalną) do recenzji. Funkcja MIN ignoruje puste komórki.

Pozostałe daty są porównywane w celu określenia najwcześniejszej (najniższej) daty. Ten typ obliczeń przy użyciu zwykłych formuł wymaga wykonania kilku komórek. Formuła tablicowa może wykonywać wszystkie obliczenia w jednej komórce.

Excel 2010 i nowsze wersje – grupowanie – pierwsza kolumna

Funkcja AGGREGATE ma niezwykłą zdolność ignorowania komórek błędów. Ta zdolność umożliwia nam utworzenie pojedynczej formuły, która działa jak formuła tablicowa, ale nie jest tablicą.

Funkcja AGREGACJA jest taka sama, jak funkcja SUMY CZĘŚCIOWE. Określasz obliczenie, które ma zostać wykonane. Liczba „15” na początku określa funkcję SMALL. Funkcja SMALL jest elastyczną wersją funkcji MIN.

Funkcja MIN zwraca minimalną wartość w zakresie. Funkcja SMALL może znaleźć najniższą lub drugą najniższą pozycję, określając numer pozycji: 1 znajduje najniższą, a 2 znajduje drugą najniższą.

Drugi argument AGGREGATE ma wartość 6, a to instruuje go, aby ignorował błędy.

W programie Excel daty są liczbami. Każda data ma podstawowy numer seryjny. W funkcji AGREGATUJ dzielimy zakres dat przez wynik obliczenia warunkowego. To konto rozpatruje dwa oddzielne warunki. Oto jak działa to obliczenie.

Każda komórka w zakresie może zwracać wartość PRAWDA lub FAŁSZ. Kiedy pomnożymy oba warunki razem, TRUE zamieni się na 1, a FALSE na 0. Wynik z A2 zostanie pomnożony przez wynik z E2 i tak dalej poniżej zakresów.

Pod dwoma warunkami istnieją cztery możliwości i dwa możliwe wyniki, jak pokazano na Rysunek 04.

Kiedy pomnożymy cokolwiek przez zero (FALSE), zwraca zero, jak widać w Rysunek 04. Zwraca 1 tylko wtedy, gdy oba warunki są PRAWDZIWE.

Podzieliliśmy daty na wyniki dla tego przypadku. Każdy warunek, który nie pasuje, będzie wynosił zero. Dzielenie przez zero zwraca błąd.

Poleciliśmy funkcji AGGREGATE ignorowanie błędów. Gdy spełnią się oba warunki, data zostanie podzielona na 1, pozostawiając ją niezmienioną. Pozostałe daty będą spełniały oba kryteria. Jest analizowany przez funkcję SMALL w celu określenia minimalnej daty.

Liczba 1 na końcu funkcji AGREGATU wskazuje, że funkcja MAŁA zwraca najniższą (najstarszą) wartość dat.

Dodano funkcję IFERROR w celu obsługi błędu generowanego, gdy nie są spełnione żadne warunki.

Dzieje się tak, gdy nie ma pustej komórki dla określonego kodu projektu.

Excel 2019 i nowsze – MINIFS – preferowane rozwiązanie – kolumna J.

Tak jak SUMA.WARUNKÓW umożliwia warunkowe grupowanie, tak MIN.WARUNKÓW umożliwia warunkowe obliczenia minimalne.

Pierwszy zakres, D2: D13, to zakres, w którym znajduje się dolna granica.

Następnie kojarzysz zakres warunku A2: A13 z warunkiem G4. Możesz dodać warunki, dodając kolejny zakres warunków, E2: E13 i inny warunek „”. Wszystkie są oddzielone przecinkami.

Ponieważ ta funkcjonalność została stworzona, aby sprostać naszym wymaganiom, jest to zalecane rozwiązanie. Niestety jest dostępny tylko w Excel 2019 i wersji subskrypcyjnej. Zauważ, że istnieje również funkcja MAXIFS.

Wersja tylko dla subskrypcji – tablice dynamiczne – kolumna K.

Wersja subskrypcyjna programu Excel jest dostępna za pośrednictwem platformy Microsoft 365 (dawniej Office 365). Ta wersja otrzymuje regularne aktualizacje. W 2020 roku otrzymał aktualizację Dynamic Arrays, którą omówiłem w trzech osobnych artykułach w maju, czerwcu i lipcu 2020 roku.

Macierze dynamiczne zmieniają sposób obliczania formuł. W przypadku formuł tablicowych nie trzeba już używać klawiszy Ctrl + Shift + Enter, aby je wprowadzać. Formuły w komórkach H4 i K4 są identyczne, z wyjątkiem nawiasów na obu końcach.

Obliczenie w wersji subskrypcyjnej działa tak samo, jak opisana wcześniej formuła tablicowa. Nie polecam tego rozwiązania z dynamiczną matrycą, ponieważ dysponujemy wyspecjalizowaną funkcjonalnością MINIFS dostępną w wersji subskrypcyjnej.

Ta formuła pokazuje, że nowy aparat obliczeniowy programu Excel może obsługiwać formułę pojedynczej komórki, która działa z warunkami na podstawie zakresów, a nie tylko pojedynczych komórek. Zawarłem go, abyś mógł zobaczyć, jak możemy go dostosować do bardziej wymagających wymagań.

Załóżmy, że zamiast znajdować pierwszą datę na podstawie kodu projektu, chcemy znaleźć pierwszą datę na podstawie pierwszej litery kodu konta w kolumnie C. Różne prefiksy mogą odnosić się do różnych kategorii rachunków. Rysunek 05 Ma raport. Formuła jest zawarta w K9 w Rysunek 03. MINIFS nie radzi sobie z tego typu sytuacjami.

Tablice dynamiczne umożliwiają manipulowanie zakresami za pomocą innych funkcji. LEFT wyodrębnia znaki z lewej strony komórki. Możemy go użyć do wyodrębnienia pierwszej litery zakresu kolumny obliczeniowej.

Możemy zamienić warunek kodu projektu na warunek, który wyodrębnia pierwszą literę z każdej komórki w zakresie C2: C13. Jest to następnie porównywane z literą w komórce J9, aby zwrócić PRAWDA lub FAŁSZ dla każdej komórki w zakresie.

Excel rozwijał się przez lata. Przeszliśmy od złożonych formuł tablicowych, które wymagały specjalnego wprowadzania danych z klawiatury, do funkcji, która działa jak tablica, a następnie do funkcji niestandardowej zbudowanej do obsługi minimalnych obliczeń warunkowych.

Wreszcie program Excel ma formuły, które mogą obsługiwać elastyczne warunki w zakresach przy użyciu tablic dynamicznych.

Pamiętaj, aby obejrzeć film wideo dołączony do tego artykułu, w którym bardziej szczegółowo opisano działanie tych formuł.

Towarzyszące wideo i plik Excel zawierają bardziej szczegółowe informacje, aby zilustrować te techniki.

Neale Blackwood CPA prowadzi księgowość A4, zapewnia szkolenia, seminaria internetowe i usługi konsultingowe dotyczące programu Excel. Pytania można przesyłać na adres [email protected]