Сегодня я хотел бы поделиться с вами небольшой хитростью, как правильно исправить значение в формуле Excel. К сожалению, очень немногие пользователи используют такую удобную функциональность электронных таблиц, и это позор. Многие часто сталкивались с ситуацией, когда необходимо изменить или скопировать формулы, но вот незадача, адреса ячеек тоже ушли «влево» и результат получить не удалось. А получить желаемый результат нам поможет доллар, а точнее знак «$», вот и все, это самое главное условие фиксации значения в ячейках.
Итак, рассмотрим подробнее все варианты того, как устроена клетка. Есть три варианта фиксации:
- Полная фиксация клетки;
- Расположение формулы в Excel вертикально;
- Закрепление формул по горизонтали.
Полная фиксация ячейки
Полная фиксация ячейки — это когда значение фиксируется по вертикали и горизонтали (например, $A$1), здесь значение не может никуда перемещаться, так называемая абсолютная формула. Очень удобно использовать эту опцию, когда вам нужно сослаться на значение в ячейке, такое как обменный курс, постоянный уровень минимальной заработной платы, расход топлива и т д.
В примере у нас есть товар и его стоимость в рублях, но нам нужно знать, стоит ли он в многолетних долларах. Так как у нас есть постоянная ячейка D1, в которой может меняться сам курс валюты в зависимости от экономической ситуации в стране. Сам диапазон расчета от Е4 до Е7. Когда мы набираем формулу =D4/D1 в ячейку Е4, то в результате копирования ячейки изменят направление и сместятся вниз, пропуская столь необходимый тип сдвига. Но если мы внесем изменения и зафиксируем значение в формуле простым знаком доллара, то получим следующий результат =D4/$D$1 и в этом случае, сдвинув и скопировав формулу, получим нужный нам результат во всех ячейки диапазона;
Фиксация формулы в Excel по вертикали
Вертикальное частичное исправление (пример $A1), это просто фиксированные столбцы, возможность изменения формулы частично сохранена, но только по горизонтали (в строке). Как вы можете видеть на скриншоте или в файле примера, который вы скачали.
Фиксация формул по горизонтали
Следующая булавка будет горизонтальной (например, A$1). И все правила остаются в силе как и в предыдущем пункте, но немного наоборот. Рассмотрим этот пример более подробно. У нас есть товар, который продается в разных городах и имеет разный процент наценки, и нам нужно рассчитать, с какой наценкой и где мы его получим. В диапазоне K1:M1 мы ставим процент наценки и эти ячейки необходимо настроить для автоматического расчета. Диапазон для написания формул — K4:M7, здесь нам нужно получить результаты одним щелчком мыши, просто правильно набрав формулу. Растянув формулу по диагонали, мы должны задать диапазон процентной ставки (по горизонтали) и диапазон стоимости товара (по вертикали). Потом.
При выполнении таких расчетов очень легко и быстро можно пересчитать множество вариантов, изменив всего 1 цифру. В файле примера вы можете убедиться в этом, изменив общий обменный курс или региональные проценты. И такие расчеты будут в разы быстрее, чем другие варианты написания формул в Excel. Но необходимость в этом надо видеть исходя из вашей текущей задачи и установка значения в ячейках находится в ключевых местах.
Чтобы не менять постоянно раскладку клавиатуры при наборе знака «$» для фиксации значения в формуле можно использовать «горячую» клавишу F4. Если курсор находится в адресе ячейки, при нажатии на столбцы и строки автоматически добавляется знак «$». При повторном нажатии она будет добавлена только для столбцов, при повторном нажатии она будет добавлена только для строк, а 4 щелчка удалят все исправления, формула вернется в исходный вид.
Скачать пример можно здесь.
И это все, что у меня есть! Я очень надеюсь, что вы разобрались со всеми вариантами того, как можно исправить ячейку в формуле. Буду очень благодарен за любой оставленный вами отзыв, так как это показатель читабельности и вдохновляет меня на написание новых статей! Делитесь с друзьями, читайте и ставьте лайки!
Не забудьте поблагодарить автора!
Деньги — нерв войны.
Марк Туллий Цицерон
Очень часто в Excel требуется заморозить (заморозить) конкретную ячейку в формуле. По умолчанию ячейки автоматически перетаскиваются и изменяются в размере. Посмотрите на этот пример.
У нас есть данные о количестве проданной продукции и цене за 1 кг, необходимо автоматически рассчитать доход.
Для этого запишем в ячейку D2 формулу =B2*C2
Если мы перетащим формулу дальше вниз, она автоматически переключится на соответствующие ячейки. Например, в ячейке D3 будет формула =B3*C3 и так далее. В этом смысле нам не нужно постоянно прописывать одну и ту же формулу, просто тащите ее вниз. Но бывают ситуации, когда нам нужно зафиксировать (исправить) формулу в ячейке, чтобы она не смещалась при перетаскивании.
Взгляните на этот пример. Предположим, нам нужно рассчитать доход не только в рублях, но и в долларах. Курс доллара указан в ячейке В7 и составляет 35 рублей за 1 доллар. Для расчета в долларах нужно доход в рублях (столбец Г) разделить на ставку в долларах.
Если записать формулу как в предыдущем варианте. В ячейке E2 набираем =D2*B7 и тянем формулу вниз, тогда ничего не получится. По аналогии с предыдущим примером, в ячейке E3 формула изменится на =E3*B8; Как видите, первая часть формулы у нас изменилась как положено Е3, но и долларовая ячейка изменилась на В8, и в этой ячейке ничего не указано. Поэтому нам нужно исправить ссылку на ячейку с курсом доллара в формуле. Для этого нужно указать знаки доллара и формула в ячейке E3 будет иметь вид =D2/$B$7, теперь если мы растянем формулу ссылка на ячейку B7 не сдвинется, а все в ней не фиксированное изменится как нам нужно.
Примечание. В этом примере мы указываем два знака доллара $B$7. Поэтому мы сказали Excel заморозить и столбец B, и строку 7, бывают случаи, когда нам нужно заморозить только один столбец или только одну строку. В этом случае знак $ ставится только перед столбцом или строкой B$7 (фиксируется строка 7) или $B7 (фиксируется только столбец B)
Формулы, содержащие знаки доллара в Excel, называются абсолютными (они не меняются при перетаскивании), а формулы, изменяющиеся при перетаскивании, называются относительными.
Чтобы не набирать знак доллара вручную, можно поставить курсор в формуле в ячейке E2 (выделить текст B7) и затем нажать на клавиатуре клавишу F4, Excel автоматически исправит формулу, поставив доллар перед столбец и строка если еще раз нажать клавишу F4, то только столбец зафиксируется, еще раз только строка, еще раз все вернется в исходный вид.
Формула сообщает Excel, что делать с числами или значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.
В конструкцию формулы входят: константы, операторы, ссылки, функции, имена диапазонов, круглые скобки, содержащие аргументы, и другие формулы. На примере обсудим практическое применение формул для начинающих пользователей.
Формулы в Excel для чайников
Чтобы задать формулу для ячейки, необходимо ее активировать (поставить курсор) и ввести равно (=). Вы также можете ввести знак равенства в строке формул. После ввода формулы нажмите Enter. Результат расчета появится в ячейке.
Excel использует стандартные математические операторы:
| Оператор | Операция | Пример |
| + (подробнее) | Добавление | =В4+7 |
| — (меньше) | Вычитание | =А9-100 |
| * (звездочка) | Умножение | =А3*2 |
| / (косая черта) | Разделение | =А7/А8 |
| ^ (циркумфлекс) | Степень | =6^2 |
| = (знак равенства) | Так же | |
| Меньше | ||
| > | Плюс | |
| Меньше или равно | ||
| >= | Больше или равно | |
| Это не то же самое |
При умножении обязательно используется символ «*». Опускать его, как это принято при письменных арифметических вычислениях, недопустимо. То есть запись (2+3) 5 Excel не поймет.
Excel можно использовать как калькулятор. То есть ввести в формулу числа и математические операторы расчета и сразу получить результат.
Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, на значение которой будет действовать формула.
При изменении значений в ячейках формула автоматически пересчитывает результат.
Ссылки можно комбинировать в одной формуле с простыми числами.
Оператор умножил значение в ячейке B2 на 0,5. Чтобы ввести ссылку на ячейку в формулу, просто нажмите на эту ячейку.
В нашем примере:
- Поместите курсор в ячейку B3 и введите =.
- Щелкнули по ячейке В2 — Excel ее «обозначил» (в формуле появилось название ячейки, вокруг ячейки образовался «мигающий» прямоугольник).
- Вводим с клавиатуры знак *, значение 0.5 и нажимаем ENTER.
Если в формуле используется несколько операторов, программа будет обрабатывать их в следующей последовательности:
- %, ^;
- *, /;
- +, -.
Вы можете изменить последовательность, используя круглые скобки: Excel сначала вычисляет значение выражения в круглых скобках.
Как в формуле Excel обозначить постоянную ячейку
Существует два типа ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: меняются относительные, абсолютные остаются постоянными.
Программа считает все ссылки на ячейки относительными, если пользователь не укажет другое условие. Относительные ссылки позволяют дублировать одну и ту же формулу в нескольких строках или столбцах.
- Вручную заполните первые столбцы обучающей таблицы. У нас есть такой вариант:
- Помните из математики: чтобы найти стоимость нескольких единиц продукта, вы умножаете цену 1 единицы на количество. Для расчета стоимости введите в ячейку D2 формулу: = цена за единицу * количество. Константы формулы — это ссылки на ячейки с соответствующими значениями.
- Нажимаем ENTER — программа выводит значение умножения. Такие же манипуляции необходимо произвести для всех ячеек. Как установить формулу для столбца в Excel: Скопируйте формулу из первой ячейки в другие строки. Относительные ссылки помогают.
Находим маркер автозаполнения в правом нижнем углу первой ячейки столбца. Нажмите на эту точку левой кнопкой мыши, удерживайте ее и перетащите вниз по столбцу.
Отпустите кнопку мыши — формула будет скопирована в выделенные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.
Звенья в ячейке связаны с цепью.
Формула абсолютной ссылки относится к той же ячейке. То есть при автодополнении или копировании константа остается неизменной (или константой).
Чтобы указать Excel на абсолютную ссылку, пользователь должен поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.
- Создадим строку «Итого». Найдите общую стоимость всех товаров. Выберите числовые значения из столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
- Воспользуемся функцией автозаполнения. Кнопка находится на вкладке Главная в группе инструментов редактирования».
- После нажатия значка «Сумма» (или комбинации клавиш ALT + «=») выбранные числа складываются вместе, и результат отображается в пустой ячейке.
Сделаем еще одну колонку, где посчитаем долю каждого товара в общей стоимости. Для этого вам нужно:
- Разделите стоимость одного предмета на стоимость всех предметов и умножьте результат на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы она оставалась неизменной при копировании.
- Чтобы получить проценты в Excel, необязательно умножать частное на 100. Выделите ячейку с результатом и нажмите «Форматировать проценты». Или нажмите комбинацию горячих клавиш: CTRL+SHIFT+5
- Копируем формулу на весь столбец: меняется только первое значение формулы (относительная ссылка). Вторая (абсолютная ссылка) остается прежней. Проверим правильность расчетов: найдем результат сто%. Все правильно.
При создании формул используются следующие форматы абсолютной ссылки:
- $B$2 — при копировании столбец и строка остаются постоянными;
- B$2 — при копировании строка не меняется;
- $B2: столбец не изменяется.
Как составить таблицу в Excel с формулами
Для экономии времени при вводе однотипных формул в ячейки таблицы используются маркеры автозаполнения. Если вам нужно исправить ссылку, сделайте ее абсолютной. Для изменения значений при копировании относительной ссылки.
Самые простые формулы для заполнения таблиц в Excel:
- Давайте вставим еще один столбец перед названиями продуктов. Выберите любую ячейку в первом столбце, щелкните правой кнопкой мыши. Нажмите «Вставить». Или сначала нажмите комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А затем комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
- Назовем новый столбец «Нет п/п». Введите «1» в первую ячейку и «2» во вторую. Выберите первые две ячейки: «вставьте» маркер автозаполнения левой кнопкой мыши, перетащите его вниз.
- По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковы: день, месяц, год. Вводим «15 октября» в первую ячейку и «15 ноября» во вторую. Выберите первые две ячейки и «растяните» маркер вниз.
- Найдите среднюю цену товара. Выберите столбец с ценами + еще одна ячейка. Откройте меню кнопки «Сумма»: выберите формулу для автоматического расчета среднего значения.
Чтобы проверить правильность вставленной формулы, дважды щелкните ячейку с результатом.
При работе с электронными таблицами Excel 2010, 2013, 2016 иногда необходимо следить за тем, чтобы при перемещении формулы по ячейкам ссылка оставалась неизменной. В связи с этим многие пользователи сталкиваются с неудобствами во время работы, однако выход из этой ситуации есть. В таблице Excel есть специальная активная кнопка для фиксации, как ее активировать будет рассмотрено в этой статье.
Как зафиксировать ячейку в формуле в таблицах Excel – вариант №1
Чтобы убедиться, что символ столбца и номер строки не изменились при перемещении формулы, вам нужно сделать несколько вещей. Сначала щелкните ячейку, которую в данный момент нужно исправить, чтобы она стала выделенной. После этого в таблице формул нажмите на ссылку той ячейки, которую необходимо исправить. После этого нужно один раз нажать на клавиатуре кнопку F4.
В результате ссылка будет зафиксирована со знаком $ (знак доллара). Например, если в вашей формуле было значение C3, после выполнения описанной выше процедуры ссылка должна иметь следующий формат: $C$3.
Знак $ перед символом будет означать, что при перемещении формулы, перемещении ее по столбцам ссылка не изменится. Второй знак $, расположенный после символа и, соответственно, перед цифрами, будет означать, что при перемещении формулы по строкам ссылка не изменится.
Как закрепить ячейку в формуле в Excel – вариант №2
Этот метод почти такой же, как и предыдущий, однако здесь вам нужно нажать F4 дважды, а не один раз. Так можно зафиксировать ячейку в формуле так, чтобы при ее перемещении символ в столбце менялся, а число в строке — нет.
Как в Экселе закрепить ячейку в формуле – вариант №3
В этом случае проделываем все те же действия, что и в способе номер один, однако клавишу F4 необходимо нажать трижды. В этом случае перед символом будет вставлен только один символ подтверждения. Теперь, когда вы перемещаете формулу по строкам, числа будут меняться, а по мере перемещения по столбцам значение будет одинаковым.
Как отменить действия фиксации ячейки в формуле в таблицах Excel
Если по какой-то причине в таблицах Excel 2016, 2013, 2010 необходимо отвязать определенную ячейку, это можно сделать легко. Для этого щелкните по формуле левой кнопкой мыши, чтобы она была выделена. Затем нажмите F4 столько раз, сколько необходимо, пока не исчезнут все знаки доллара.