Excel – dzielenie wprowadzonej liczby przez 1000

W tym wpisie wytłumaczę co można zrobić, aby liczby, które wpisujemy do komórek Excela, od razy były dzielone przez 1000. Dlaczego? bo np. operujesz na małych liczbach i zamiast pisać 0,002 wygodniej jest Ci wpisać po prostu 2, a Excel i chcesz żeby Excel automatycznie zrozumiał, że chodzi o 0,002. Pokaże też jak dzielić przez inne liczby, nie tylko przez 1000.

SPIS TREŚCI

Wyświetlenie skróconej liczby w komórce Excela

Jeśli pracujesz na bardzo dużych liczbach, np. na wielu tysiącach lub milionach i przytłacza Cię liczba cyfr, to możesz te liczby skrócić. Przykładowo, zamiast widzieć 3.349.203,20 zobaczysz 3 mln. Oto ważne implikacje wynikające z takiego skrócenia liczby:

• • zapis liczby jest krótszy więc łatwiej ją przeczytać.
  • jako że jej zapis jest skrócony, to widzisz jej przybliżoną wartość.
  • liczba tak naprawdę nie jest zmieniana, ani przybliżana – dla Excela to jest dla ta sama liczba, bez żadnych przybliżeń.
  • Możesz dokładnie wskazać, w których komórkach liczby mają być tak prezentowane.

Przejdźmy do działania:

• • Zaznacz komórki, gdzie chcesz widzieć skrócony zapis.
  • naciśnij na nich prawym przyciskiem myszy i wybierz Formatuj komórki (skrót CTRL + 1).
  • W oknie, przejdź do zakładki Liczby i po lewej stronie kliknij w niestandardowe.
  • W polu, które pojawi się po prawej stronie, masz zapisany już jakiś format liczbowy. Powiedzmy, że chcemy pokazać liczbę w najprostszy sposób (bez miejsc dziesiętnych, ani innych dodatków) –  zapisz w tym polu cyfrę 0, a po niej dopisz spację i zamknij okno.

Rysunek 1. Menu po kliknięciu prawym przyciskiem myszy.

Rysunek 2. Okno formatów danych.

Wartość pokazana w komórce będzie 1000 razy mniejsza od oryginalnej. Tak jak pisałem wcześniej, jeśli masz tę komórkę zaznaczoną i zerkniesz na pasek formuły, to tam zobaczysz liczbę oryginalną, która faktycznie znajduje się w komórce.

Rysunek 3. Liczba już po zmianie formatu.

Jeśli będziesz dokonywać jakichkolwiek obliczeń z tą komórką, to Excel będzie korzystał z tej oryginalnej, faktycznej wartości.

Jeśli w oknie formatowania dopiszesz drugą spację, to liczba będzie dzielona przez milion. Dopisanie dodatkowej spacji zawsze „przesuwa przecinek” w tej liczbie o dodatkowej trzy miejsca.

Dzielenie przez zmianę ustawień Excela

Jest taka opcja w Excelu, która „przesuwa przecinek” w liczbach, które zapisujesz. Czyli dzieli je przez 10, albo 100, albo 1000, itd. Ta sama funkcja może liczby także mnożyć przez 10, 100, 1000 itd, co może być przydatne, gdy pracujesz na dużych liczbach.

Pamiętaj jednak, że gdy włączysz tę opcję, to będzie ona działać we wszystkich plikach Excela, które otworzysz na swoim komputerze. Nie ma możliwości wybrania konkretnego pliku, ani konkretnego arkusza, ani komórek, w których będzie ona działać.

Aby włączyć tę opcję, przejdź do:

• • Zakładki Plik, w górnym menu Excela.
  • Potem na w lewym, dolnym rogu kliknij Opcje.
  • W oknie, wybierz po lewej stronie zakładkę Zaawansowane.
  • Zaznacz opcję Automatycznie wstaw przecinek dziesiętny, w górnej części okna.
  • Poniżej wybierz miejsca dziesiętne. Jeśli wybierzesz np. „3”, to liczby będą dzielone przez 1000, jeśli „2”, to będą dzielone przez „100”, ale jeśli wybierzesz „-3”, to będą mnożone przez 1000.

Rysunek 4. Zakładka Plik w górnym menu Excela.

Rysunek 5. Przycisk Opcje, po kliknięciu Plik.

Rysunek 6. Okno z opcjami. Zakładka Zaawansowane i opcja pozwalająca dzielić i mnożyć liczby.

Gdy teraz będziesz ręcznie zapisywać w komórce liczby, to będą one odpowiednio dzielone lub mnożone. Wklejane liczby nie będą ulegały zmianie.

Ważne jest to, że tak zmienione liczby są faktycznie zmieniane. Czyli nie tylko widać w komórce liczbę 3 zamiast wpisanych 3000, ale Excel tak samo jak Ty, widzi tam 3.

Dzielenie liczb za pomocą dodatkowej kolumny

Jest to mało eleganckie rozwiązanie, ale jeśli ważniejsza dla Ciebie jest funkcjonalność i sprawność pracy niż elegancja, to zapoznaj się z nim. Oto jego kluczowe cechy:

• • Faktycznie będzie zmieniana wartość wprowadzanych komórek (nie tylko Ty będziesz widzieć tę zmianę, ale Excel także)
  • Możesz wskazać konkretne komórki, dla których liczby mają być przeliczane
  • Możesz wykonywać dowolne przeliczenia, więc nie tylko dzielić przez 1000, ale możesz dzielić np. przez 7,6 lub wykonywać dowolne inne obliczenia.

Dotychczas brzmi to dobrze, ale najgorsza część brzmi tak:

• • liczb nie wprowadzasz do tej właściwej kolumny, ale do kolumny pomocniczej. We właściwej kolumnie wprowadzasz wcześniej formułę, które odpowiednio przelicza liczby z kolumny pomocniczej.

Już pokazuję to na przykładzie. Powiedzmy, że w kolumnie A chcesz widzieć liczby przemnożone przez 1,23. W związku z tym, w kolumnie A wprowadzasz formułę, mnożącą liczby z kolumny B przez 1,23.

Rysunek 7. Formuła mnożąca liczbę z kolumny B przez 1,23.

Jeśli chcesz teraz zobaczyć prawidłową liczbę w kolumnie A, to musisz zapisać liczbę w kolumnie B.

Rysunek 8. Efekt wprowadzania liczb do kolumny B – w kolumnie A wyświetlają się liczby po przemnożeniu ich przez 1,23.

Możesz w taki sposób wprowadzać liczby do arkusza, a potem, gdy przychodzi moment podsumowania/zaraportowania to ukrywasz kolumnę B, żeby widać było tylko te właściwe liczby.

Dzielenie wprowadzanych liczb za pomocą makra

Jest to rozwiązanie dla cierpliwych bądź zaawansowanych użytkowników, bo opiera się o makro, czyli programowanie w VBA. Ja wszystko po kolei opisuję oraz podaję kod, niemniej jednak, wymaga to małej „gimnastyki”, aby takie makro u siebie wprowadzić. Oto istotne cechy tego rozwiązania:

• • Wprowadzana liczba będzie faktycznie zmieniana.
  • Możesz dokładnie określić, w których komórkach dzielenie (zmienianie) liczby ma mieć miejsce
  • Możesz na liczbie wykonać dowolne obliczenia, nie tylko dzielenie.
  • Nie potrzebujesz żadnych dodatkowych kolumn, ani narzędzi pomocniczych (oczywiście z wyjątkiem „zainstalowania” tego makra).
  • Trudnością jest sam proces „zainstalowania” makra.

No to do dzieła. Mamy pewien arkusz, w którym chcemy dzielić przez 100 liczby wprowadzone do komórek od A1 do B100. Wykonuj następujące polecenia:

• • Otwórz swój plik Excela.
  • Przejdź do opcji „Zapisz jako” aby zapisać go w formacie, który pozwala na używanie makr.
  • W oknie zapisywania wybierz jako „Typ” drugą z opcji, czyli „Skoroszyt programu Excel z obsługą makr”. Teraz plik będzie miał inne rozszerzenie, a więc też inną nazwę, więc jeśli inne pliki „zaciągają” coś z niego, to nie będą widziały tego nowego pliku i będzie trzeba dokonać odpowiednich zmian (tutaj nie opisuję, jak dokonać tych zmian).
• Rysunek 9. Zmiana typu pliku.
  • Mając otwarty plik, już w nowym formacie, użyj skrótu ALT + F11. Może być tak, że na Twojej klawiaturze trzeba użyć jeszcze klawisza funkcyjnego, czyli ALT + FN + F11.
  • Po lewej stronie powinno być okienko z nazwą Twojego pliku – naciśnij na nazwie swojego pliku dwa razy.
• Rysunek 10. Wybór projektu swojego pliku.
  • Dzięki temu, rozwinęło się więcej elementów. Naciśnij dwukrotnie na elemencie, który nazywa się jak arkusz, w którym makro ma działać.
• Rysunek 11. Wybór arkusza, w którym makro ma działać.
  • Po prawej stronie, w głównym okienku (które będzie puste) wybierz na pierwszej liście rozwijanej Worksheet, a na drugiej Change.
• Rysunek 12. Ustawienie Worksheet i Change.
  • Skasuj kod, który pojawi się w oknie głównym i wklej poniższy kod (przewiń nieco w dół stronę, aby zobaczyć kod). Gdy wklejasz kod, to nieważne są tzw. „tabulatory” po lewej stronie poszczególnych linijek (jeśli są takie same jak u mnie to ok, jeśli nie, to też nie ma problemu).

Rysunek 13. Wklejony kod makra.

• 
  • Dostosuj w poniższym kodzie elementy zaznaczone na czerwono, czyli adres komórek, w którym makro ma działać, oraz liczbę przez którą ma dzielić – obecnie ustawione są komórki od A1 do B100 i dzielenie przez 100.
  • Zamknij całe okno z VBA. Zapisz plik Excela.
  • Otwórz ponownie plik Excela – prawdopodobnie pojawi się komunikat z informacją, że w pliku mogą być makra i pytaniem czy zgadzasz się na ich uruchamianie. Naciśnij że Tak. Niestety za każdym uruchomieniem pliku trzeba wyrażać taką zgodę.
  • Jeśli chcesz domyślnie wyrażać zgodę na makra (bez klikania przycisku za każdym razem), to wykonaj poniższe operacje (uprzedzam, że dotyczyć to będzie wszystkich plików Excela, które otwierasz i stwarza to teoretycznie niebezpieczeństwo uruchomienia jakichś wirusowych makr, więc robisz to na własną odpowiedzialność).
    1. 1. Naciśnij przycisk PLIK
       2. Przycisk OPCJE
       3. Zakładka po lewej – CENTRUM ZAUFANIA
       4. Przycisk USTAWIENIA CENTRUM ZAUFANIA
       5. Po lewej – USTAWIENIA MAKR
       6. Zaznaczasz opcję WŁĄCZ WSZYSKIE MAKRA (niezalecane…)
• Rysunek 14. Ustawienia bezpieczeństwa.
  • Zamknij plik i uruchom go ponownie, aby zmiany weszły w życie.

Kod do wklejenia:

Private Sub Worksheet_Change(ByVal Target As Range)
    Set isect = Application.Intersect(Target, Range("A1:B100"))
    If isect Is Nothing Then
    Else
        If Target.Cells.Count = 1 Then
            If Target.Value <> "" Then
                Application.EnableEvents = False
                Target.Value = Target.Value / 100
                Application.EnableEvents = True
            End If
        Else
            Application.EnableEvents = False
            For Each c In Target.Cells
                If c.Value <> "" Then
                    c.Value = c.Value / 100
                End If
            Next c
            Application.EnableEvents = True
        End If
    End If
End Sub

Zapewne można ten kod zapisać „ładniej”, więc wybaczcie, jeśli znajdziecie jakieś „ubytki” w nim, ale skupiłem się na tym, żeby przede wszystkim działał i żeby cały proces „instalacji” makra dokładnie wytłumaczyć.