Czytelnicy Lifehackera są już zaznajomieni z Denis Batyanov którzy podzielili się z nami. Dzisiaj Denis opowie o tym, jak uniknąć najczęstszych problemów z Excelem, które często sami tworzymy.

Natychmiast zrób rezerwację, że materiał artykułu jest przeznaczony dla początkujących. Użytkownicy Excela. Doświadczeni użytkownicy już nie raz tańczyli zapalająco na tym prowizji, więc moim zadaniem jest uchronienie przed tym młodych i niedoświadczonych „tancerzy”.

Nie podajesz nagłówków do kolumn tabeli

Wiele narzędzi programu Excel, takich jak sortowanie, filtrowanie, tabele inteligentne, tabele przestawne, zakłada, że ​​dane zawierają nagłówki kolumn. W przeciwnym razie albo w ogóle nie będziesz mógł z nich korzystać, albo nie będą działać całkiem poprawnie. Zawsze upewnij się, że tabele zawierają nagłówki kolumn.

Puste kolumny i wiersze w Twoich tabelach

To myli Excela. Kiedy napotka pusty wiersz lub kolumnę w Twojej tabeli, zacznie myśleć, że masz 2 tabele, a nie tylko jedną. Będziesz musiał to ciągle poprawiać. Nie ukrywaj również wierszy/kolumn, których nie potrzebujesz w tabeli, lepiej je usunąć.

Wiele tabel na jednym arkuszu

Jeśli nie są to malutkie tabliczki zawierające księgi wartości, to nie warto tego robić.

Pełna praca z więcej niż jednym stołem na arkuszu będzie niewygodna. Na przykład, jeśli jedna tabela znajduje się po lewej stronie, a druga po prawej, filtrowanie jednej tabeli wpłynie na drugą. Jeśli tabele znajdują się jedna pod drugą, nie można użyć ustalania obszarów, a także jedna z tabel będzie musiała być stale przeszukiwana i wykonywać niepotrzebne manipulacje, aby umieścić na niej kursor tabeli. Potrzebujesz tego?

Dane tego samego typu są sztucznie rozmieszczone w różnych kolumnach

Bardzo często użytkownicy, którzy znają Excela wystarczająco powierzchownie, preferują ten format tabeli:

Wydawałoby się, że mamy nieszkodliwy format gromadzenia informacji o sprzedaży agentów i ich karach. Taki układ stołu jest dobrze odbierany przez osobę wizualnie, ponieważ jest zwarty. Uwierz mi jednak, że to koszmar próbować wydobyć dane z takich tabel i uzyskać sumy częściowe (informacje zbiorcze).

Fakt jest taki podany format zawiera 2 wymiary: dla , musisz zdecydować się na wiersz, iterując po gałęzi, grupie i agencie. Kiedy znajdziesz właściwy wiersz, będziesz musiał poszukać żądanej kolumny, ponieważ jest ich dużo. A ta „dwuwymiarowość” znacznie komplikuje pracę z taką tabelą dla standardowych narzędzi Excela – formuł i tabel przestawnych.

Jeśli zbudujesz tabelę przestawną, przekonasz się, że nie jest możliwe łatwe uzyskanie danych według roku lub kwartału, ponieważ wskaźniki są rozłożone na różne pola. Nie masz jednego pola wielkości sprzedaży, którym można wygodnie manipulować, ale 12 oddzielnych pól. Będziesz musiał ręcznie utworzyć oddzielne pola obliczeniowe dla kwartałów i lat, chociaż gdyby to wszystko było w jednej kolumnie, tabela przestawna zrobiłaby to za Ciebie.

Jeśli chcesz zastosować standardowe formuły sumowania, takie jak SUMIF, SUMIFS, SUMPRODUCT, przekonasz się również, że nie będą one działać skutecznie z tym układem tabeli.

Rozdzielenie informacji na różnych arkuszach książki „dla wygody”

Innym częstym błędem jest posiadanie standardowego formatu tabeli i potrzeba analizy opartej na tych danych, aby opublikować ją na osobnych arkuszach skoroszytu programu Excel. Na przykład często tworzy się osobne arkusze na każdy miesiąc lub rok. W rezultacie ilość pracy związanej z analizą danych jest faktycznie mnożona przez liczbę utworzonych arkuszy. Nie musisz tego robić. Zbierz informacje na JEDNYM arkuszu.

Informacje w komentarzach

Użytkownicy często dodają ważna informacja, którego mogą potrzebować, w komentarzu do komórki. Pamiętaj, że to, co jest w komentarzach, możesz zobaczyć tylko (jeśli to znajdziesz). Trudno go umieścić w celi. Polecam, lepiej przeznaczyć osobną kolumnę na komentarze.

Formatowanie bałaganu

Zdecydowanie nie doda niczego dobrego do Twojego stołu. Wygląda to odpychająco dla osób korzystających z Twoich arkuszy kalkulacyjnych. W najlepszym razie nie będą przywiązywać do tego wagi, w najgorszym pomyślą, że nie jesteś zorganizowany i niechlujny w biznesie. Staraj się:

Scalanie komórek

Używaj łączenia komórek tylko wtedy, gdy nie możesz się bez niego obejść. Scalone komórki bardzo utrudniają manipulowanie zakresami, w których się mieszczą. Występują problemy z przenoszeniem komórek, wstawianiem komórek itp.

Łączenie tekstu i liczb w jednej komórce

Bolesne wrażenie robi komórka zawierająca liczbę, uzupełnioną z tyłu stałą tekstową „RUB”. lub „USD” wprowadzone ręcznie. Zwłaszcza jeśli tak nie jest drukowany formularz, ale zwykły stół. Operacje arytmetyczne z takimi komórkami są naturalnie niemożliwe.

Liczby jako tekst w komórce

Unikaj przechowywania danych liczbowych w komórce w formacie tekstowym. Z biegiem czasu niektóre komórki w takiej kolumnie będą miały format tekstowy, a niektóre będą miały zwykły. Z tego powodu będą problemy z formułami.

Jeśli Twój stół będzie prezentowany przez projektor LCD

Wybierz najbardziej kontrastujące kombinacje koloru i tła. Ciemne tło i jasne litery dobrze prezentują się na projektorze. Najgorsze wrażenie robi czerwień na czerni i odwrotnie. Ta kombinacja wygląda na projektorze z wyjątkowo niskim kontrastem - unikaj tego.

Arkusz trybu strony w Excelu

Jest to ten sam tryb, w którym program Excel pokazuje, w jaki sposób arkusz zostanie podzielony na strony po wydrukowaniu. Obramowania stron są podświetlone na niebiesko. Nie polecam ciągłej pracy w tym trybie, co wielu robi, ponieważ sterownik drukarki bierze udział w procesie wyświetlania danych na ekranie, a to z wielu powodów (na przykład drukarka sieciowa i ten moment niedostępne) jest najeżona zawieszaniem się w procesie wizualizacji i przeliczania formuł. Pracuj normalnie.

Wiele więcej przydatna informacja Dowiedz się więcej o programie Excel na

Możesz dostosować wyświetlanie wartości zerowych w komórce lub użyć zestawu standardów formatowania w tabeli, które wymagają ukrycia wartości zerowych. Możesz pokazywać i ukrywać wartości null na różne sposoby.

Ukrywanie i pokazywanie wszystkich wartości null w arkuszu

Wybierz Plik > Parametry > do tego.

W grupie

• Pokaż zera w komórkach zawierających wartości zerowe.

Ukrywanie wartości zerowych w wybranych komórkach

Wykonaj poniższe czynności, aby ukryć wartości null w wybranych komórkach przy użyciu formatu liczb. Jeśli wartość w jednej z tych komórek zmieni się na wartość niezerową, zostanie ona wyświetlona w komórce, a jej format będzie podobny do ogólnego formatu liczb.

Pokaż ukryte wartości.

Wybierz komórki z ukrytymi wartościami zerowymi.

Możesz nacisnąć klawisze CTRL+1 lub na karcie Dom Kliknij Format > Formatuj komórki.

Aby zastosować domyślny format liczb, wybierz Numer > Ogólne i naciśnij przycisk ok.

Ukrywanie wartości zerowych zwracanych przez formułę

Na karcie Dom Formatowanie warunkowe i wybierz Reguły wyboru komórek > Równe.

W lewym polu wpisz 0 .

W prawym polu wybierz Format niestandardowy.

W terenie Format komórki otwórz kartę Czcionka.

Katalogowany Kolor wybierz biały kolor i naciśnij przycisk ok.

Wyświetlaj zera jako spacje lub myślniki

Aby rozwiązać ten problem, użyj funkcji JEŻELI.

Jeśli komórka zawiera wartości null, użyj formuły takiej jak ta, aby zwrócić pustą komórkę:

JEŻELI(A2-A3=0;"";A2-A3)

Oto jak czytać formułę. Jeśli wynik obliczenia (A2-A3) to „0”, nic nie jest wyświetlane, w tym „0” (jest to oznaczone podwójnymi cudzysłowami „”). W przeciwnym razie wyświetlany jest wynik obliczeń A2-A3. Jeśli nie chcesz pozostawiać komórek pustych, ale wyświetlać coś innego niż „0”, wstaw łącznik „-” lub inny znak między podwójnymi cudzysłowami.

Wybierz raport w formie tabeli przestawnej.

Na karcie Analiza w grupie Stół obrotowy Parametry i wybierz przedmiot Parametry.

Przejdź do zakładki Znacznik i format

• W terenie Format Sprawdź pudełko Wyświetlanie błędów

  Pole wyboru W przypadku pustych komórek wyświetl

Konieczność wyświetlania wartości zerowych (0) na arkuszach nie zawsze pojawia się. Niezależnie od tego, czy standardy formatowania wymagają od Ciebie lub Twoich własnych preferencji pokazywania lub ukrywania wartości null, istnieje kilka sposobów na zaimplementowanie wszystkich tych wymagań.

Wybierz Plik > Parametry > do tego.

W grupie Pokaż opcje dla następnego arkusza wybierz arkusz, a następnie wykonaj jedną z następujących czynności:

• Aby wyświetlić zero (0) wartości w komórkach, zaznacz pole wyboru Pokaż zera w komórkach zawierających wartości zerowe.

  Aby wyświetlić wartości null jako puste komórki, usuń zaznaczenie Pokaż zera w komórkach zawierających wartości zerowe.

Możesz nacisnąć klawisze CTRL+1 lub na karcie Dom Kliknij Format > Formatuj komórki.

W terenie Rodzaj Wchodzić 0;-0;;@

Uwagi:

Ukryte wartości pojawiają się na pasku formuły lub w komórce tylko wtedy, gdy edytujesz ich zawartość. Te wartości nie są drukowane.

Aby ponownie wyświetlić ukryte wartości, zaznacz komórki, a następnie naciśnij klawisze CTRL+1 lub na karcie Dom w grupie komórki najedź kursorem na element Format i wybierz Format komórki. Aby zastosować domyślny format liczb, na liście Kategoria Wybierz Ogólny. Aby ponownie wyświetlić datę i godzinę, wybierz odpowiedni format daty i godziny na karcie Numer.

Wybierz komórkę zawierającą wartość zero (0).

Na karcie Dom w grupie Style kliknij strzałkę obok elementu Formatowanie warunkowe, najedź na element Zasady wyboru komórek i wybierz opcję Równa się.

W lewym polu wpisz 0 .

W prawym polu wybierz Format niestandardowy.

W oknie dialogowym Format komórki otwórz kartę Czcionka.

W terenie Kolor wybierz biały.

Aby wykonać to zadanie, użyj funkcji JEŚLI.

Przykład

Kopiowanie przykładu

Funkcja JEŻELI.

Ukrywanie wartości zerowych w raporcie w formie tabeli przestawnej

Na karcie Parametry w grupie Opcje tabeli przestawnej kliknij strzałkę obok polecenia Parametry i wybierz przedmiot Parametry.

Przejdź do zakładki Znacznik i format, a następnie wykonaj następujące czynności:

Zmiana sposobu wyświetlania błędów. W terenie Format Sprawdź pudełko Wyświetlanie błędów. Wpisz w polu wartość, którą chcesz wyświetlić zamiast błędów. Aby wyświetlić błędy jako puste komórki, usuń cały tekst z pola.

Zmień sposób wyświetlania pustych komórek. Pole wyboru W przypadku pustych komórek wyświetl. Wprowadź w polu wartość, którą chcesz wyświetlić w pustych komórkach. Aby były puste, usuń cały tekst z pola. Aby wyświetlić wartości null, wyczyść to pole wyboru.

Konieczność wyświetlania wartości zerowych (0) na arkuszach nie zawsze pojawia się. Niezależnie od tego, czy standardy formatowania wymagają od Ciebie lub Twoich własnych preferencji pokazywania lub ukrywania wartości null, istnieje kilka sposobów na zaimplementowanie wszystkich tych wymagań.

Pokaż lub ukryj wszystkie wartości zerowe w arkuszu

Ukrywanie wartości zerowych w wybranych komórkach przy użyciu formatu liczb

Te akcje pozwalają ukryć wartości null w wybranych komórkach. Jeśli wartość w jednej z komórek stanie się niezerowa, jej format będzie podobny do ogólnego formatu liczb.

Wybierz komórki zawierające wartości null (0), które chcesz ukryć.

Możesz nacisnąć klawisze CTRL+1 lub na karcie Dom w grupie komórki Kliknij Format > Formatuj komórki.

W terenie Rodzaj Wchodzić 0;-0;;@

Uwagi:

Ukrywanie wartości zerowych zwracanych przez formułę przy użyciu formatowania warunkowego

Wybierz komórkę zawierającą wartość zero (0).

Na karcie Dom w grupie Style kliknij strzałkę obok przycisku Formatowanie warunkowe i wybierz Reguły wyboru komórek > Równe.

W lewym polu wpisz 0 .

W prawym polu wybierz Format niestandardowy.

W oknie dialogowym Format komórki otwórz kartę Czcionka.

W terenie Kolor wybierz biały.

Używanie formuły do ​​wyświetlania zer jako spacji lub myślników

Aby wykonać to zadanie, użyj funkcji JEŻELI.

Przykład

Aby ułatwić zrozumienie tego przykładu, skopiuj go na pusty arkusz.

Kopiowanie przykładu

Podkreśl przykład podany w tym artykule.

Ważny: Nie wyróżniaj nagłówków wierszy ani kolumn.

Zaznacz przykład w pomocy

Naciśnij klawisze CTRL+C.

W programie Excel utwórz pusty skoroszyt lub arkusz.

Zaznacz komórkę A1 w arkuszu i naciśnij klawisze CTRL+V.

Ważny: Aby przykład działał poprawnie, musi być wstawiony do komórki A1.

Aby przełączać się między wyświetlaniem wyników a wyświetlaniem formuł zwracających te wyniki, naciśnij klawisze CTRL+` (akcent) lub na karcie Formuły w grupie Zależności formuł kliknij Pokaż formuły.

Kopiując przykład do pustego arkusza, możesz go dostosować tak, jak chcesz.

Aby uzyskać więcej informacji na temat korzystania z tej funkcji, zobacz artykuł dotyczący funkcji JEŻELI.

Ukrywanie wartości zerowych w raporcie w formie tabeli przestawnej

Kliknij raport w formie tabeli przestawnej.

Na karcie Parametry w grupie Opcje tabeli przestawnej kliknij strzałkę obok polecenia Parametry i wybierz przedmiot Parametry.

Przejdź do zakładki Znacznik i format, a następnie wykonaj następujące czynności:

Zmiana sposobu wyświetlania błędów. W terenie Format Sprawdź pudełko Wyświetlanie błędów. Wpisz w polu wartość, którą chcesz wyświetlić zamiast błędów. Aby wyświetlić błędy jako puste komórki, usuń cały tekst z pola.

Zmień sposób wyświetlania pustych komórek. Pole wyboru W przypadku pustych komórek wyświetl. Wprowadź w polu wartość, którą chcesz wyświetlić w pustych komórkach. Aby były puste, usuń cały tekst z pola. Aby wyświetlić wartości null, wyczyść to pole wyboru.

Praca z projektem VB (12)
Formatowanie warunkowe (5)
Listy i zakresy (5)
Makra (procedury VBA) (63)
Różne (39)
Błędy i usterki Excela (3)

Excel nie liczy poprawnie. Czemu?

Często przy obliczaniu różnicy dwóch komórek w Excelu widać, że nie jest to zero, chociaż liczby są takie same. Na przykład komórki A1 i B1 zawierają tę samą liczbę 10,7, a w C1 odejmujemy inną od jednej:

A najdziwniejsze jest to, że nie kończymy na 0! Czemu?

Powód jest oczywisty – format komórki
Po pierwsze, najbardziej oczywista odpowiedź: jeśli porównujesz wartości dwóch komórek, musisz upewnić się, że liczby są naprawdę równe i nie są zaokrąglone przez format komórek. Na przykład, jeśli weźmiemy te same liczby z powyższego przykładu, to jeśli je wybierzemy -prawy przycisk myszy - Formatuj komórki-patka Numer-wybierz format numeryczny i ustaw liczbę miejsc dziesiętnych na 7:

Teraz wszystko staje się oczywiste - liczby są różne i zostały po prostu zaokrąglone przez format komórki. I oczywiście nie mogą być równe. W takim przypadku optymalnie byłoby zrozumieć, dlaczego liczby są takie, jakie są, i dopiero wtedy podjąć decyzję. A jeśli masz pewność, że liczby naprawdę należy zaokrąglić do dziesiątych części, możesz użyć funkcji ZAOKR we wzorze:
=ZAOKR(B1 ,1)-ZAOKR(A1 ,1)=0
=ZAOKR(B1,1)-ZAOKR(A1,1)=0
Istnieje również bardziej radykalna metoda:

• Excel 2007: przycisk pakietu Office -Opcje Excela -Zaawansowany -
• Excel 2010: Plik -Opcje -Zaawansowany -Ustaw precyzję, jak pokazano
• Excel 2013 i nowsze wersje: Plik -Opcje -Zaawansowany -Ustaw precyzję, jak pokazano

Spowoduje to zapisanie wszystkich liczb na wszystkich arkuszach skoroszytu dokładnie tak, jak są one wyświetlane w formacie komórki. Tę czynność najlepiej wykonać na kopii książki, ponieważ. przenosi do formularza wszystkie dane liczbowe ze wszystkich arkuszy księgi, gdy są one wyświetlane na ekranie. Tych. jeśli sama liczba zawiera 5 miejsc po przecinku, a format komórki jest ustawiony tylko na 1, to po zastosowaniu tej opcji liczba zostanie zaokrąglona do 1 miejsca po przecinku. W tym samym czasie anuluj ta operacja nie możesz, chyba że zamkniesz skoroszyt bez zapisywania.

Powodem jest oprogramowanie
Ale często w Excelu można zaobserwować bardziej interesujące „zjawisko”: różnica między dwiema liczbami ułamkowymi uzyskanymi przez formułę nie jest równa dokładnie tej samej liczbie zapisanej bezpośrednio w komórce. Na przykład wpisz w komórce następującą formułę:
=10,8-10,7=0,1
wynik powinien być odpowiedzią PRAWDA. Ale w rzeczywistości to będzie FAŁSZYWE. I ten przykład nie jest jedyny - takie zachowanie Excela nie jest rzadkością w obliczeniach. Można go również spotkać w mniej jednoznacznej formie - gdy obliczenia opierają się na wartości innych komórek, które z kolei są również obliczane za pomocą formuł itp. Ale powód jest taki sam we wszystkich przypadkach.

Dlaczego pozornie identyczne liczby nie są równe?
Najpierw zrozummy, dlaczego program Excel uważa powyższe wyrażenie za fałszywe. W końcu, jeśli odejmiesz liczbę 10,7 od 10,8 - w każdym razie otrzymasz 0,1. Więc gdzieś po drodze coś poszło nie tak. Napiszmy w izolatka lewa strona wyrażenia: =10.8-10,7 . W komórce pojawi się 0.1. Teraz zaznacz tę komórkę - prawy przycisk myszy - Formatuj komórki-patka Numer-wybierz format numeryczny i ustaw liczbę miejsc dziesiętnych na 15:


i teraz widać, że w rzeczywistości komórka to nie dokładnie 0.1, ale 0.100000000000001. Tych. w 15. cyfrze znaczącej mamy „ogon” w postaci dodatkowej jednostki.
A teraz dowiemy się, skąd wziął się ten „ogon”, ponieważ zarówno logicznie, jak i matematycznie nie powinno go tam być. Postaram się opowiedzieć bardzo zwięźle i bez zbędnej zawiłości - jeśli chcesz, możesz znaleźć ich wiele na ten temat w Internecie.
Rzecz w tym, że w tych odległych czasach (to mniej więcej lata 70.), kiedy PC był jeszcze czymś egzotycznym, nie było jednego standardu pracy z liczbami zmiennoprzecinkowymi (ułamkowymi, choć prostymi). Dlaczego w ogóle ten standard? Więc co? programy komputerowe widzą liczby na swój sposób, a ułamkowe ogólnie ze statusem „wszystko jest skomplikowane”. Jednocześnie ta sama liczba ułamkowa może być reprezentowana na różne sposoby, a operacje z nią mogą być również przetwarzane. Dlatego w tamtych czasach ten sam program podczas pracy z liczbami mógł dawać różne wyniki na różnych komputerach. Uwzględnienie wszystkich możliwych pułapek każdego komputera nie jest łatwym zadaniem, więc w pewnym momencie rozpoczęto opracowywanie jednego standardu pracy z liczbami zmiennoprzecinkowymi. Pomijając różne szczegóły, niuanse i ciekawość samej historii powiem tylko, że w końcu to wszystko zaowocowało Standard IEEE754. I zgodnie z jego specyfikacją, w dziesiętnej reprezentacji dowolnej liczby, błędy są dopuszczalne w 15. cyfrze znaczącej. Co prowadzi do nieuniknionych błędów w obliczeniach. Najczęściej można to zaobserwować właśnie w operacjach odejmowania, ponieważ. to odejmowanie liczb znajdujących się blisko siebie prowadzi do utraty cyfr znaczących.
Możesz również dowiedzieć się więcej o samej specyfikacji w artykule firmy Microsoft: Wyniki operacji arytmetycznych zmiennoprzecinkowych w programie Excel mogą być niedokładne
To jest właśnie powód takiego zachowania programu Excel. Chociaż uczciwie należy zauważyć, że nie tylko Excel, ale wszystkie programy oparte na ten standard. Oczywiście pojawia się logiczne pytanie: dlaczego przyjęli tak zabugowany standard? Powiedziałbym, że wybrano kompromis między wydajnością a funkcjonalnością. Chociaż mogły istnieć inne powody.

O wiele ważniejsze jest coś innego: jak sobie z tym poradzić?
Właściwie nie ma mowy, bo. to jest błąd oprogramowania. I w tym przypadku nie ma innego wyjścia, jak korzystać z różnego rodzaju łatek, takich jak ROUND i podobnych funkcji. Jednocześnie ROUND powinno być tutaj użyte nie tak, jak pokazano na samym początku, ale trochę inaczej:
=ZAOKR(10,8 - 10,7;1)=0,1
=ZAOKR(10,8-10,7,1)=0,1
tych. w ROUND powinniśmy umieścić samo wyrażenie "buggy", a nie każdy z jego argumentów z osobna. Jeśli umieścisz każdy argument, to nie przyniesie to efektu, ponieważ problem nie tkwi w samej liczbie, ale w sposobie, w jaki program ją widzi. I w tym przypadku 10,8 i 10,7 są już zaokrąglone do jednej cyfry i jasne jest, że zaokrąglanie każdej liczby z osobna nie da żadnego efektu. Możesz jednak wyjść w drugą stronę. Pomnóż każdą liczbę przez określoną wartość (powiedzmy przez 1000, aby usunąć 100% miejsc po przecinku), a następnie odejmij i porównaj:
=((10,8*1000)-(10,7*1000))/1000=0,1

Chciałbym wierzyć, że przynajmniej kiedyś Microsoftowi uda się pokonać opisaną cechę standardu IEEE754, a przynajmniej zrobić łatkę, która wykona proste obliczenia nie gorsze niż kalkulator 50 rubli :) Czy artykuł pomógł? Udostępnij link znajomym! Lekcje wideo

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slajd","texteffecteasing":"łatwośćOutCubic","texteffectduration":600,"texteffectslidedirection":"w lewo","texteffectslideddistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slajd","texteffectslidedirection1":"prawo","texteffectslidedistance1":120,"texteffecteasing1":"łatwośćOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slajd","texteffectslidedirection2":"prawo","texteffectslidedistance2":120,"texteffecteasing2":"łatwośćOutCubic","texteffectduration2":600,"texteffectdelay2"::1500," textcss":"wyświetlanie:blok; dopełnienie:12px; text-align:lewo;","textbgcss":"wyświetlanie:blok;pozycja:bezwzględna;góra:0px;lewo:0px;szerokość:100%;wysokość:100% ; kolor tła:#333333; krycie:0,6; filtr:a lpha(przezroczystość=60);","titlecss":"wyświetlanie:blok; pozycja:względna; czcionka: pogrubiona 14px \"Lucida Sans Unicode\", \"Lucida Grande\",sans-serif,Arial; kolor:#fff;","descriptioncss":"wyświetlanie:blok; pozycja:względna; czcionka:12px \"Lucida Sans Unicode\", \"Lucida Grande\", sans-serif, Arial; kolor:#fff; margines-góra:8px;","buttoncss":"wyświetlanie:blok; pozycja:względna; margines górny:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"rozmiar czcionki:12px;","descriptioncssresponsive":"wyświetlacz:brak !ważne;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Zdarza się, że skopiowali tabelę do Excela, ale liczby w niej nie są brane pod uwagę, formuły nie działająprzewyższać. Jednym z powodów może być to, że wartości komórek są w formacie tekstowym. Pamiętaj, że w programie Excel są dwa formaty - format komórki i format wartości. Przeczytaj więcej na ten temat w artykule Konwertuj datę na tekst programu Excel.
Zastanówmy się więc dlaczego wFormuła Excela nie działa.
Musisz dodać najwyższe liczby w komórkach. W komórkach wartości są zapisywane w formacie tekstowym z niedrukowalnymi znakami między wierszami. Musimy przekonwertować wartości z tekstu na liczby i podświetlić pierwszą liczbę w komórce. Mamy taki skopiowany stół z 1C.
Pierwszy krok.
Przekształćmy wartość komórek na format liczbowy.
Wybierz komórki. Na karcie Strona główna w sekcji Edycja kliknij przycisk Znajdź.
W wyświetlonym oknie dialogowym w wierszu „Znajdź” umieść przecinek. Nie umieszczaj niczego w wierszu „Zamień”.
Kliknij przycisk „Zamień wszystko”. Wyjdzie tak.
Druga faza.
W tym samym oknie „Znajdź i zamień” (nie usunęliśmy go, a komórki pozostały zaznaczone), wstawiamy kropkę w wierszu „Znajdź”. A w wierszu „Zamień” umieść przecinek. Kliknij „Zaznacz wszystko”.

Wyszło tak.
Usuń okno Znajdź i zamień.
Trzeci etap.
Jak podświetlić pierwszą liczbę w komórceprzewyższać.
W komórce B1 wpisz następującą formułę. =LEWO(A1,ZNAJDŹ(ZNAK(10);A1)-1)
Za pomocą tej formuły wybraliśmy pierwszą liczbę z komórki. Skopiuj formułę według kolumny. Wyszło tak.
W komórce B4 napisaliśmy formułę dodawania. =B1+B2+B3
Autosumowanie nie działa, ale odręczne formuły działają.
W programie Excel możesz ustawić komórkę, aby łączyła się z innym arkuszem, zakresem, komórką, witryną itp. Po kliknięciu tego łącza program Excel podąża za nim. Ale jak wyróżnić samą komórkę, a nie hiperłącze? Przeczytaj artykuł na ten temat

W przypadku błędnych obliczeń formuły wyświetlają kilka rodzajów błędów zamiast wartości. Rozważmy je na praktycznych przykładach w procesie pracy formuł, które dawały błędne wyniki obliczeń.

Błędy formuły Excel wyświetlane w komórkach

Ta lekcja opisuje wartości błędów formuł, które mogą zawierać komórki. Znając znaczenie każdego kodu (na przykład: #WARTOŚĆ!, #DZIAŁ/0!, #NUMER!, #NIE DOTYCZY!, #NAZWA!, #PUSTY!, #REFERENCJA!) możesz łatwo dowiedzieć się, jak znaleźć błąd w formule i ją wyeliminuj.

Jak usunąć #DIV/0 w Excelu?

Jak widać, przy dzieleniu przez komórkę z pustą wartością program traktuje ją jako dzielenie przez 0. W efekcie daje wartość: #DIV/0! Można to zweryfikować za pomocą podpowiedzi.

W innych obliczeniach arytmetycznych (mnożenie, sumowanie, odejmowanie) pusta komórka jest również wartością zerową.



Wynik błędnego obliczenia to #LICZBA!

Błędny numer: #NUMER! jest błędem niemożności wykonania obliczeń we wzorze.

Kilka praktycznych przykładów:

Błąd: #NUMER! występuje, gdy wartość liczbowa jest za duża lub za mała. Ta sama droga podany błąd może wystąpić, gdy próbujesz wyciągnąć pierwiastek z liczby ujemnej. Na przykład =ROOT(-25).

W komórce A1 liczba jest za duża (10^1000). Excel nie może pracować z tak dużymi liczbami.

Komórka A2 ma ten sam problem z dużymi liczbami. Wydawałoby się, że 1000 to mała liczba, ale po zwróceniu jej silni okazuje się, że jest to zbyt duża wartość liczbowa, której Excel nie może obsłużyć.

W komórce A3 - pierwiastek kwadratowy nie może być liczbą ujemną, a program jest wyświetlany podany wynik ten sam błąd.

Jak usunąć ND w Excelu?

Wartość niedostępna: #N/A! - oznacza, że ​​wartość nie jest dostępna dla wzoru:

Formuła zapisana w B1: =MATCH("Maxim", A1:A4) wyszukuje treść tekstową "Maxim" w zakresie komórek A1:A4. Zawartość znaleziona w drugiej komórce A2. Dlatego funkcja zwraca wynik 2. Druga formuła szuka treści tekstowej „Andrey”, a następnie zakres A1:A4 nie zawiera takich wartości. Dlatego funkcja zwraca błąd #N/D (brak danych).

Błąd #NAZWA! w Excelu

Być sklasyfikowane jako błąd w pisaniu funkcji. Błędna nazwa: #IMIĘ! - oznacza, że ​​Excel nie rozpoznał tekstu zapisanego w formule (nazwa funkcji = SUMA() jest mu nieznana, jest napisana z błędem). Jest to wynik błędu składni podczas zapisywania nazwy funkcji. Na przykład:

Błąd #PUSTE! w Excelu

Pusty zestaw: #PUSTY! są błędy operatora przecięcia zbioru. W Excelu istnieje coś takiego jak przecięcie zbiorów. Służy do szybkiego pobierania danych z dużych tabel poprzez zapytanie o punkt przecięcia pionowego i poziomego zakresu komórek. Jeśli zakresy się nie przecinają, program wyświetla błędną wartość - #OPRÓŻNIJ! Ustawiony operator przecięcia jest pojedynczą spacją. Oddziela zakresy pionowe i poziome określone w argumentach funkcji.

W tym przypadku przecięciem zakresów jest komórka C3, a funkcja wyświetla jej wartość.

Podane argumenty w funkcji: =SUMA(B4:D4 B2:B3) – nie tworzą przecięcia. Dlatego funkcja podaje wartość z błędem - #EMPTY!

#POŁĄCZYĆ! - Błąd odwołania do komórki Excela

W tym przykładzie wystąpił błąd, gdy formuła została niepoprawnie skopiowana. Mamy 3 zakresy komórek: A1:A3, B1:B4, C1:C2.

W pierwszym zakresie, w komórce A4, wprowadź formułę sumowania: =SUMA(A1:A3). A następnie skopiuj tę samą formułę do drugiego zakresu, w komórce B5. Formuła, tak jak poprzednio, sumuje tylko 3 komórki B2:B4, z pominięciem wartości pierwszego B1.

Gdy ta sama formuła została skopiowana do trzeciego zakresu, funkcja zwróciła błąd #REF! w komórce C3. Ponieważ powyżej komórki C3 mogą znajdować się tylko 2 komórki, a nie 3 (zgodnie z oryginalną formułą).

Notatka. W takim przypadku najwygodniej jest nacisnąć kombinację klawiszy ALT + = pod każdym zakresem przed rozpoczęciem wprowadzania. Następnie wstaw funkcję sumowania i automatycznie określ liczbę komórek sumujących.

Jak naprawić VALUE w Excelu

#WARTOŚĆ! - błąd w znaczeniu. Jeśli spróbujemy dodać liczbę i słowo w Excelu, otrzymamy #ARG! Ciekawostką jest to, że jeśli spróbujemy dodać dwie komórki, w których wartością pierwszej jest liczba, a drugiej tekst za pomocą funkcji =SUMA(), to nie wystąpi błąd, a tekst przyjmie wartość 0 przy obliczaniu. Na przykład:

Kraty w komórce Excel

Wiersz słupków zamiast wartości komórki ###### - podana wartość nie jest błędem. To tylko informacja, że ​​szerokość kolumny jest zbyt wąska, aby pomieścić poprawne wyświetlanie zawartości komórki. Wystarczy rozwinąć kolumnę. Np. kliknij dwukrotnie lewym przyciskiem myszy na granicy nagłówków kolumn danej komórki.

Tak więc kraty (######) zamiast wartości komórek można zobaczyć z ujemną datą. Na przykład próbujemy odjąć nową datę od starej daty. W wyniku obliczeń format komórek to „Data” (a nie „Ogólne”).

Nieprawidłowy format komórki może również wyświetlać serię znaków krzyżyka (######) zamiast wartości.