Jak pomnożyć kolumny w programie Excel: 4 metody

Wyobraź sobie arkusz kalkulacyjny, w którym w kolumnie A znajdują się ilości, a w kolumnie B ceny jednostkowe. Wypełnienie kolumny C sumami wydaje się banalne. Jeśli szukasz informacji o tym, jak pomnożyć kolumny w programie Excel, zazwyczaj właśnie to zadanie próbujesz wykonać. Wpisujesz więc formułę w pierwszej komórce, przeciągasz ją w dół, a połowa wyników okazuje się błędna. Drugi wiersz wygląda dobrze. Trzeci wiersz to nonsens, a w dwudziestym wierszu cała kolumna po cichu się rozpadła.

Większość ludzi obwinia za to siebie. W końcu formuła była poprawna. Problem wynika z tego, co Excel robi samoczynnie podczas kopiowania formuły, a prawie nikt tego nie wyjaśnia, zanim napotkasz tę przeszkodę.

Właśnie ta różnica — między odwołaniami bezwzględnymi a względnymi — sprawia, że działający wzór na mnożenie przestaje działać w połowie kolumny. W tym przewodniku omówiono cztery rzeczywiste sposoby mnożenia kolumn i wskazano dokładnie, gdzie każdy z nich zawodzi, dzięki czemu można wykryć błąd, zanim się rozprzestrzeni.

Gdy typowe porady po cichu zawodzą

Wyszukaj ten temat, a otrzymasz przejrzystą listę. Wpisz „ =A2*B2 ”, przeciągnij róg i gotowe. Użyj funkcji PRODUCT. Spróbuj opcji „Wklej specjalnie”. Wszystko poprawne, ale niekompletne.

Problem pojawia się, gdy kopiujesz formułę w dół. Program Excel domyślnie traktuje odwołania do komórek jako względne, co oznacza, że przesuwa je tak, aby pasowały do każdego nowego wiersza. Zazwyczaj jest to pożądane zachowanie podczas mnożenia dwóch sąsiadujących kolumn, ponieważ w wierszu 3 powinny być używane wartości z wiersza 3, a nie z wiersza 2.

Jednak w momencie, gdy jedna ze stron mnożenia to pojedyncza komórka – stawka podatkowa, współczynnik przeliczeniowy, stała cena – to automatyczne przesunięcie staje się błędem. Formuła w wierszu 2 odwołuje się do komórki ze stawką. Po skopiowaniu do wiersza 3 odwołuje się ona do komórki położonej o jedną w dół, która prawdopodobnie jest pusta. Program Excel mnoży przez zero i zwraca wynik zerowy lub błąd.

W poradnikach wymienia się różne metody, ale nigdy nie wspomina się o tym. W rezultacie czytelnik robi wszystko „jak trzeba”, obserwuje, jak kolumna się zawala, i nie ma pojęcia, który z czterech kroków to spowodował. Rozwiązanie to jeden znak. Trzeba tylko wiedzieć, gdzie go umieścić.

Jak pomnożyć kolumny w programie Excel: cztery metody, które naprawdę działają

Przeanalizujmy każde z tych podejść, zaczynając od tego, z którego będziesz korzystać najczęściej.

Metoda 1: Wzór z gwiazdką. W programie Excel nie ma osobnego przycisku do mnożenia, więc mnożenie odbywa się za pomocą gwiazdki. Aby pomnożyć dwie kolumny w stylu Excela, kliknij pierwszą pustą komórkę w kolumnie wynikowej i wpisz:

=A2*B2

Naciśnij klawisz Enter. W tej komórce znajduje się teraz iloczyn wartości z komórek A2 i B2. Jest to podstawowy element niemal każdej z poniższych metod, a na stronach pomocy technicznej firmy Microsoft traktuje się go jako domyślny element składowy służący do mnożenia kolumny liczb.

Metoda 2: Wypełnij w dół. Jedna formuła to nie kolumna. Aby pomnożyć całą kolumnę, program Excel wymaga, aby ta formuła była powtórzona w każdym wierszu, a ręczne przeciąganie szybko się nudzi.

Zaznacz komórkę zawierającą formułę. Widzisz mały kwadrat w prawym dolnym rogu? To jest uchwyt wypełniania. Kliknij go dwukrotnie, a program Excel skopiuje formułę w dół, aż do końca sąsiednich danych. Jeśli chcesz mieć większą kontrolę, przeciągnij ją ręcznie. Niezależnie od tego, w jaki sposób to zrobisz, program Excel dostosuje odwołania w każdym wierszu, więc =A2*B2 zamieni się w =A3*B3, następnie w =A4*B4 i tak dalej. Jak wskazuje samouczek serwisu DataCamp dotyczący mnożenia komórek i kolumn, jest to najszybszy sposób w przypadku dwóch dopasowanych kolumn.

=PRODUCT(A2,B2)

Lub w całym zakresie:

=PRODUCT(A2:D2)

Funkcja PRODUCT mnoży wszystkie wartości, które jej podasz. Przydaje się, gdy w wierszu znajduje się cztery lub pięć czynników, a nie chcesz łączyć gwiazdek. Ponadto ignoruje puste komórki i tekst, zamiast generować błąd, czego zwykła gwiazdka nie zawsze jest w stanie dokonać. Wpisz ją w dół w taki sam sposób, jak każdą inną formułę.

Metoda 4: Wklej specjalnie – mnożenie. Pierwsze trzy opcje zapisują wyniki w nowej kolumnie. Opcja „Wklej specjalnie” działa inaczej — zmienia istniejące liczby w miejscu, w którym się znajdują, więc trzecia kolumna nie jest potrzebna.

Załóżmy, że kolumna A zawiera ceny i chcesz każdą z nich pomnożyć przez stały współczynnik. Wpisz ten współczynnik w pustą komórkę, skopiuj go, zaznacz kolumnę z cenami, a następnie otwórz menu „Wklej specjalnie” (Ctrl+Alt+V), wybierz opcję „Pomnóż” w sekcji „Operacje” i kliknij „OK”. Program Excel pomnoży każdą zaznaczoną komórkę przez skopiowaną wartość i nadpisze jej zawartość. Poradnik serwisu How-To Geek dotyczący mnożenia kolumn w programie Excel dobrze opisuje tę procedurę.

Jest jednak pewien haczyk: funkcja „Wklej specjalnie” ma charakter destrukcyjny. Nie pozostawia ona żadnej formuły, więc jeśli współczynnik ulegnie później zmianie, trzeba będzie wszystko robić od nowa. Należy jej używać do jednorazowych korekt, a nie w arkuszach kalkulacyjnych, które podlegają ciągłym zmianom.

Problem ze znakiem dolara – rzetelne wyjaśnienie

To właśnie ta część, którą pomijają inne poradniki, a to właśnie ona jest przyczyną uszkodzenia twojej kolumny.

Chcesz pomnożyć zawartość kolumny przez stałą liczbę, na przykład stawkę podatkową znajdującą się w komórce E1. Wpisujesz „ =A2*E1 ”, otrzymujesz prawidłowy wynik i wypełniasz w dół. Wiersz 2 jest poprawny. Wszystko poniżej niego jest błędne.

Zwróć uwagę na to, jak działa Excel. W wierszu 3 formuła niepostrzeżenie zmienia się w =A3*E2. Część „A3” jest w porządku — to przesunięcie względne działa zgodnie z zamierzeniami. Jednak komórka E1 również uległa przesunięciu do komórki E2, która jest pusta. W wierszu 4 wykorzystywana jest komórka E3. Każdy wiersz mnoży się teraz przez pustą komórkę.

Odwołanie do komórki zawierającej stawkę musi pozostać na swoim miejscu niezależnie od tego, gdzie zostanie skopiowana formuła. Tę rolę pełni znak dolara. Zablokowanie go wygląda następująco:

=A2*$E$1

Symbol „ $ ” przed literą kolumny powoduje zablokowanie tej kolumny. Symbol „ $ ” przed numerem wiersza powoduje zablokowanie tego wiersza. Dzięki funkcji „ $E$1 ” program Excel w każdym wierszu wskazuje komórkę E1, podczas gdy komórka A2 nadal przesuwa się do A3, A4 i w dół. Jedna część się przesuwa, a druga jest zablokowana — właśnie tego potrzebuje kolumna ze stałym mnożnikiem.

Warto zapamiętać jeden skrót: kliknij odwołanie do komórki w pasku formuły i naciśnij klawisz F4. Program Excel przełącza się kolejno między opcjami blokady — E1, $E$1, E$1, $E1 — dzięki czemu nigdy nie musisz ręcznie wpisywać znaków dolara.

Która metoda do jakiego zadania

Dwie dopasowane kolumny, ilość pomnożona przez cenę? Formuła z gwiazdką, wklej w dół. Pomnożenie przez jedną stałą komórkę? To samo podejście, ale najpierw zablokuj tę komórkę za pomocą opcji „ $ ”. Kilka czynników w każdym wierszu oznacza funkcję PRODUCT. A jednorazowa podwyżka ceny, do której już nigdy nie wrócisz, to zadanie dla funkcji „Wklej specjalnie”.

Rozwiązywanie typowych problemów

Niektóre błędy powtarzają się wielokrotnie.

Błędy typu #VALUE! prawie zawsze oznaczają, że jedna z komórek zawiera tekst lub liczbę zapisaną jako tekst z przypadkową spacją lub symbolem waluty. Oczyść komórkę źródłową lub przełącz się na funkcję PRODUCT, która pomija wpisy nieliczbowe, zamiast generować błąd.

Zera w miejscach, gdzie spodziewałeś się liczb, wskazują bezpośrednio na problem ze znakiem dolara. Kliknij formułę w uszkodzonym wierszu i sprawdź, czy nie zmieniło się stałe odwołanie. Jeśli gdzieś adres $E$1 zmienił się w E2, oznacza to, że skopiowałeś formułę w dół bez jej zablokowania.

Jeśli wyniki się nie aktualizują, zazwyczaj oznacza to, że tryb obliczeń został przełączony na ręczny. Przejdź do sekcji „Formuły”, a następnie „Opcje obliczeń” i ustaw ponownie opcję „Automatycznie”. Zdarza się to rzadko, ale może mieć miejsce po otwarciu plików od innych osób.

Kolumna zawierająca formuły wyświetlane jako tekst oznacza, że format komórki jest ustawiony na „Tekst”, więc program Excel odczytuje wyrażenie „ =A2*B2 ” jako dosłowny ciąg znaków. Zmień format na „Ogólny” lub „Liczba”, a następnie ponownie wprowadź formułę.

Jeśli chcesz zapoznać się z większą liczbą przykładów, na stronie GeeksforGeeks znajdziesz wyczerpujące omówienie tych skrajnych przypadków w artykule poświęconym mnożeniu liczb, komórek i kolumn.

Najczęściej zadawane pytania

Dlaczego mój wzór działa w pierwszym wierszu, ale nie w kolejnych?
Mnożysz przez stałą komórkę, która nie jest zablokowana. Program Excel przesuwa odwołanie w dół podczas wypełniania, przez co po pierwszym wierszu przestaje ono wskazywać właściwą komórkę. Dodaj znaki dolara ( $E$1) i ponownie wypełnij.

Jaka jest różnica między funkcjami ` =A2*B2 ` a ` =PRODUCT(A2,B2)`? `
` W przypadku dwóch komórek nie ma żadnej różnicy — zwracają ten sam wynik. Funkcja `PRODUCT` sprawdza się, gdy mamy do czynienia z kilkoma czynnikami lub zakresem, i grzecznie pomija puste komórki oraz komórki zawierające tekst, zamiast generować błąd.

Jak pomnożyć całą kolumnę bez przeciągania?
Wpisz formułę w pierwszej komórce, a następnie kliknij dwukrotnie uchwyt wypełniania znajdujący się w prawym dolnym rogu komórki. Program Excel automatycznie skopiuje ją w dół, dostosowując długość do długości danych.

Czy mogę pomnożyć zawartość dwóch kolumn bez tworzenia trzeciej kolumny?
Tak, można to zrobić za pomocą opcji „Wklej specjalnie: mnożenie”, ale powoduje to nadpisanie pierwotnych liczb i nie pozostawia żadnej formuły. To dobre rozwiązanie w przypadku jednorazowej zmiany, ale ryzykowne, jeśli chodzi o dane, które będziesz później edytować.

Jaką funkcję pełni F4 w formule?
Funkcja ta przełącza odwołanie między względnym a bezwzględnym. Kliknij odwołanie do komórki na pasku formuły, naciśnij klawisz F4, a program Excel będzie cyklicznie przełączał między opcjami „ E1 ”, „ $E$1 ”, „ E$1 ” i „ $E1 ”, co pozwoli Ci uniknąć ręcznego wpisywania znaków dolara.

W skrócie

Cztery metody, jedna powtarzająca się pułapka. Formuła z gwiazdką i przeciągnięcie w dół pozwalają pomnożyć większość kolumn, funkcja PRODUCT porządkuje wiersze z wieloma czynnikami, a opcja „Wklej specjalnie” dostosowuje wartości w miejscu. Oto jak pomnożyć kolumny w programie Excel, nie dopuszczając do uszkodzenia formuły w połowie operacji. Gdy jedna strona równania stanowi pojedyncza stała komórka, przed skopiowaniem formuły w dół należy ją zablokować za pomocą opcji „ $ ”. Ten jeden znak stanowi całą różnicę między kolumną, która działa poprawnie, a kolumną, która psuje się już w drugim wierszu.

Aby program Excel działał prawidłowo, trzeba przede wszystkim posiadać legalną, aktywowaną kopię pakietu Office — bez irytujących pasków z przypomnieniami i bez ograniczeń dostępu do funkcji.