Mieszane odniesienia w Excelu (spis treści)
- Wprowadzenie do mieszanego odniesienia w Excelu
- Przykłady mieszanego odniesienia w Excelu
Wprowadzenie do mieszanego odniesienia w Excelu
Odwołanie jest jedną z ważnych funkcji programu Excel, która pomaga podczas pracy z operacjami programu Excel za pomocą formuł. Mieszane odniesienia, sama nazwa mówi, że jest to kombinacja odniesień. Połączenie obejmuje odniesienie względne i odniesienie bezwzględne.
Należy zrozumieć odniesienia względne i bezwzględne, aby zrozumieć i pracować z odniesieniem mieszanym. Najpierw zobaczymy, jakie jest odniesienie względne, a co absolutne, jak możemy go użyć w formułach, a wszyscy zobaczą z pokrewnymi przykładami.
Przykłady mieszanego odniesienia w Excelu
Oto przykłady pracy z odniesieniem względnym i bezwzględnym z odniesieniem mieszanym w programie Excel podane poniżej.
Możesz pobrać ten mieszany szablon Excel tutaj - Mieszany szablon ExcelPrzykład 1 - odniesienie względne
Domyślnie odniesienie w programie Excel będzie traktować je jako odniesienie względne. Odniesienie jest niczym, jeśli wprowadzimy „= A1” w komórce B1, to odniesiemy A1 w B1. Zobaczmy przykład, aby zrozumieć odniesienie względne.
Rozważ poniższy zrzut ekranu zarobków i wydatków od stycznia do maja.
Teraz chcemy obliczyć gotówkę pozostałą po wydatkach wypłaconych z zarobków. Możemy to uzyskać za pomocą prostej formuły odejmowania wydatków od zarobków. Wystarczy wprowadzić formułę obok wydatków, aby znaleźć pozostałą gotówkę każdego miesiąca.
Obserwuj poniższy zrzut ekranu Wprowadziłem formułę obok wydatków, które można znaleźć na pasku formuły.
Jeśli zaobserwujemy, że wzór w D2 to B2-C2, oznacza to, że D2 odnosi się do kombinacji B2 i C2. Kiedy przeciągniemy formułę do dolnych linii, zajmie to podobnie jak D2, co oznacza, że dla D2 wziął referencję z B2 i C2 podobnie dla D3, to odniesie się do B3 i C3, dla D4 odniesie się do B4 i C4 i tak dalej.
Możemy obserwować powiązane zrzuty ekranu z innych miesięcy. Poniższy zrzut ekranu dotyczy D3, który odnosi się do B3 i C3.
Zrzut ekranu związany z D4, który dotyczy B4 i C4.
Oznacza to, że formuła zawsze będzie traktować to samo odniesienie jako takie samo jak pierwsza formuła, ponieważ odnosi się do tego. Mam nadzieję, że masz pomysł, co jest względnym odniesieniem, w którym możemy użyć.
Przykład 2 - Absolutne odniesienie
Rozważ ten sam przykład, aby zrozumieć również odniesienie bezwzględne. Rozważmy z powyższego zrzutu ekranu, że mamy zarobki i wydatki, załóżmy, że mamy ustalony koszt czynszu, dodatkowo wydatki dostępne w tabeli.
W powyższym programie wspominaliśmy czynsz tylko w jednej komórce, czyli F2. Teraz musimy zmienić formułę w kolumnie D, aby odjąć czynsz od „gotówki pozostałej po wydatkach”.
Obserwuj zrzut ekranu, który odjęliśmy F2 od wyniku B2-C2. Przeciągnijmy formułę również do innych komórek.
Przeciągnąłem formułę, ale nadal wyniki się nie zmieniają, ponieważ w komórce D2 odjęliśmy F2, jak wcześniej wyjaśniono domyślnie, Excel weźmie odniesienie względne, więc zajęło F3 dla formuły w D3 podobnie F4 w D4. Ale nie mamy żadnych wartości w komórkach F3, F4, F5 i F6, dlatego uważa te komórki za zero i nie wpływało na wyniki.
Jak możemy to rozwiązać bez przeciągania czynszu do komórek F3, F4, F5 i F6. Tutaj absolutne odniesienie pomaga osiągnąć prawidłowe wyniki bez przeciągania czynszu.
Bardzo łatwo jest utworzyć bezwzględne odwołanie, wystarczy kliknąć formułę i wybrać komórkę F2 i kliknąć D2, a następnie odniesienie bezwzględne zostanie utworzone.
Jeśli $ zostanie zastosowane zarówno dla indeksu kolumny F, jak i indeksu wiersza 2, wówczas odniesienie bezwzględne będzie na poziomie komórki. Teraz przeciągnij formułę w D3, D4, D5 i D6 i zobacz, jak zmieniają się wyniki.
Zauważ, że formuła w komórce D3 to $ F $ 2, nawet po przeciągnięciu formuły. Ponieważ jest zablokowany na poziomie komórki, będzie pobierał referencje tylko z F2. To jest użycie bezwzględnego odniesienia. Spowoduje to pobranie referencji z zablokowanego indeksu.
Przykład 3 - Mieszane odniesienie
Jak wspomniano wcześniej w referencji mieszanej, użyjemy zarówno referencji względnej, jak i referencji bezwzględnej. Zobaczmy, jak możemy korzystać z obu na tym samym przykładzie.
Rozważ dane w poniższym formacie, jak pokazano na zrzucie ekranu.
Z powyższej tabeli musimy znaleźć procent zarobków każdego miesiąca w stosunku do „Ogółem” zarobków z 5 miesięcy podobnie, odsetki wydatków i gotówkę pozostałą po wydatkach.
Jeśli nadal nie jest jasne, musimy dowiedzieć się 5000 procent z całkowitych zarobków 5 miesięcy 32100.
Teraz w poniższej pustej tabeli musimy znaleźć procent dla każdej komórki.
Wzór na wyliczenie procentu jest podzielony na zarobki w styczniu z sumą 32100.
Jeśli zauważysz, użyłem bezwzględnego odniesienia, ponieważ zarobki za cały miesiąc powinny być dzielone tylko przez sumę. Teraz możemy przeciągnąć formułę do maja.
Teraz przeciągnij formuły również na dół.
Jeśli nadal będziemy obserwować, formuła wydatków uwzględnia wyłącznie Zysk, ale w rzeczywistości powinna ona kosztować łącznie 15200. W tym celu musimy utworzyć referencję na poziomie kolumny, która nie jest wymagana na poziomie wiersza.
Zauważ, że wzór $ symbol jest dostępny w N, a nie w indeksie wiersza 11. Wprowadź zmiany zawsze w pierwszej formule, a następnie przeciągnij do innych komórek.
Wszędzie formuła wybiera się poprawnie. Jeśli zaobserwujemy, wykonaliśmy odniesienie bezwzględne w operacjach wierszowych i odniesienie względne w operacjach kolumnowych. Mam nadzieję, że teraz jest jasne, jak wykonać mieszane odniesienie i w jakich obszarach będzie to przydatne.
Rzeczy do zapamiętania
- Odniesienie mieszane jest kombinacją odniesienia względnego i odniesienia bezwzględnego.
- Odwołanie względne jest domyślnym odniesieniem w programie Excel podczas odwoływania się do dowolnych komórek w operacjach programu Excel. W tej drugiej komórce zostanie zastosowane podobne odwołanie jak w pierwszej formule.
- Odwołanie bezwzględne to kolejny typ, w którym odniesienie zostanie ustalone jako stałe w każdym miejscu wklejenia formuły.
- Aby utworzyć bezwzględne odwołanie, wybierz odwołanie do komórki i kliknij F4, a następnie symbol $ doda do indeksu wierszy i indeksu kolumny.
- Odniesienie mieszane jest kombinacją obu, dlatego w tym przypadku możemy zastosować odniesienie bezwzględne do samego indeksu kolumny lub samego indeksu wiersza.
- $ A1 - Oznacza odniesienie utworzone dla indeksu kolumny.
- $ 1 - Odwołanie do środka utworzone dla indeksu wierszy.
- $ A $ 1 - Oznacza odwołanie utworzone dla indeksów wierszy i kolumn.
Polecane artykuły
Jest to przewodnik po mieszanym podręczniku w programie Excel. Tutaj omawiamy, jak działa odniesienie względne i bezwzględne z mieszanym odniesieniem w programie Excel wraz z przykładami i szablonem programu Excel do pobrania. Możesz także przejrzeć następujące artykuły, aby dowiedzieć się więcej -
- Formuła DZIEŃ w Excelu
- Formuła wielkości próbki
- HLOOKUP Formula w Excelu
- WARTOŚĆ Formuła w programie Excel