Skip to content
При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только одной цели — он указывает, следует ли изменять ссылку при копировании. И это короткое руководство предоставляет полную информацию о том, какими способами можно закрепить адрес ячейки, чтобы он не менялся при копировании формулы.
Если вы создаете формулу только для одной клетки вашей таблицы Excel, то проблема как зафиксировать ячейку вас не волнует. А вот если её нужно копировать или перемещать по таблице, то здесь-то и скрываются подводные камни. Чтобы не сломать расчеты, некоторые ячейки следует зафиксировать в формулах, чтобы их адреса уже не менялись.
Как упоминалось ранее, относительные ссылки на ячейки являются основными по умолчанию для любой формулы, созданной в Excel. Но их главная особенность — изменение при копировании и перемещении. Во многих же случаях необходимо зафиксировать адрес ячейки в формуле, чтобы не потерять эту ссылку при изменении таблицы. Ниже мы рассмотрим следующие способы:
- Как зафиксировать ячейку вручную.
- Использование функциональной клавиши.
- Выборочная фиксация по строке или столбцу.
- Закрепите адрес ячейки при помощи имени.
Чтобы предотвратить изменение ссылок на ячейку, строку или столбец, используют абсолютную адресацию , которая отличается тем, что перед координатой строки или столбца ставится знак доллара $.
Поясним на простом примере.
=A1*B1
Здесь используются относительные ссылки. Если переместить это выражение на 2 ячейки вниз и 2 вправо, то мы увидим уже
=C3*D3
На 2 позиции изменилась буква столбца и на 2 единицы – номер строки.
Если в ячейке A1 у нас записана информация, которую нам нужно использовать во многих клетках нашей таблицы (например, курс доллара, размер скидки и т.п.), то желательно зафиксировать ее, чтобы ссылка на ячейку A1 никогда не «сломалась»:
=$A$1*B1
В результате, если мы повторим предыдущую операцию, то получим в результате формулу
=$A$1*D3
Ссылка на A1 теперь не относительная, а абсолютная. Более подробно об относительных и абсолютных ссылках вы можете прочитать в этой статье на нашем блоге.
В этом и состоит решение проблемы фиксации ячейки — нужно превратить ссылку в абсолютную.
А теперь рассмотрим подробнее, какими способами можно закрепить ячейку, строку или столбец в формуле.
Как вручную зафиксировать ячейку в формуле.
Предположим, у нас уже имеется формула в одной из клеток нашей таблицы.
В ячейке D2 подсчитаем сумму скидки:
=B2*F2
Записывать подобный расчет для каждого товара — хлопотно и нерационально. Хочется скопировать его из C2 вниз по столбцу. Но при этом ссылка на F2 не должна измениться. Иначе наши расчеты окажутся неверными.
Поэтому ссылку на ячейку F2 в нашем расчёте нужно каким-то образом зафиксировать, чтобы предотвратить ее изменение. Для этого мы при помощи знаков $ превратим ее из относительной в абсолютную.
Самый простой выход – отредактировать C2, для чего можно дважды кликнуть по ней мышкой, либо установить в нее курсор и нажать функциональную клавишу F2.
Далее при помощи курсора и клавиатуры вставляем в нужные места знак $ и нажимаем Enter. Получаем:
=B2*$F$2
Другими словами, использование $ в ссылках на ячейки делает их фиксированными и позволяет перемещать формулу в Excel без их изменения. Вот теперь можно и копировать, как показано на скриншоте ниже.
Фиксируем ячейку при помощи функциональной клавиши.
Вновь открываем ячейку для редактирования и устанавливаем курсор на координаты нужной нам ячейки.
Нажимаем функциональную клавишу F4 для переключения вида ссылки.
Неоднократно нажимая F4, вы будете переключать ссылки в следующем порядке:
Для того, чтобы зафиксировать ссылку на ячейку, достаточно нажать F4 всего один раз.
Думаю, это несколько удобнее, чем вводить знак доллара вручную.
Частичная фиксация ячейки по строке или по столбцу.
Часто случается, что необходимо зафиксировать только строку или столбец в адресе ячейки. Для этого используются смешанные ссылки.
Вы можете использовать два вида смешанных ссылок:
- Строка фиксируется, а столбец изменяется при копировании.
- Столбец блокируется, а строка изменяется при копировании.
Смешанная ссылка содержит одну относительную и одну абсолютную координату, например $A1 или A$1. Проще говоря, знак доллара используется только единожды.
Получить такую ссылку вы можете любым из описанных выше способов. Либо вручную выбираете место и устанавливаете знак $, либо нажимаете F4 не один, а два или три раза. Вы это видите на рисунке чуть выше.
В результате мы имеем следующее:
В таблице ниже показано, как может быть закреплена ссылка на ячейку.
| Зафиксированная ячейка | Что происходит при копировании или перемещении | Клавиши на клавиатуре |
| $A$1 | Столбец и строка не меняются. | Нажмите F4. |
| A$1 | Строка не меняется. | Дважды нажмите F4. |
| $A1 | Столбец не изменяется. | Трижды нажмите F4. |
Рассмотрим пример, когда нужно закрепить только одну координату: либо столбец, либо строку. И все это в одной формуле.
Предположим, нужно рассчитать цены продажи при разных уровнях наценки. Для этого нужно умножить колонку с ценами (столбец В) на 3 возможных значения наценки (записаны в C2, D2 и E2). Вводим выражение для расчёта в C3, а затем копируем его сначала вправо по строке, а затем вниз:
=$B3*(1+C$2)
Так вы можете использовать силу смешанной ссылки для расчета всех возможных цен с помощью всего одной формулы.
В первом множителе мы зафиксировали в координатах ячейки адрес столбца. Поэтому при копировании вправо по строке адрес $B3 не изменится: ведь строка по-прежнему третья, а буква столбца у нас зафиксирована и меняться не может.
А вот во втором множителе знак доллара мы поставили перед номером строки. Поэтому при копировании вправо координаты столбца изменятся и вместо C$2 мы получим D$2. В результате в D3 у нас получится выражение:
=$B3*(1+D$2)
А когда будем копировать вниз по столбцу, всё будет наоборот: $B3 изменится на $B4, $B5 и т.д. А вот D$2 не изменится, так как «заморожена» строка. В результате в С4 получим:
=$B4*(1+C$2)
Самый приятный момент заключается в том, что формулу мы записываем только один раз, а потом просто копируем ее. Одним махом заполняем всю таблицу и экономим очень много времени.
И если ваши наценки вдруг изменятся, просто поменяйте числа в C2:E2, и проблема пересчёта будет решена почти мгновенно.
В случае, если вам нужно поменять относительные ссылки на абсолютные (или наоборот) в группе ячеек, в целом столбце или большой области, то описанный выше способ ручной корректировки может стать весьма обременительным и скучным занятием. При помощи специального инструмента преобразования формул вы можете выделить целый диапазон, а затем преобразовать формулы в этих ячейках в абсолютные либо в относительные ссылки. Или же можно просто заменить все формулы их значениями одним кликом мышки.
Как зафиксировать ячейку, дав ей имя.
Отдельную ячейку или целый диапазон ячеек в Excel также можно определить по имени. Для этого вы просто выбираете нужную ячейку, вводите желаемое имя в поле Имя и нажимаете клавишу Enter.
Вернёмся к нашему примеру со скидками. Давайте попробуем ячейке F2 присвоить собственное имя, чтобы затем использовать его в расчетах.
Установите курсор в F2, а затем присвойте этому адресу имя, как это показано на рисунке выше. При этом можно использовать только буквы, цифры и нижнее подчёркивание, которым можно заменить пробел. Знаки препинания и служебные символы не допускаются. Не будем мудрствовать и назовём его «скидка».
Это имя теперь вы можете использовать в формулах вашей рабочей книги. Это своего рода абсолютная ссылка, поскольку за ним навсегда закрепляются координаты определенной ячейки или диапазона.
Таким образом, ячейку F2 мы ранее фиксировали при помощи абсолютной ссылки и знака $ —
=B2*$F$2
а теперь то же самое делаем при помощи её имени «скидка»:
=B2*скидка
Ячейка так же надёжно зафиксирована, а формула же при этом становится более понятной и читаемой.
Эксель понимает, что если в формуле встречается имя «скидка», то вместо него нужно использовать содержимое ячейки F2.
Вот какими способами можно зафиксировать ячейку в формуле в Excel. Благодарю вас за чтение и надеюсь, что эта информация была полезной!
Как удалить сразу несколько гиперссылок — В этой короткой статье я покажу вам, как можно быстро удалить сразу все нежелательные гиперссылки с рабочего листа Excel и предотвратить их появление в будущем. Решение работает во всех версиях Excel,… 
Как использовать функцию ГИПЕРССЫЛКА — В статье объясняются основы функции ГИПЕРССЫЛКА в Excel и приводятся несколько советов и примеров формул для ее наиболее эффективного использования. Существует множество способов создать гиперссылку в Excel. Чтобы сделать ссылку на… Гиперссылка в Excel: как сделать, изменить, удалить — В статье разъясняется, как сделать гиперссылку в Excel, используя 3 разных метода. Вы узнаете, как вставлять, изменять и удалять гиперссылки на рабочих листах, а также исправлять неработающие ссылки. Гиперссылки широко используются… 
Как использовать функцию ДВССЫЛ – примеры формул — В этой статье объясняется синтаксис функции ДВССЫЛ, основные способы ее использования и приводится ряд примеров формул, демонстрирующих использование ДВССЫЛ в Excel. В Microsoft Excel существует множество функций, некоторые из которых… 
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать… 
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы… 
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…
Очень важно знать для быстрого расчета прогноза в MS Excel — Как в формуле зафиксировать ссылку на ячейку или диапазон?
Это необходимо для того, чтобы, когда вы протягивали формулу, ссылка на ячейку не смещалась. Например, для расчета коэффициента сезонности января (см. вложение) мы средние продажи за январь (пункт 2 см. вложение) делим на среднегодовые продажи за 3 года (пункт 3 см. вложение). Если мы просто протянем ячейку вниз, чтобы рассчитать коэффициенты для других месяцев, то для февраля мы получим, что среднегодовые продажи за февраль разделятся на ноль, а не на среднегодовые продажи за 3 года.
Как зафиксировать ссылку на ячейку, чтобы, когда мы протягивали формулу, ссылка не смещалась?
Для этого в строке формул выделяете ссылку, которую хотите зафиксировать:
и нажимаете клавишу «F4». Ссылка станет со значками $, как на рисунке:
это означает, что если вы протяните формулу, то ссылка на ячейку $F$4 останется на месте, т.е. зафиксирована строка ‘4’ и столбец ‘F’. Если вы еще раз нажмёте клавишу F4, то ссылка станет F$4 — это означает, что зафиксирована строка 4, а столбец F будет перемещаться.
Если еще раз нажмете клавишу «F4», то ссылка станет $F4:
Это означает, что зафиксирован столбец F и он не будет перемещаться, когда вы будите протаскивать формулу, а ссылка на строку 4 будет двигаться.
Если ссылки имеют вид R1C1, то полностью зафиксированная ячейка будет иметь вид R4C6:
Если зафиксирована только строка (R), то ссылка будет R4C[-1]
Если зафиксирован только столбец (С), то ссылка будет иметь вид RC6
Для того, чтобы зафиксировать диапазон, необходимо его выделить в строке формул в Excel и нажать клавиши “F4”.
Предлагаю вам самостоятельно проделать описанные выше операции, и если будут вопросы, задать их в комментариях к данной статье.
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite — автоматический расчет прогноза в Excel.
- 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Зарегистрируйтесь и скачайте решения
Статья полезная? Поделитесь с друзьями
Как закрепить строку в Excel
Часто при создании отчетов, прайс-листов и других документов в Excel, нам требуется сделать неподвижной – закрепить, какую-либо строку или столбец. Обычно – это верхняя строка документа Эксель, в которой находятся обозначения столбцов таблиц. А так как таблицы массивные и приходится использовать прокрутку, то не всегда удается запомнить, что находится в том или ином столбце. Для этого и существует возможность закрепить верхнюю или любую другую строку, чтобы данные в ней всегда были на виду.
Как это сделать в Excel 2007? Да очень просто.Чтобы закрепить строчку в документе Эксель переходим в верхнем меню на вкладку Вид. Выбираем окошко “Закрепить области” – “Закрепить верхнюю строку”.
Все, теперь при прокрутке – она всегда у вас будет оставаться на виду и вы не собьетесь при заполнении таблицы или любых других работах.
Также в Эксель можно закрепить и первый столбец. Делается это аналогично верхней строке. Вы просто выбираете пункт “Закрепить первый столбец”.
Если вам нужно закрепить какую-то область – это также не трудно сделать, нажав на соответствующую кнопку.
Очень важно знать для быстрого расчета прогноза в MS Excel — Как в формуле зафиксировать ссылку на ячейку или диапазон?
Это необходимо для того, чтобы, когда вы протягивали формулу, ссылка на ячейку не смещалась. Например, для расчета коэффициента сезонности января (см. вложение) мы средние продажи за январь (пункт 2 см. вложение) делим на среднегодовые продажи за 3 года (пункт 3 см. вложение). Если мы просто протянем ячейку вниз, чтобы рассчитать коэффициенты для других месяцев, то для февраля мы получим, что среднегодовые продажи за февраль разделятся на ноль, а не на среднегодовые продажи за 3 года.
Как зафиксировать ссылку на ячейку, чтобы, когда мы протягивали формулу, ссылка не смещалась?
Для этого в строке формул выделяете ссылку, которую хотите зафиксировать:
и нажимаете клавишу «F4». Ссылка станет со значками $, как на рисунке:
это означает, что если вы протяните формулу, то ссылка на ячейку $F$4 останется на месте, т.е. зафиксирована строка ‘4’ и столбец ‘F’. Если вы еще раз нажмёте клавишу F4, то ссылка станет F$4 — это означает, что зафиксирована строка 4, а столбец F будет перемещаться.
Если еще раз нажмете клавишу «F4», то ссылка станет $F4:
Это означает, что зафиксирован столбец F и он не будет перемещаться, когда вы будите протаскивать формулу, а ссылка на строку 4 будет двигаться.
Если ссылки имеют вид R1C1, то полностью зафиксированная ячейка будет иметь вид R4C6:
Если зафиксирована только строка (R), то ссылка будет R4C
Если зафиксирован только столбец (С), то ссылка будет иметь вид RC6
Для того, чтобы зафиксировать диапазон, необходимо его выделить в строке формул в Excel и нажать клавиши “F4”.
Предлагаю вам самостоятельно проделать описанные выше операции, и если будут вопросы, задать их в комментариях к данной статье.
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite — автоматический расчет прогноза в Excel.
- 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Зарегистрируйтесь и скачайте решения Статья полезная? Поделитесь с друзьями
Очень часто в Excel требуется закрепить (зафиксировать) определенную ячейку в формуле. По умолчанию, ячейки автоматически протягиваются и изменяются. Посмотрите на этот пример.
У нас есть данные по количеству проданной продукции и цена за 1 кг, необходимо автоматически посчитать выручку.
Чтобы это сделать мы прописываем в ячейке D2 формулу =B2*C2
Если мы далее протянем формулу вниз, то она автоматически поменяется на соответствующие ячейки. Например, в ячейке D3 будет формула =B3*C3 и так далее. В связи с этим нам не требуется прописывать постоянно одну и ту же формулу, достаточно просто ее протянуть вниз. Но бывают ситуации, когда нам требуется закрепить (зафиксировать) формулу в одной ячейке, чтобы при протягивании она не двигалась.
Взгляните на вот такой пример. Допустим, нам необходимо посчитать выручку не только в рублях, но и в долларах. Курс доллара указан в ячейке B7 и составляет 35 рублей за 1 доллар. Чтобы посчитать в долларах нам необходимо выручку в рублях (столбец D) поделить на курс доллара.
Если мы пропишем формулу как в предыдущем варианте. В ячейке E2 напишем =D2*B7 и протянем формулу вниз, то у нас ничего не получится. По аналогии с предыдущим примером в ячейке E3 формула поменяется на =E3*B8 — как видите первая часть формулы поменялась для нас как надо на E3, а вот ячейка на курс доллара тоже поменялась на B8, а в данной ячейке ничего не указано. Поэтому нам необходимо зафиксировать в формуле ссылку на ячейку с курсом доллара. Для этого необходимо указать значки доллара и формула в ячейке E3 будет выглядеть так =D2/$B$7, вот теперь, если мы протянем формулу, то ссылка на ячейку B7 не будет двигаться, а все что не зафиксировано будет меняться так, как нам необходимо.
Примечание: в рассматриваемом примере мы указал два значка доллара $B$7. Таким образом мы указали Excel, чтобы он зафиксировал и столбец B и строку 7, встречаются случаи, когда нам необходимо закрепить только столбец или только строку. В этом случае знак $ указывается только перед столбцом или строкой B$7 (зафиксирована строка 7) или $B7 (зафиксирован только столбец B)
Формулы, содержащие значки доллара в Excel называются абсолютными (они не меняются при протягивании), а формулы которые при протягивании меняются называются относительными.
Чтобы не прописывать знак доллара вручную, вы можете установить курсор на формулу в ячейке E2 (выделите текст B7) и нажмите затем клавишу F4 на клавиатуре, Excel автоматически закрепит формулу, приписав доллар перед столбцом и строкой, если вы еще раз нажмете на клавишу F4, то закрепится только столбец, еще раз — только строка, еще раз — все вернется к первоначальному виду.
Вставлять строки и колонки в Excel очень удобно при форматировании таблиц и листов. Но еще больше расширяют возможности программы функция вставки ячеек и целых диапазонов как смежных, так и несмежных.
Рассмотрим на практических примерах как добавить (или удалить) ячейки в таблицу в Excel и их диапазонов на листе. По сути ячейки не добавляются а просто значение одних перемещаются на другие. Данный факт следует учитывать когда лист заполнен более чем на 50%. Тогда ячеек для строк или столбцлов может просто не хватать и данная операция будет удалять данные. В таком случаи рационально содержание одного листа разбить на 2 или 3. Это одна из главных причин почему в новых версиях Excel добавлено больше количество столбцов и строк (их 65 000 строк в старых версиях до 1 000 000 в новых).
Вставка диапазона пустых ячеек
Как вставить ячейки в таблице Excel? Допустим, у нас есть таблица чисел, в которой необходимо вставить две пустые ячейки посередине.
Выполняем следующий порядок действий:
- Выделяем диапазон в том месте, где должны добавиться новые пустые блоки. Переходим на закладку «Главная»-«Вставить»-«Вставить ячейки». Или просто кликаем по выделенной области правой кнопкой мышки и выбираем опцию вставить. Или нажмите комбинацию горячих клавиш CTRL+SHIFT+ «плюс».
- Появится диалоговое окно «Добавление ячеек» где нужно задать необходимые параметры. В данном случае выберем опцию «со сдвигом вниз».
- Нажимаем ОК. После чего в таблицу чисел добавятся новые ячейки, а старые сохранят свои значения и сместятся вниз, уступив свое место.
В данной ситуации можно просто нажать инструмент «Главная»-«Вставить» (без выбора опций). Тогда новые ячейки вставятся, а старые сместятся вниз (по умолчанию), без вызова диалогового окна параметров.
Чтобы добавить ячейки в Excel используйте горячие клавиши CTRL+SHIFT+«плюс» предварительно выделив их.
Примечание. Обратите внимание на диалоговое окно параметров. Два последних параметра позволяют нам вставлять строки и столбцы таким же способом.
Удаление ячеек
Теперь из нашей таблицы чисел удалим этот же диапазон. Просто выделяем нужный диапазон. Кликните правой кнопкой мышки по выделенному диапазону и выбираем «Удалить». Или идем в закладку «Главная»-«Удалить»-«со сдвигом вверх». Результат обратно пропорциональный предыдущему результату.
Чтобы удалить ячейки в Excel используйте горячие клавиши CTRL+«минус» предварительно выделив их.
Примечание. Таким же образом можно удалять строки и столбцы.
Внимание! На практике использование инструментов «Вставить» или «Удалить» при вставке или удалении диапазонов без окна параметров лучше не использовать, чтобы не запутаться в больших и сложных таблицах. Если хотите экономить время, пользуйтесь горячими клавишами. Они вызывают диалоговое окно параметров вставки и удаления, позволяют быстрее справиться с задачей в любом случае.
Нередко пользователи сталкиваются с необходимостью закрепить ячейку в формуле. Например, она возникает в ситуациях, когда нужно скопировать формулу, но чтобы ссылка не перемещалась на такое же количество ячеек вверх и вниз, как было скопировано относительно исходного места.
В этом случае можно зафиксировать ссылку на ячейку в Excel. Причем это можно сделать сразу несколькими способами. Давайте более детально разберемся, как достичь этой цели.
Содержание
- Что такое ссылка Excel
- Метод 1
- Метод 2
- Метод 3
- Метод 4
- Закрепление ячеек для большого диапазона
- Пример
- Ссылки на ячейку в макросах
- Выводы
Что такое ссылка Excel
Лист состоит из ячеек. Каждая из них содержит определенную информацию. Другие ячейки могут ее использовать в вычислениях. Но как они понимают, откуда брать данные? Это помогают им сделать ссылки.
Каждая ссылка обозначает ячейку с помощью одной буквы и одной цифры. Буква обозначает столбец, а цифра – строку.
Ссылки бывают трех типов: абсолютные, относительные и смешанные. Второй из них выставлен по умолчанию. Абсолютной ссылкой считается та, которая имеет фиксированный адрес как столбца, так и колонки. Соответственно, смешанная – это та, где зафиксирована или отдельно колонка, или строка.
Метод 1
Для того, чтобы сохранить адреса и колонки, и ряда, необходимо выполнить следующие шаги:
- Нажать по ячейке, содержащей формулу.
- Нажать по строке формул по той ячейке, которая нам нужна.
- Нажать F4.
Как следствие, ссылка ячейки изменится на абсолютную. Ее можно будет узнать по характерному знаку доллара. Например, если нажать на ячейку B2, а потом нажать на F4, то ссылка обретет следующий вид: $B$2.
Что означает знак доллара перед частью адреса на ячейку?
- Если он размещается перед буквой, то это говорит о том, что ссылка на столбец остается такой же, независимо от того, куда была перемещена формула.
- Если знак доллара находится перед числом, это говорит о том, что закреплена строка.
Метод 2
Этот способ почти такой же самый, как и прошлый, только нажать нужно F4 два раза. например, если у нас была ячейка B2, то после этого она станет B$2. Простыми словами, таким способом у нас получилось зафиксировать строку. При этом буква столбца будет изменяться.
Очень удобно, например, в таблицах, где нужно в нижней ячейке вывести содержимое второй ячейки сверху. Вместо того, чтобы делать такую формулу много раз, достаточно зафиксировать строку и дать возможность меняться столбцу.
Метод 3
Это полностью аналогичный предыдущему метод, только нужно нажать клавишу F4 три раза. Тогда абсолютной будет только ссылка на колонку, а строка останется зафиксированной.
Метод 4
Предположим, у нас есть абсолютная ссылка на ячейку, но тут понадобилось сделать ее относительной. Для этого необходимо нажать клавишу F4 такое количество раз, чтобы не было знаков $ в ссылке. Тогда она станет относительной, и при перемещении или копировании формулы будет изменяться как адрес столбца, так и адрес строки.
Закрепление ячеек для большого диапазона
Видим, что приведенные выше методы вообще не представляют никакой сложности для выполнения. Но задачи бывают специфические. И, например, что делать, если у нас есть сразу несколько десятков формул, ссылки в которых нужно превратить в абсолютные.
К сожалению, стандартными методами Excel достичь этой цели не получится. Для этого нужно воспользоваться специальным аддоном, который называется VBA-Excel. Она содержит много дополнительных возможностей, позволяющих значительно быстрее выполнять стандартные задачи с Excel.
В ее состав входит больше ста пользовательских функций и 25 различных макросов, а также она регулярно обновляется. Она позволяет улучшить работу почти с любым аспектом:
- Ячейки.
- Макросы.
- Функции разных типов.
- Ссылки и массивы.
В том числе, эта надстройка позволяет закрепить ссылки сразу в большом количестве формул. Для этого необходимо выполнить следующие действия:
- Выделить диапазон.
- Открыть вкладку VBA-Excel, которая появится после установки.
- Открыть меню «Функции», где располагается опция «Закрепить формулы».
6 - Далее появится диалоговое окно, в котором нужно указать необходимый параметр. Этот аддон позволяет закрепить столбец и колонку по отдельности, вместе, а также снять уже имеющееся закрепление пакетом. После того, как будет выбран необходимый параметр с помощью соответствующей радиокнопки, нужно подтвердить свои действия путем нажатия «ОК».
Пример
Давайте приведем пример для большей наглядности. Допустим, у нас есть информация, в которое описывается стоимость товаров, его общее количество и выручка за продажи. И перед нами стоит задача сделать так, чтобы таблица, исходя из количества и стоимости автоматически определялась, сколько денег получилось заработать без вычета убытков.
В нашем примере для этого необходимо ввести формулу =B2*C2. Она достаточно простая, как видите. Очень легко на ее примере описывать то, как можно закрепить адрес ячейки или отдельный ее столбец или ряд.
Можно, конечно, в данном примере попробовать протянуть с помощью маркера автозаполнения формулу вниз, но в таком случае ячейки будут автоматически изменены. Так, в ячейке D3 будет другая формула, где цифры будут заменены, соответственно, на 3. Далее по схеме – D4 – формула обретет вид =B4*C4, D5 – аналогично, но с цифрой 5 и так далее.
Если так и надо (в большинстве случаев так и получается), то проблем нет. Но если нужно зафиксировать формулу в одной ячейке, чтобы она не изменялась при перетягивании, то сделать это будет несколько сложнее.
Предположим, нам необходимо определить долларовую выручку. В ячейке B7 давайте его укажем. Давайте немного поностальгируем и укажем стоимость 35 рублей за доллар. Соответственно, чтобы определить выручку в долларах, необходимо сумму в рублях разделить на курс доллара.
Вот, как оно выглядит в нашем примере.
Если мы аналогично предыдущему варианту попробуем прописать формулу, то потерпим поражение. Точно так же формула изменится на соответствующую. В нашем примере она будет такой: =E3*B8. Отсюда мы можем увидетЬ. что первая часть формулы превратилась в E3, и мы ставим перед собой эту задачу, а вот изменение второй части формулы на B8 нам ни к чему. Поэтому нам нужно превратить ссылку на абсолютную. Можно сделать это и без нажатия на клавишу F4, просто поставив знак доллара.
После того, как мы превратили ссылку на вторую ячейку в абсолютную, то она стала защищенной от изменений. Теперь можно смело ее перетаскивать с помощью маркера автозаполнения. Все зафиксированные данные будут оставаться такими же, независимо от положения формулы, а незафиксированные будут гибко меняться. Во всех ячейках будет выручка в рублях, описанная в этой строке, делиться на один и тот же курс доллара.
Сама формула будет выглядеть следующим образом:
=D2/$B$7
Внимание! Мы указали два знака доллара. Таким образом мы показываем программе, что нужно фиксировать как столбец, так и строку.
Ссылки на ячейку в макросах
Макрос – это подпрограмма, которая позволяет автоматизировать действия. В отличие от стандартного функционала Excel, макрос позволяет сразу задать конкретную ячейку и выполнить определенные действия всего в несколько строчек кода. Полезно для пакетной обработки информации, например, если нет возможности установки аддонов (например, используется компьютер компании, а не личный).
Для начала нужно понять, что ключевое понятие макроса – объекты, которые могут содержать в себе другие объекты. За электронную книгу (то есть, документ) отвечает объект Workbooks. В его состав входит объект Sheets, который являет собой совокупность всех листов открытого документа.
Соответственно, ячейки – это объект Cells. Он содержит все ячейки определенного листа.
Каждый объект уточняется с помощью аргументов в скобках. В случае с ячейками, ссылки на них даются в такой последовательности. Сначала указывается номер строки, а потом – номер или буква столбца (допустимы оба формата).
Например, строчка кода, содержащая ссылку на ячейку C5, будет выглядеть так:
Workbooks(«Книга2.xlsm»).Sheets(«Лист2»).Cells(5, 3)
Workbooks(«Книга2.xlsm»).Sheets(«Лист2»).Cells(5, «C»)
Также доступ к ячейке можно получить с помощью объекта Range. Вообще, он предназначен для того, чтобы давать ссылку на диапазон (элементы которого, к слову, также могут быть абсолютными или относительными), но можно дать просто название ячейки, в таком же формате, как в документе Excel.
В этом случае строчка будет выглядеть следующим образом.
Workbooks(«Книга2.xlsm»).Sheets(«Лист2»).Range(«C5»)
Может показаться, что этот вариант удобнее, но преимущество первых двух вариантов в том, что можно использовать переменные в скобках и давать ссылку уже не абсолютную, а что-то типа относительной, которая будет зависеть от результатов вычислений.
Таким образом, макросы могут эффективно использоваться в программах. По факту, все ссылки на ячейки или диапазоны здесь будут абсолютными, и поэтому с их помощью также можно фиксировать их. Правда, это не так удобно. Использование макросов может быть полезным при написании сложных программ с большим количеством шагов в алгоритме. Вообще, стандартный способ использования абсолютных или относительных ссылок значительно удобнее.
Выводы
Мы разобрались, что такое ссылка на ячейку, как она работает, для чего нужна. Поняли разницу между абсолютными и относительными ссылками и разобрались, что нужно сделать для того, чтобы превратить один тип в другой (простыми словами, закрепить адрес или открепить его). Поняли, как можно это сделать сразу с большим количеством значений. Теперь вы гибко использовать эту функцию в правильных ситуациях.
Оцените качество статьи. Нам важно ваше мнение:
Как в формуле эксель сделать неизменную ячейку
Простой способ зафиксировать значение в формуле Excel
Сегодня я бы хотел поделиться с вами такой небольшой хитростью, как можно правильно зафиксировать значение в формуле Excel. К сожалению, очень мало пользователей используют таким удобным функционалом табличного процессора, а это жаль. Часто многие сталкивались с такой ситуацией что возникает необходимость сдвинуть или скопировать формулы, но вот незадача, адреса ячеек также уходили «налево» и результата невозможно было получить. А для получения нужного результата, нам окажет помощь доллар, а точнее знак «$», вот именно он является самым главным условием что бы закрепить значение в ячейках.
Итак, рассмотрим более детально все варианты как закрепляется ячейка. Есть три варианта фиксации:
Полная фиксация ячейки
Полная фиксация ячейки — это когда закрепляется значение по вертикали и горизонтали (пример, $A$1), здесь значение никуда не может сдвинутся, так называемая абсолютная формула. Очень удобно такой вариант использовать, когда необходимо ссылаться на значение в ячейке, такие как курс валют, константа, уровень минимальной зарплаты, расход топлива, процент доплат, кофициент и т.п.
В примере у нас есть товар и его стоимость в рублях, а нам нужно узнать он стоит в вечнозеленых долларах. Поскольку, обменный курс у нас постоянная ячейка D1, в которой сам курс может меняться исходя из экономической ситуации страны. Сам диапазон вычисление находится от E4 до E7. Когда мы в ячейку Е4 пропишем формулу =D4/D1, то в результате копирования, ячейки поменяют адреса и сдвинутся ниже, пропуская, так необходимый нам обменный курс. А вот если внести изменения и зафиксировать значение в формуле простым символом доллара («$»), то мы получим следующий результат =D4/$D$1 и в этом случае, сдвигая и копируя, формулу мы получаем нужный нам результат во всех ячейках диапазона;
Фиксация формулы в Excel по вертикали
Частичная фиксация по вертикали (пример $A1), это закрепления только столбцов, возможность сдвига формулы частично сохраняется, но только по горизонтали (в строке). Как видно со скриншота или скачанного вами файла с примером.
Фиксация формул по горизонтали
Следующее закрепление будет по горизонтали (пример, A$1). И все правила остаются действительными как и предыдущем пункте, но немножко наоборот. Рассмотрим данный пример подробнее. У нас есть товар, продаваемый, в разных городах и имеющие разную процентную градацию наценок, а нам необходимо высчитать какую наценку и где мы будем ее получать. В диапазоне K1:M1 мы проставили процент наценки и эти ячейки у нас должны быть закреплены для автоматических вычислений. Диапазон для написания формул у нас является К4:М7, здесь мы должны в один клик получить результаты просто правильно прописав формулу. Растягивая формулу по диагонали, мы должны зафиксировать диапазон процентной ставки (горизонталь) и диапазон стоимости товара (вертикаль). Итак, мы фиксируем горизонтальную строку $1 и вертикальный столбец $J и в ячейке К4 прописываем формулу =$J4*K$1 и после ее копирование во все ячейки вычисляемого диапазона и получаем нужный результат без каких-либо сдвигов в формуле.
Производя подобные вычисления очень легко и быстро делать перерасчёт на разнообразнейшие варианты, изменив всего 1 цифру. В файле примера вы сможете проверить это изменив всего курс валюты или региональные проценты. И такие вычисление, будут в несколько раз быстрее нежели, другие варианты написание формул в Excel и количество ошибок будет значительно меньше. Но необходимость этого надо увидеть исходя с вашей текущей задачи и проводить фиксацию значения в ячейках стоит в ключевых местах.
Что бы постоянно не переключать раскладку клавиатуры при прописании знака «$» для закрепления значение в формуле, можно использовать «горячую» клавишу F4. Если курсор стоит на адресе ячейки, то при нажатии, будет автоматически добавлен знак «$» для столбцов и строчек. При повторном нажатии, добавится только для столбцов, еще раз нажать, будет только для строк и 4-е нажатие снимет все закрепления, формула вернется к первоначальному виду.
Скачать пример можно здесь.
А на этом у меня всё! Я очень надеюсь, что вы поняли все варианты как возможно зафиксировать ячейку в формуле. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Не забудьте поблагодарить автора!
Деньги — нерв войны.
Марк Туллий Цицерон
Как в Эксель задать статическую ячейку, чтобы при «перетягивании » формулы
ссылка на эту ячейку оставалась неизменной
Мне нужна формула на проверку сопоставления одной даты с рядом дат, я перетягиваю ее и хочу, чтобы эта одна дата оставалась неизменной, а другой аргумент перебегал по ряду дат, однако оба аргумента перебегают.
=ЕСЛИ(C5=I2;»сопост»;»-«) примерно вот такая штука, как задать С5 чтобы во всех формулах она была с5
Хотите зафиксировать столбец, ставьте значок $ перед буковкой, хотите строку — ставьте то же самое перед циферкой. И тяните, куда нужно, всё на месте останется. И будет Вам счастье 🙂
Эксель вообще полезная штука, её узнавать интересно.
Эксель довольно сложная программа в использовании и даже будучи пользуясь ей долгое время, можно каждый день открывать для себя новые функции и «возможности». Для того, чтобы зафиксировать столбец используйте символ $ (Shift +4) перед буквой. Чтобы зафиксировать строку используйте тот же символ $ (Shift +4) перед номером строки.
Например, вам необходимо ввести в ячейку D7 значение ячейки C5 и «протянуть» по горизонтали и вертикали. Для этого вам надо перед обозначением столбца поставить значок $. У вас закрепится столбец C и при вертикальном «протаскивании» столбец C останется закрепленным но будет меняться строка. А при горизонтальном «протаскивании» закрепленной останется строка, меняться будет столбец. Что бы этого не произошло вам надо перед цифро 5 также поставить значок $.
Как зафиксировать ячейку в Excel в формуле
При работе с таблицами Excel 2010, 2013, 2016 иногда необходимо сделать так, чтобы при перемещении формулы по ячейкам ссылка оставалась неизменной. В связи с этим многие пользователи сталкиваются с неудобствами при работе, однако, есть выход из данного положения. В таблице Excel предусмотрена специальная активная кнопка для фиксации, как ее активировать будет рассмотрено в данной статье.
Как зафиксировать ячейку в формуле в таблицах Excel – вариант №1
Чтобы при перемещении формулы, символ столбца и номер строки не менялись, необходимо выполнить некоторые действия. Для начала следует кликнуть по необходимой в данный момент для фиксации ячейке, чтобы она выделилась. После этого необходимо в таблице формул кликнуть на ссылку ячейки, которая нуждается в фиксации. После этого нужно 1 раз нажать на кнопку на клавиатуре F4.
В результате ссылка будет зафиксирована с помощью $ (знака доллара). Например, если у вас в формуле было значение С3, то после того, как вы проведете вышеописанную процедуру, ссылка обязана стать такого формата — $С$3.
Знак $, расположенный перед символом будет означать то, что при перемещении формулы, смещая ее в столбцах, ссылка меняться не будет. Второй знак $, расположенный после символа и, соответственно, перед цифрами будет означать то, что при перемещении формулы по строкам, ссылка меняться не будет.
Как закрепить ячейку в формуле в Excel – вариант №2
Данный способ практически не отличается от предыдущего, однако, здесь необходимо нажать два раз F4 вместо одного. Таким образом вы сможете произвести фиксацию ячейку в формуле так, что при ее перемещении символ столба будет меняться, а цифра строки – нет.
Как в Экселе закрепить ячейку в формуле – вариант №3
В данном случае выполняем все те же действия что и в способе номер один, однако, клавишу F4 нужно нажать три раза. В таком случае вставится только один знак фиксации перед символом. Теперь при передвижении формулы по строкам цифры будут меняться, а при передвижении по столбцам значение будет прежнее.
Как отменить действия фиксации ячейки в формуле в таблицах Excel
Если по каким-либо причинам в таблицах Excel 2016, 2013, 2010 необходимо отменить фиксацию ссылки определенной ячейки, это легко можно сделать. Для этого кликните по формуле левой кнопкой мыши, чтобы она выделилась. Затем нажимайте F4 столько раз, сколько необходимо пока не пропадут все знаки доллара.
Как в excel закрепить (зафиксировать) ячейку в формуле
Очень часто в Excel требуется закрепить (зафиксировать) определенную ячейку в формуле. По умолчанию, ячейки автоматически протягиваются и изменяются. Посмотрите на этот пример.
У нас есть данные по количеству проданной продукции и цена за 1 кг, необходимо автоматически посчитать выручку.
Чтобы это сделать мы прописываем в ячейке D2 формулу =B2*C2
Если мы далее протянем формулу вниз, то она автоматически поменяется на соответствующие ячейки. Например, в ячейке D3 будет формула =B3*C3 и так далее. В связи с этим нам не требуется прописывать постоянно одну и ту же формулу, достаточно просто ее протянуть вниз. Но бывают ситуации, когда нам требуется закрепить (зафиксировать) формулу в одной ячейке, чтобы при протягивании она не двигалась.
Взгляните на вот такой пример. Допустим, нам необходимо посчитать выручку не только в рублях, но и в долларах. Курс доллара указан в ячейке B7 и составляет 35 рублей за 1 доллар. Чтобы посчитать в долларах нам необходимо выручку в рублях (столбец D) поделить на курс доллара.
Если мы пропишем формулу как в предыдущем варианте. В ячейке E2 напишем =D2* B7 и протянем формулу вниз, то у нас ничего не получится. По аналогии с предыдущим примером в ячейке E3 формула поменяется на =E3* B8 — как видите первая часть формулы поменялась для нас как надо на E3, а вот ячейка на курс доллара тоже поменялась на B8, а в данной ячейке ничего не указано. Поэтому нам необходимо зафиксировать в формуле ссылку на ячейку с курсом доллара. Для этого необходимо указать значки доллара и формула в ячейке E3 будет выглядеть так =D2/ $B$7 , вот теперь, если мы протянем формулу, то ссылка на ячейку B7 не будет двигаться, а все что не зафиксировано будет меняться так, как нам необходимо.
Примечание: в рассматриваемом примере мы указал два значка доллара $ B $ 7. Таким образом мы указали Excel, чтобы он зафиксировал и столбец B и строку 7 , встречаются случаи, когда нам необходимо закрепить только столбец или только строку. В этом случае знак $ указывается только перед столбцом или строкой B $ 7 (зафиксирована строка 7) или $ B7 (зафиксирован только столбец B)
Формулы, содержащие значки доллара в Excel называются абсолютными (они не меняются при протягивании), а формулы которые при протягивании меняются называются относительными.
Чтобы не прописывать знак доллара вручную, вы можете установить курсор на формулу в ячейке E2 (выделите текст B7) и нажмите затем клавишу F4 на клавиатуре, Excel автоматически закрепит формулу, приписав доллар перед столбцом и строкой, если вы еще раз нажмете на клавишу F4, то закрепится только столбец, еще раз — только строка, еще раз — все вернется к первоначальному виду.
Как в Эксель задать статическую ячейку, чтобы при «перетягивании » формулы
ссылка на эту ячейку оставалась неизменной
Мне нужна формула на проверку сопоставления одной даты с рядом дат, я перетягиваю ее и хочу, чтобы эта одна дата оставалась неизменной, а другой аргумент перебегал по ряду дат, однако оба аргумента перебегают.
=ЕСЛИ(C5=I2;»сопост»;»-«) примерно вот такая штука, как задать С5 чтобы во всех формулах она была с5
Хотите зафиксировать столбец, ставьте значок $ перед буковкой, хотите строку — ставьте то же самое перед циферкой. И тяните, куда нужно, всё на месте останется. И будет Вам счастье 🙂
Эксель вообще полезная штука, её узнавать интересно.
Эксель довольно сложная программа в использовании и даже будучи пользуясь ей долгое время, можно каждый день открывать для себя новые функции и «возможности». Для того, чтобы зафиксировать столбец используйте символ $ (Shift +4) перед буквой. Чтобы зафиксировать строку используйте тот же символ $ (Shift +4) перед номером строки.
Например, вам необходимо ввести в ячейку D7 значение ячейки C5 и «протянуть» по горизонтали и вертикали. Для этого вам надо перед обозначением столбца поставить значок $. У вас закрепится столбец C и при вертикальном «протаскивании» столбец C останется закрепленным но будет меняться строка. А при горизонтальном «протаскивании» закрепленной останется строка, меняться будет столбец. Что бы этого не произошло вам надо перед цифро 5 также поставить значок $.
Часто так бывает, что при копировании формул, Вам нужно, что бы ссылка на ячейку в формуле осталась такой же, как и была, а не переместилась относительно исходного места. Тогда Вам на помощь придет такая функция в Excel, как фиксация ссылок на ячейки в Экселе. Остановимся подробно на всех вариантах.
1. Способ, как закрепить (зафиксировать) строку и столбец в формуле Excel
Для того, что бы ссылка на ячейку не изменялась полностью, т.е. адрес столбца и строки оставались неизменными, проделайте следующие простые действия:
- Кликните на ячейке с формулой.
- Кликните в строке формул на адрес той ячейке, что Вы хотите закрепить.
- Нажмите F4 один раз.
В результате ссылка на ячейку должна быть дополнена знаками доллара, к примеру у Вас в формуле была ссылка на ячейку B2, то после вышеуказанной операции, ссылка на ячейку должна стать $B$2.
Разберем подробно, что значит знак доллара перед буквой и перед числом:
- Знак доллара перед буквой означает, что при перемещении формулы вправо или влево, т.е. смещая ее по столбцам, ссылка на столбец ячейки в формуле меняться не будет.
- Знак доллара перед числом означает, что при перемещении формулы вверх
или вниз, т.е. смещая ее по строкам, ссылка на строку ячейки в
формуле меняться не будет.
2. Способ, как закрепить (зафиксировать) строку в формуле Excel
Способ полностью аналогичный тому, что описан выше, только Вам нужно будет нажать дважды на F4. К примеру, если у Вас в формуле ссылка на ячейку B2, то Вы получите B$2. Это значит, что теперь при перемещении формулы, будет изменяться буква столбца, а номер строки будет оставаться неизменным.
3. Способ, как закрепить (зафиксировать) столбец в формуле Excel
Все тоже самое, что и в вариантах выше, только нажмите на клавишу F4 трижды. Вы должны получить ссылку на ячейку вида $B2, т.е. теперь при перемещении формулы, будет меняться номер строки, а буква столбца будет неизменной.
4. Способ, как отменить фиксацию ячейки в формуле Excel
В случае если Вам наоборот нужно отменить фиксацию ячейки в формуле, то нажмите F4 несколько раз, так что бы в ссылке на ячейку не осталось знаков $, тогда при перемещении формулы, будет изменяться адрес ячейки как по строкам, так и по столбцам.
Спасибо за внимание. Остались вопросы — задавайте их в комментариях к статье. Подписывайтесь на наши группы Вконтакте, Facebook, Twitter, Google+ и будете получать первыми информацию о новых статьях на сайте.
Когда вы используете относительную ссылку на ячейку в формуле, она автоматически настраивается, когда вы используете маркер заполнения, чтобы применить его к другому месту или скопировать и вставить в другие ячейки. См. Снимок экрана ниже.
Во многих случаях вам нужно, чтобы ссылка на ячейку оставалась постоянной, а не корректировалась автоматически. В этом руководстве мы покажем вам, как сохранить постоянную ссылку на ячейку формулы в Excel.
Сохраняйте постоянную ссылку на ячейку формулы с помощью клавиши F4
Чтобы ссылка на ячейку в формуле оставалась постоянной, вам просто нужно добавить символ $ к ссылке на ячейку, нажав клавишу F4. Пожалуйста, сделайте следующее.
1. Выделите ячейку с формулой, которую вы хотите сделать постоянной.
2. На панели формул поместите курсор в ячейку, которую вы хотите сделать постоянной, затем нажмите F4 .
В этом случае я не хочу, чтобы ссылка на ячейку A1 изменялась при перемещении формулы, поэтому я помещаю курсор на A1 в формуле и затем нажимаю F4. Смотрите скриншот:
Затем вы можете увидеть, что ссылка на ячейку A1 в формуле постоянна.
Сохраняйте постоянную ссылку на ячейку формулы всего за несколько кликов
Здесь очень рекомендую Kutools for Excel‘s Преобразовать ссылки утилита. Эта функция помогает легко преобразовать все ссылки на формулы в большом количестве в выбранном диапазоне или нескольких диапазонах в конкретный тип ссылки на формулу. Например, преобразовать относительное в абсолютное, абсолютное в относительное и т. Д.
Скачать Kutools for Excel Сейчас! (30-дневная бесплатная трасса)
Давайте посмотрим, как использовать эту функцию, чтобы легко сохранить постоянную ссылку на ячейку формулы в Excel.
1. После установки Kutools for Excel, Пожалуйста, нажмите Кутулс > Больше > Преобразовать ссылки для активации Преобразование ссылок на формулы функцию.
2. Когда Преобразование ссылок на формулы появится диалоговое окно, настройте его следующим образом.
- Выберите диапазон или несколько диапазонов (удерживайте Ctrl клавиша для выбора нескольких диапазонов один за другим) вы хотите сделать ссылки постоянными;
- Выберите К абсолютному вариант;
- Нажмите OK кнопку.
Затем все относительные ссылки на ячейки в выбранном диапазоне немедленно заменяются на постоянные ссылки.
Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Демонстрация: сохраняйте постоянную ссылку на ячейку формулы с помощью Kutools for Excel
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (10)
Оценок пока нет. Оцените первым!
Excel для Microsoft 365 Excel для Интернета Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
ссылка на ячейку указывает на ячейку или диапазон ячеек листа. Ссылки можно применять в формула, чтобы указать приложению Microsoft Office Excel на значения или данные, которые нужно использовать в формуле.
Ссылки на ячейки можно использовать в одной или нескольких формулах для указания на следующие элементы:
-
данные из одной или нескольких смежных ячеек на листе;
-
данные из разных областей листа;
-
данные на других листах той же книги.
Например:
|
Формула |
Объект ссылки |
Возвращаемое значение |
|---|---|---|
|
=C2 |
Ячейка C2 |
Значение в ячейке C2 |
|
=A1:F4 |
Ячейки A1–F4 |
Значения во всех ячейках, но после ввода формулы необходимо нажать сочетание клавиш Ctrl+Shift+Enter. Примечание. Эта функция не работает в Excel в Интернете. |
|
=Актив-Пассив |
Ячейки с именами «Актив» и «Пассив» |
Разность значений в ячейках «Актив» и «Пассив» |
|
{=Неделя1+Неделя2} |
Диапазоны ячеек «Неделя1» и «Неделя2» |
Сумма значений в диапазонах ячеек «Неделя1» и «Неделя2» как формула массива |
|
=Лист2!B2 |
Ячейка B2 на листе Лист2 |
Значение в ячейке B2 на листе Лист2 |
-
Щелкните ячейку, в которую нужно ввести формулу.
-
В строка формул
введите = (знак равенства). -
Выполните одно из следующих действий.
-
Создайте ссылку на одну или несколько ячеек. Чтобы создать ссылку, выделите ячейку или диапазон ячеек на том же листе.
Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
-
Создайте ссылку на определенное имя. Чтобы создать ссылку на определенное имя, выполните одно из указанных ниже действий.
-
Введите имя.
-
Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.
Примечание: Если в углу цветной границы нет квадратного маркера, значит это ссылка на именованный диапазон.
-
-
-
Выполните одно из указанных ниже действий.
-
Если требуется создать ссылку в отдельной ячейке, нажмите клавишу ВВОД.
-
Если требуется создать ссылку в формула массива (например A1:G4), нажмите сочетание клавиш CTRL+SHIFT+ВВОД.
Ссылка может быть одной ячейкой или диапазоном, а формула массива может возвращать одно или несколько значений.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
-
введите = (знак равенства).На ячейки, расположенные на других листах в той же книге, можно сослаться, вставив перед ссылкой на ячейку имя листа с восклицательным знаком (!). В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же книге.
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
-
Щелкните ячейку, в которую нужно ввести формулу.
-
В строка формул
введите = (знак равенства) и формулу, которую нужно использовать. -
Щелкните ярлычок листа, на который нужно сослаться.
-
Выделите ячейку или диапазон ячеек, на которые нужно сослаться.
Примечание: Если имя другого листа содержит знаки, не являющиеся буквами, необходимо заключить имя (или путь) в одинарные кавычки (‘).
Также можно скопировать и вставить ссылку на ячейку, а затем воспользоваться командой Ссылки на ячейки для создания ссылки на ячейку. Эту команду можно использовать в указанных ниже случаях.
-
Для отображения важных данных в более заметном месте. Предположим, существует книга с множеством листов, на каждом из которых есть ячейка, отображающая итоговые данные по другим ячейкам этого листа. Чтобы выделить такие итоговые ячейки, можно создать ссылки на них с первого листа книги, которые позволят увидеть итоговые данные из всей книги на ее первом листе.
-
Для упрощения ссылок на ячейки между листами и книгами. Команда Ссылки на ячейки автоматически вставляет выражения с правильным синтаксисом.
-
Выделите ячейку с данными, ссылку на которую необходимо создать.
-
Нажмите клавиши CTRL+C или перейдите на вкладку Главная и в группе Буфер обмена щелкните Копировать
.
-
Нажмите клавиши CTRL+V или перейдите на вкладку Главная и в группе Буфер обмена щелкните Вставить
.По умолчанию при вставке скопированных данных отображается кнопка Параметры вставки
. -
Нажмите кнопку Параметры вставки, а затем выберите пункт Вставить связь
.
.
.
.
.-
Дважды щелкните ячейку, содержащую формулу, которую нужно изменить. Каждая ячейка или диапазон ячеек в Excel, на которые ссылается формула, выделяются своим цветом.
-
Выполните одно из указанных ниже действий.
-
Чтобы переместить ссылку на ячейку или диапазон, перетащите цветную границу к новой ячейке или диапазону.
-
Чтобы изменить количество ячеек в диапазоне, перетащите угол границы.
-
В строка формул выделите ссылку в формуле и введите новую ссылку
. -
Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.
-
-
Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Если после ввода ссылки на ячейку в формулу задается имя для ссылки на ячейку, иногда требуется заменить существующие ссылки на ячейки определенными именами.
-
Выполните одно из указанных ниже действий.
-
Выделите диапазон ячеек, содержащих формулы, в которых необходимо заменить ссылки на ячейки определенными именами.
-
Чтобы заменить ссылки именами во всех формулах листа, выделите одну пустую ячейку.
-
-
На вкладке Формулы в группе Определенные имена щелкните стрелку рядом с кнопкой Присвоить имя и выберите команду Применить имена.
-
Выберите имена в поле Применить имена, а затем нажмите кнопку ОК.
-
Выделите ячейку с формулой.
-
В строке формул строка формул
выделите ссылку, которую нужно изменить. -
Для переключения между типами ссылок нажмите клавишу F4.
Дополнительные сведения о разных типах ссылок на ячейки см. в статье Обзор формул.
-
Щелкните ячейку, в которую нужно ввести формулу.
-
В строка формул
введите = (знак равенства). -
Выделите ячейку или диапазон ячеек на том же листе. Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
-
Выполните одно из указанных ниже действий.
-
Если требуется создать ссылку в отдельной ячейке, нажмите клавишу ВВОД.
-
Если требуется создать ссылку в формула массива (например A1:G4), нажмите сочетание клавиш CTRL+SHIFT+ВВОД.
Ссылка может быть одной ячейкой или диапазоном, а формула массива может возвращать одно или несколько значений.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
-
На ячейки, расположенные на других листах в той же книге, можно сослаться, вставив перед ссылкой на ячейку имя листа с восклицательным знаком (!). В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же книге.
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
-
Щелкните ячейку, в которую нужно ввести формулу.
-
В строка формул
введите = (знак равенства) и формулу, которую нужно использовать. -
Щелкните ярлычок листа, на который нужно сослаться.
-
Выделите ячейку или диапазон ячеек, на которые нужно сослаться.
Примечание: Если имя другого листа содержит знаки, не являющиеся буквами, необходимо заключить имя (или путь) в одинарные кавычки (‘).
-
Дважды щелкните ячейку, содержащую формулу, которую нужно изменить. Каждая ячейка или диапазон ячеек в Excel, на которые ссылается формула, выделяются своим цветом.
-
Выполните одно из указанных ниже действий.
-
Чтобы переместить ссылку на ячейку или диапазон, перетащите цветную границу к новой ячейке или диапазону.
-
Чтобы изменить количество ячеек в диапазоне, перетащите угол границы.
-
В строка формул
выделите ссылку в формуле и введите новую ссылку.
-
-
Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
-
Выделите ячейку с формулой.
-
В строке формул строка формул
выделите ссылку, которую нужно изменить. -
Для переключения между типами ссылок нажмите клавишу F4.
Дополнительные сведения о разных типах ссылок на ячейки см. в статье Обзор формул.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.