Как в экселе ячейку сделать постоянной: Как сохранить постоянную ссылку на ячейку формулы в 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 имеет широкий функционал по использованию данного арифметического действия. Давайте выясним, какими способами можно выполнить деление в Экселе.

Содержание

Выполнение деления

В Microsoft Excel деление можно произвести как при помощи формул, так и используя функции. Делимым и делителем при этом выступают числа и адреса ячеек.

Способ 1: деление числа на число

Лист Эксель можно использовать как своеобразный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (обратная черта) — «/».

1. Становимся в любую свободную ячейку листа или в строку формул. Ставим знак «равно» (=). Набираем с клавиатуры делимое число. Ставим знак деления (/). Набираем с клавиатуры делитель. В некоторых случаях делителей бывает больше одного. Тогда, перед каждым делителем ставим слеш (/).
2. Для того, чтобы произвести расчет и вывести его результат на монитор, делаем клик по кнопке Enter.

После этого Эксель рассчитает формулу и в указанную ячейку выведет результат вычислений.

Если вычисление производится с несколькими знаками, то очередность их выполнения производится программой согласно законам математики. То есть, прежде всего, выполняется деление и умножение, а уже потом — сложение и вычитание.

Как известно, деление на 0 является некорректным действием. Поэтому при такой попытке совершить подобный расчет в Экселе в ячейке появится результат «#ДЕЛ/0!».

Урок: Работа с формулами в Excel

Способ 2: деление содержимого ячеек

Также в Excel можно делить данные, находящиеся в ячейках.

1. Выделяем в ячейку, в которую будет выводиться результат вычисления. Ставим в ней знак «=». Далее кликаем по месту, в котором расположено делимое. За этим её адрес появляется в строке формул после знака «равно». Далее с клавиатуры устанавливаем знак «/». Кликаем по ячейке, в которой размещен делитель. Если делителей несколько, так же как и в предыдущем способе, указываем их все, а перед их адресами ставим знак деления.
2. Для того, чтобы произвести действие (деление), кликаем по кнопке «Enter».

Можно также комбинировать, в качестве делимого или делителя используя одновременно и адреса ячеек и статические числа.

Способ 3: деление столбца на столбец

Для расчета в таблицах часто требуется значения одного столбца разделить на данные второй колонки. Конечно, можно делить значение каждой ячейки тем способом, который указан выше, но можно эту процедуру сделать гораздо быстрее.

1. Выделяем первую ячейку в столбце, где должен выводиться результат. Ставим знак «=». Кликаем по ячейке делимого. Набираем знак «/». Кликаем по ячейке делителя.
2. Жмем на кнопку Enter, чтобы подсчитать результат.
3. Итак, результат подсчитан, но только для одной строки. Для того, чтобы произвести вычисление в других строках, нужно выполнить указанные выше действия для каждой из них. Но можно значительно сэкономить своё время, просто выполнив одну манипуляцию. Устанавливаем курсор на нижний правый угол ячейки с формулой. Как видим, появляется значок в виде крестика. Его называют маркером заполнения. Зажимаем левую кнопку мыши и тянем маркер заполнения вниз до конца таблицы.

Как видим, после этого действия будет полностью выполнена процедура деления одного столбца на второй, а результат выведен в отдельной колонке. Дело в том, что посредством маркера заполнения производится копирование формулы в нижние ячейки. Но, с учетом того, что по умолчанию все ссылки относительные, а не абсолютные, то в формуле по мере перемещения вниз происходит изменение адресов ячеек относительно первоначальных координат. А именно это нам и нужно для конкретного случая.

Урок: Как сделать автозаполнение в Excel

Способ 4: деление столбца на константу

Бывают случаи, когда нужно разделить столбец на одно и то же постоянное число — константу, и вывести сумму деления в отдельную колонку.

1. Ставим знак «равно» в первой ячейке итоговой колонки. Кликаем по делимой ячейке данной строки. Ставим знак деления. Затем вручную с клавиатуры проставляем нужное число.
2. Кликаем по кнопке Enter. Результат расчета для первой строки выводится на монитор.
3. Для того, чтобы рассчитать значения для других строк, как и в предыдущий раз, вызываем маркер заполнения. Точно таким же способом протягиваем его вниз.

Как видим, на этот раз деление тоже выполнено корректно. В этом случае при копировании данных маркером заполнения ссылки опять оставались относительными. Адрес делимого для каждой строки автоматически изменялся. А вот делитель является в данном случае постоянным числом, а значит, свойство относительности на него не распространяется. Таким образом, мы разделили содержимое ячеек столбца на константу.

Способ 5: деление столбца на ячейку

Но, что делать, если нужно разделить столбец на содержимое одной ячейки. Ведь по принципу относительности ссылок координаты делимого и делителя будут смещаться. Нам же нужно сделать адрес ячейки с делителем фиксированным.

1. Устанавливаем курсор в самую верхнюю ячейку столбца для вывода результата. Ставим знак «=». Кликаем по месту размещения делимого, в которой находится переменное значение. Ставим слеш (/). Кликаем по ячейке, в которой размещен постоянный делитель.
2. Для того, чтобы сделать ссылку на делитель абсолютной, то есть постоянной, ставим знак доллара ($) в формуле перед координатами данной ячейки по вертикали и по горизонтали. Теперь этот адрес останется при копировании маркером заполнения неизменным.
3. Жмем на кнопку Enter, чтобы вывести результаты расчета по первой строке на экран.
4. С помощью маркера заполнения копируем формулу в остальные ячейки столбца с общим результатом.

После этого результат по всему столбцу готов. Как видим, в данном случае произошло деление колонки на ячейку с фиксированным адресом.

Урок: Абсолютные и относительные ссылки в Excel

Способ 6: функция ЧАСТНОЕ

Деление в Экселе можно также выполнить при помощи специальной функции, которая называется ЧАСТНОЕ. Особенность этой функции состоит в том, что она делит, но без остатка. То есть, при использовании данного способа деления итогом всегда будет целое число. При этом, округление производится не по общепринятым математическим правилам к ближайшему целому, а к меньшему по модулю. То есть, число 5,8 функция округлит не до 6, а до 5.

Посмотрим применение данной функции на примере.

1. Кликаем по ячейке, куда будет выводиться результат расчета. Жмем на кнопку «Вставить функцию» слева от строки формул.
2. Открывается Мастер функций. В перечне функций, которые он нам предоставляет, ищем элемент «ЧАСТНОЕ». Выделяем его и жмем на кнопку «OK».
3. Открывается окно аргументов функции ЧАСТНОЕ. Данная функция имеет два аргумента: числитель и знаменатель. Вводятся они в поля с соответствующими названиями. В поле «Числитель» вводим делимое. В поле «Знаменатель» — делитель. Можно вводить как конкретные числа, так и адреса ячеек, в которых расположены данные. После того, как все значения введены, жмем на кнопку «OK».

После этих действий функция ЧАСТНОЕ производит обработку данных и выдает ответ в ячейку, которая была указана в первом шаге данного способа деления.

Эту функцию можно также ввести вручную без использования Мастера. Её синтаксис выглядит следующим образом:

=ЧАСТНОЕ(числитель;знаменатель)

Урок: Мастер функций в Excel

Как видим, основным способом деления в программе Microsoft Office является использование формул. Символом деления в них является слеш — «/». В то же время, для определенных целей можно использовать в процессе деления функцию ЧАСТНОЕ. Но, нужно учесть, что при расчете таким способом разность получается без остатка, целым числом. При этом округление производится не по общепринятым нормам, а к меньшему по модулю целому числу.

Изменение типа ссылки: относительная, абсолютная, смешанная

По умолчанию ссылка на ячейку является относительной ссылкой, которая означает, что ссылка относительна к расположению ячейки. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы фактически ссылаетесь на ячейку, которая находится на два столбца слева (C минус A) в одной строке (2). При копировании формулы, содержаной относительную ссылку на ячейку, эта ссылка в формуле изменится.

Например, при копировании формулы =B4*C4 из ячейки D4 в D5 формула в ячейке D5 корректируется на один столбец вправо и становится =B5*C5. Если вы хотите сохранить исходную ссылку на ячейку в этом примере при копировании, необходимо сделать ссылку на ячейку абсолютной, предшествуя столбцам (B и C) и строке (2) знаком доллара($). Затем при копировании формулы =$B$4*$C$4 из D4 в D5 формула остается той же.

В меньшей степени может потребоваться смешанные абсолютные и относительные ссылки на ячейки, предшествуя столбецу или значению строки знаком доллара, что исправит столбец или строку (например, $B 4 или C$4).

Чтобы изменить тип ссылки на ячейку, выполните следующее.

1. Выделите ячейку с формулой.

2. В строке формул строка формул выделите ссылку, которую нужно изменить.

3. Для переключения между типами ссылок нажмите клавишу F4.

   В приведенной ниже таблице по сумме обновляется тип ссылки при копировании формулы, содержащей ссылку, на две ячейки вниз и на две ячейки справа.

Работа в Excel с формулами и таблицами для чайников

Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек.2= (знак равенства)РавноМеньше>БольшеМеньше или равно>=Больше или равноНе равно

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.

Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.

При изменении значений в ячейках формула автоматически пересчитывает результат.

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

1. Поставили курсор в ячейку В3 и ввели =.
2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:

Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках.



Как в формуле Excel обозначить постоянную ячейку

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.

Все ссылки на ячейки программа считает относительными, если пользователем не задано другое условие. С помощью относительных ссылок можно размножить одну и ту же формулу на несколько строк или столбцов.

1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки в ячейке соотнесены со строкой.

Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

• $В$2 – при копировании остаются постоянными столбец и строка;
• B$2 – при копировании неизменна строка;
• $B2 – столбец не изменяется.

Как составить таблицу в Excel с формулами

Чтобы сэкономить время при введении однотипных формул в ячейки таблицы, применяются маркеры автозаполнения. Если нужно закрепить ссылку, делаем ее абсолютной. Для изменения значений при копировании относительной ссылки.

Простейшие формулы заполнения таблиц в Excel:

1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

Чтобы проверить правильность вставленной формулы, дважды щелкните по ячейке с результатом.

Как зафиксировать (закрепить) ячейку в Excel

При составлении формул в текстовом редакторе Эксель пользователь может столкнуться с необходимостью их копирования вдоль строки или столбца таблицы. Иногда математическое или текстовое выражение приходится копировать в прямоугольную область. Но если сделать это простым перетаскиванием, можно заметить, что адреса в формулах, по умолчанию установленные как относительные, будут меняться.

Чтобы этого не произошло, надо закрепить ячейку в Эксель — добиться этого поможет значок доллара, установленный в нужном месте.

Полное закрепление

Самый простоя способ, как закрепить ячейку при протягивании формулы — полностью зафиксировать ее, не давая изменяться адресу ни в одном из направлений. То есть, выбрав A1, можно рассчитывать, что этот адрес останется без изменений при любом копировании.

Такую формулу называют абсолютной — для ее получения следует поставить значки доллара перед каждой частью ссылки. То есть для A1 это будет $A$1. В ячейку можно записать, например, курс валют, который будет общим для всех расчетов. Или размеры МРОТ, расход бензина, процент начислений и другие коэффициенты.

Без знаков $ при копировании ссылка на A1 будет изменяться. Так, при копировании формулы «= A1/30» из ячейки E5 в E6 результатом станет «=A2/30». А если скопировать формулу из E5 в D5, данные для расчетов будут браться уже не из A1, а из B1. Зафиксировав ячейку в формуле Excel, пользователь делает адрес $A$1 постоянным при копировании по вертикали или горизонтали.

Фиксация по столбцу и строке

Если закрепить ячейку в формуле Excel нужно так, чтобы без изменений при копировании оставался только столбец, знак $ необходимо поставить в самом начале ссылки. Например, $A1. Вероятность сдвига в формуле сохранится только в том случае, если копирование происходит вдоль другого столбца.

Так, выражение «=$A1/E4» при копировании из E5 в D5 превратится в «$A1/D4», что позволит посчитать разные значения только с одним неизменным коэффициентом (курсом валют, процентом ставки). Но если пользователь копирует формулу вдоль столбца, то оставаться без изменений будет только первая часть ссылки: «А» — «A1», «A2», «A3» и т. д.

Еще один способ, как зафиксировать ячейку в Excel, предполагает фиксацию только по строке. В этом случае знак $ в ссылке ставится перед вторым идентификатором — например, A$1. В этом случае пользователь может протянуть формулу, копируя ее по прямоугольной области — но изменяться будет только столбец. Так можно сделать, установив в одной из строк проценты, которые будут разные для каждой категории. При копировании такая процентная ставка изменяется по столбцам.

Несколько особенностей закрепления

Закрепляя адрес ячейки в Эксель, пользователь экономит время на прописывание отдельных одинаковых формул. При этом снижается вероятность ошибки, даже если таких скопированных выражений в таблице будет всего 5-10. Когда количество формул увеличивается до нескольких десятков, без использования знака $ в адресе просто не обойтись.

Чтобы упростить прописывание доллара в ссылке и не переключаться каждый раз на другую раскладку клавиатуры (которых может быть и 2, и 3, и 4), можно выделить ту часть адреса, перед которой он ставится, и нажать F4. Если выделен весь адрес, при первом нажатии на функциональную клавишу $ ставится перед строкой и столбцом. При втором — закрепляется только столбец. При следующем — только строка. Четвертое нажатие возвращает нормальное написание адреса.

Читайте также:

Копирование формул без сдвига ссылок

Проблема

Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2.

Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:

Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.

Способ 1. Абсолютные ссылки

Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:

При большом количестве ячеек этот вариант, понятное дело, отпадает — слишком трудоемко.

Способ 2. Временная деактивация формул

Чтобы формулы при копировании не менялись, надо (временно) сделать так, чтобы Excel перестал их рассматривать как формулы. Это можно сделать, заменив на время копирования знак «равно» (=) на любой другой символ, не встречающийся обычно в формулах, например на «решетку» (#) или на пару амперсандов (&&). Для этого:

1. Выделяем диапазон с формулами (в нашем примере D2:D8)




2. Жмем Ctrl+H на клавиатуре или на вкладке Главная — Найти и выделить — Заменить (Home — Find&Select — Replace)




3. В появившемся диалоговом окне вводим что ищем и на что заменяем и в Параметрах (Options) не забываем уточнить Область поиска — Формулы. Жмем Заменить все (Replace all).




4. Копируем получившийся диапазон с деактивированными формулами в нужное место:




5. Заменяем # на = обратно с помощью того же окна, возвращая функциональность формулам.

Способ 3. Копирование через Блокнот

Этот способ существенно быстрее и проще.

Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas — Show formulas), чтобы включить режим проверки формул — в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:

Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:

Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:

Осталось только отжать кнопку Показать формулы (Show Formulas), чтобы вернуть Excel в обычный режим.

Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев — работает отлично.

Способ 4. Макрос

Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставьте новый модуль через меню Insert — Module  и скопируйте туда текст вот такого макроса:

Sub Copy_Formulas()
    Dim copyRange As Range, pasteRange As Range
    
    On Error Resume Next
    Set copyRange = Application.InputBox("Выделите ячейки с формулами, которые надо скопировать.", _
                                "Точное копирование формул", Default:=Selection.Address, Type:=8)
    If copyRange Is Nothing Then Exit Sub
    Set pasteRange = Application.InputBox("Теперь выделите диапазон вставки." & vbCrLf & vbCrLf & _
                                          "Диапазон должен быть равен по размеру исходному " & vbCrLf & _
                                          "диапазону копируемых ячеек.", "Точное копирование формул", _
                                          Default:=Selection.Address, Type:=8)
    
    If pasteRange.Cells.Count <> copyRange.Cells.Count Then
        MsgBox "Диапазоны копирования и вставки разного размера!", vbExclamation, "Ошибка копирования"
        Exit Sub
    End If
    
    If pasteRange Is Nothing Then
        Exit Sub
    Else
        pasteRange.Formula = copyRange.Formula
    End If
End Sub

Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:

Ссылки по теме

Как делить в экселе формула

Деление является одним из четырех самых распространенных арифметических действий. Редко встречаются комплексные расчеты, которые могут обойтись без него. Программа Excel имеет широкий функционал по использованию данного арифметического действия. Давайте выясним, какими способами можно выполнить деление в Экселе.

Выполнение деления

В Microsoft Excel деление можно произвести как при помощи формул, так и используя функции. Делимым и делителем при этом выступают числа и адреса ячеек.

Способ 1: деление числа на число

Лист Эксель можно использовать как своеобразный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (обратная черта) – «/».

1. Становимся в любую свободную ячейку листа или в строку формул. Ставим знак «равно»(=). Набираем с клавиатуры делимое число. Ставим знак деления (/). Набираем с клавиатуры делитель. В некоторых случаях делителей бывает больше одного. Тогда, перед каждым делителем ставим слеш (/).

После этого Эксель рассчитает формулу и в указанную ячейку выведет результат вычислений.

Если вычисление производится с несколькими знаками, то очередность их выполнения производится программой согласно законам математики. То есть, прежде всего, выполняется деление и умножение, а уже потом – сложение и вычитание.

Как известно, деление на 0 является некорректным действием. Поэтому при такой попытке совершить подобный расчет в Экселе в ячейке появится результат «#ДЕЛ/0!».

Способ 2: деление содержимого ячеек

Также в Excel можно делить данные, находящиеся в ячейках.

Выделяем в ячейку, в которую будет выводиться результат вычисления. Ставим в ней знак «=». Далее кликаем по месту, в котором расположено делимое. За этим её адрес появляется в строке формул после знака «равно». Далее с клавиатуры устанавливаем знак «/». Кликаем по ячейке, в которой размещен делитель. Если делителей несколько, так же как и в предыдущем способе, указываем их все, а перед их адресами ставим знак деления.

Можно также комбинировать, в качестве делимого или делителя используя одновременно и адреса ячеек и статические числа.

Способ 3: деление столбца на столбец

Для расчета в таблицах часто требуется значения одного столбца разделить на данные второй колонки. Конечно, можно делить значение каждой ячейки тем способом, который указан выше, но можно эту процедуру сделать гораздо быстрее.

Выделяем первую ячейку в столбце, где должен выводиться результат. Ставим знак «=». Кликаем по ячейке делимого. Набираем знак «/». Кликаем по ячейке делителя.

Жмем на кнопку Enter, чтобы подсчитать результат.

Как видим, после этого действия будет полностью выполнена процедура деления одного столбца на второй, а результат выведен в отдельной колонке. Дело в том, что посредством маркера заполнения производится копирование формулы в нижние ячейки. Но, с учетом того, что по умолчанию все ссылки относительные, а не абсолютные, то в формуле по мере перемещения вниз происходит изменение адресов ячеек относительно первоначальных координат. А именно это нам и нужно для конкретного случая.

Способ 4: деление столбца на константу

Бывают случаи, когда нужно разделить столбец на одно и то же постоянное число – константу, и вывести сумму деления в отдельную колонку.

Ставим знак «равно» в первой ячейке итоговой колонки. Кликаем по делимой ячейке данной строки. Ставим знак деления. Затем вручную с клавиатуры проставляем нужное число.

Кликаем по кнопке Enter. Результат расчета для первой строки выводится на монитор.

Как видим, на этот раз деление тоже выполнено корректно. В этом случае при копировании данных маркером заполнения ссылки опять оставались относительными. Адрес делимого для каждой строки автоматически изменялся. А вот делитель является в данном случае постоянным числом, а значит, свойство относительности на него не распространяется. Таким образом, мы разделили содержимое ячеек столбца на константу.

Способ 5: деление столбца на ячейку

Но, что делать, если нужно разделить столбец на содержимое одной ячейки. Ведь по принципу относительности ссылок координаты делимого и делителя будут смещаться. Нам же нужно сделать адрес ячейки с делителем фиксированным.

Устанавливаем курсор в самую верхнюю ячейку столбца для вывода результата. Ставим знак «=». Кликаем по месту размещения делимого, в которой находится переменное значение. Ставим слеш (/). Кликаем по ячейке, в которой размещен постоянный делитель.

Для того, чтобы сделать ссылку на делитель абсолютной, то есть постоянной, ставим знак доллара ($) в формуле перед координатами данной ячейки по вертикали и по горизонтали. Теперь этот адрес останется при копировании маркером заполнения неизменным.

Жмем на кнопку Enter, чтобы вывести результаты расчета по первой строке на экран.

После этого результат по всему столбцу готов. Как видим, в данном случае произошло деление колонки на ячейку с фиксированным адресом.

Способ 6: функция ЧАСТНОЕ

Деление в Экселе можно также выполнить при помощи специальной функции, которая называется ЧАСТНОЕ. Особенность этой функции состоит в том, что она делит, но без остатка. То есть, при использовании данного способа деления итогом всегда будет целое число. При этом, округление производится не по общепринятым математическим правилам к ближайшему целому, а к меньшему по модулю. То есть, число 5,8 функция округлит не до 6, а до 5.

Посмотрим применение данной функции на примере.

Кликаем по ячейке, куда будет выводиться результат расчета. Жмем на кнопку «Вставить функцию» слева от строки формул.

Открывается Мастер функций. В перечне функций, которые он нам предоставляет, ищем элемент «ЧАСТНОЕ». Выделяем его и жмем на кнопку «OK».

После этих действий функция ЧАСТНОЕ производит обработку данных и выдает ответ в ячейку, которая была указана в первом шаге данного способа деления.

Эту функцию можно также ввести вручную без использования Мастера. Её синтаксис выглядит следующим образом:

Как видим, основным способом деления в программе Microsoft Office является использование формул. Символом деления в них является слеш – «/». В то же время, для определенных целей можно использовать в процессе деления функцию ЧАСТНОЕ. Но, нужно учесть, что при расчете таким способом разность получается без остатка, целым числом. При этом округление производится не по общепринятым нормам, а к меньшему по модулю целому числу.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Написать формулу деления в программе эксель довольно простая задача. Но в данной статье мы рассмотрим два способа, как можно сделать деление на число в программе эксель.

На рис. 1 представлен пример, в котором в столбцах с «А2» по «А6» и с «В2» по «В6» находятся числа. Задача заключается в следующем: необходимо значение в ячейки «А2» разделить на значение из ячейки «В2», с «А3» на «В3» и т.д.

Первый способ.

В ячейки «С2» пишем следующую формулу: «=A2/B2». Затем копируем ячейку «С2» и вставляем в оставшиеся ячейки с «С3» по «С6».

Обратите внимание, что в ячейки «С3» программа эксель выдала ошибку «#ДЕЛ/0!», которая означает, что делить число на ноль нельзя. Второй способ позволяет уходить от этой ошибки.

Второй способ.

Чтобы избегать ошибки «#ДЕЛ/0!» воспользуемся специальной встроенной функцией в экселе: ЕСЛИОШИБКА(Х;Y), где X– выполняемое любое математическое действие, а Y – это значение, которое вставляется в ячейку при появление ошибки.

Пишем следующую формулу деления в ячейки «D2»: =ЕСЛИОШИБКА(A2/B2;0), т.е. если будет возникать ошибка вида «#ДЕЛ/0!», то вместо неё на экран будет выводиться ноль, таким образом мы будем избегать её. Копируем формулу из ячейки «D2» в ячейки с «D3» по «D6».

Всем привет! Деление – это одно из четырёх наиболее известных видов манипуляций с числами. Достаточно редкий случай при произведении расчётов, когда они обходятся без деления одного числа на другое.

Редактор таблиц «Microsoft Excel» обладает достаточно мощную функциональность в вопросе получения частного. Итак, сегодня мы с вами рассмотрим шесть методов, с помощью которых можно разделить одно число на другое прямо в таблице.

В редакторе таблиц «Microsoft Excel» есть возможность произведения расчетов, как с помощью привычных всем формул, так и с помощью мастера функций. При всём при этом в качестве делимого и делителя выступают числа, адреса которых вы указываете при составлении формулы.

Метод 1 – Частное двух чисел

На самом деле, редактор таблиц «Microsoft Excel» достаточно многофункциональная вещь. К примеру, его можно использовать исключительно как калькулятор, используя соответствующие знаки. К примеру, для умножения нужно использовать звёздочку («*»), а для деления достаточно слэша («/»).

1) Кликаем по любой пустой ячейке на листе, либо же по строке с формулами. В начале уравнения необходимо поставить знак равенства («=»). Для начала нужно набрать делимое, после этого поставить соответствующий знак (как мы уже сказали ранее, это слэш) и написать делитель.

В случае, когда в вашем уравнении больше одного делителя, то перед каждым из них требуется поставить соответствующий знак.

2) Собственно, осталось лишь получить ответ на написанное уравнения. Для этого нажмите клавишу Enter на клавиатуре.

Как только вы нажмёте данную кнопку программа произведёт расчёты и выведет результат в ячейку или строку для формул, в которую было вписано уравнение.

В случае, когда вы напишите уравнение, которое будет содержать в себе не только деление, а ещё и, скажем, сложение – то все расчёты будут произведены относительно базовых законов математики.

Если говорить проще, то первоочерёдными для программы будут являться деление и умножение, а уже после этого будет выполнено сложение или вычитание.

Как большинство пользователей знают, разделить какую-либо цифру или число на ноль невозможно. Поэтому, при попытке произвести операция с делителем равным нулю, то на экране будет отображена ошибка «#ДЕЛ/0!».

Метод 2 – Частное чисел из ячеек

Помимо этого у вас есть возможность разделить числа, которые располагаются в определённых ячейках.

1) Для начала нужно кликнуть по ячейке, в которую будет выведен результат операции. После этого вставляем в неё знак равенства («=»). Теперь нужно кликнуть по ячейке, в которой располагается делимое число.

После этого её месторасположение отобразится в ячейке, в которую ранее мы поставили знак равенства. Опять-таки, после этого в ячейке, в которой будет результат устанавливаем знак деления и кликаем на делитель. В случае, когда делителей несколько поочерёдно отмечаем их, не забывая про знак деления.

Помимо этого можно производить «комбинированное» деление. То есть помимо стандартных делимых или делителей из ячеек, вы можете указать их значения самостоятельно, просто набрав на клавиатуре нужную комбинацию цифр.

Метод 3 – Частное двух столбцов

Так же при произведении расчётов в табличных документах зачастую требуется разделить числа из одного столбца на числа из второго. Вы, конечно же, можете пользоваться описанным ранее способом, но если в столбце много ячеек, то это займёт много времени. А данный метод выполняется буквально за несколько кликов.

1) Для начала нужно выделить ячейку, в которую в дальнейшем будет выводится результат. Желательно, чтобы она была первой в новом столбце и находилась на одном уровне с числами из двух других.

После этого, как и в предыдущем способе, ставим в ней знак равенства, кликаем по ячейке с делимым, после ставим знак деления и кликаем по ячейке с делителем.

3) После этого мы получим результат, но он будет актуален всего лишь для двух первых ячеек. Чтобы получить результат деления столбца на столбец можно поэтапно выполнять эти действия, но мы поступим значительно проще попросту «размножив» уже написанную формулу.

Для этого наведите курсор на правый нижний угол той ячейки, в которой располагается результат. После этого появится символ в виде крестика, или по-другому «значок заполнения». Собственно, кликаем на него и удерживаем, а после начинаем тянуть его вниз к окончанию данной таблицы.

После того, как вы отпустите левую клавишу мыши, программа просчитает результат для всех ячеек, которые были задействованы в применённой ранее формуле. Всё дело в том, что этими действиями мы просто скопировали формулу.

Всё дело в том, что как вы знаете, номер каждой ячейки отличается от предыдущего на один пункт, а данная формула берёт за основу только основную информацию (к примеру, столбец «А» и «B»), а после этого начинает рассчитывать ячейки по мере их выделения формулой, что нам и помогает в данном конкретном случае.

Метод 4 – Частное столбца и определённого числа

Так же в редакторе таблиц «Microsoft Excel» вы сможете разделить числа из ячеек целого столбца на какое-либо одно неизменное число. Такие числа так же называются «константами».

1) Как и во всех описанных ранее способах, нам необходимо выставить знак равенства в первой ячейке столбца, в который будет выведен результат применения формулы. Нажимаем на делимое (первую ячейку нужного столбца), а после этого ставим слэш. Вручную с помощью клавиатуры вписываем делитель.

После этого, как вы можете заметить, данная формула будет применена ко всем ячейкам из столбца с делимым. Как вы можете заметить, в данном примере адреса ячеек всё так же оставались относительными, как и в предыдущем способе.

Поэтому в формуле адрес делимого менялся для каждой новой ячейки. Что абсолютно не касается делителя, так как в этот раз мы установили константу (постоянное число), он остался неизменным.

Метод 5 – Частное столбца и ячейки

Так же существует способ получения частного содержимого целого столбца и одной ячейки. Но если использовать один из предыдущих способов, к примеру, в котором мы делили столбец на столбец, то ячейка каждый раз будет смещаться, а нам нужно превратить её содержимое в константу. Сделать это можно следующим образом.

1) Выделяем самую первую ячейку столбца, в который будет выводится результат вычисления. Устанавливаем в ней знак равенства. Теперь нажимаем на первую ячейку в столбце, который будет являться делимым, после чего устанавливаем знак деления.

После нажимаем на ячейку, в которой располагается нужный нам делитель.

Метод 6 – Использование мастера функций

Так же разделить числа в редакторе таблиц «Microsoft Excel» можно с помощью мастера функций, а конкретно, с помощью одного инструмента, который называется «ЧАСТНОЕ». Главная особенность данной функции в том, что она делит число на число не оставляя остаток.

Иными словами, если вы будете использовать данную функцию, то в результате всегда будете получать целое число – программа просто его округлит.

При чём округляются числа не по привычным математическим правилам относительно ближайшего модуля, а относительно меньшего модуля. Иными словами, если у вас получился ответ 5,9, то по правилам он должен быть округлён до 6, но программа округлит его до 5, то есть просто «отрежет».

1) Кликаем по ячейке, в которую вы хотите вставить результат, после нажмите на клавишу «Вставить функцию».

3) Вы попадёте в раздел с «аргументами» функции «ЧАСТНОЕ». У данной функции существует всего два аргумента – числитель и знаменатель.

Вписывать их нужно в одноимённые графы. В раздел «Числитель» нужно прописать делимое, а в «Знаменатель», соответственно, делитель.

Вы можете прописывать как определённые числа, так и адреса ячеек с нужной вам информацией. Как только вы указали все нужные данные, кликните по клавише «ОК».

Так же вы можете прописать её вручную Для этого в ячейке или в графе с функциями нужно ввести следующее

Итак, сегодня мы разобрали шесть способов деления чисел в редакторе таблиц «Microsoft Excel». Надеемся, что данная статья смогла помочь вам.

Сделайте формулу Excel постоянной

Q. Иногда удобство Excel может стать
инвалидность. Например, если вы переместите формулу в новое место, Excel
автоматически изменит формулу, чтобы отразить новое местоположение. Но
это автоматическое действие может быть болезненным, если вы хотите, чтобы формула оставалась
постоянный — чтобы не приспосабливаться к новому месту. Я знаю, что могу добавить вручную
$ символов к частям формулы я хочу, чтобы они оставались постоянными, но
если вам нужно переместить несколько формул, это займет много времени.Любые идеи
как обойти эту проблему?

A. Вы хотите получить команду Excel для применения
абсолютная ссылка на перемещенную формулу, а не относительную
ссылка. Да, ты можешь это сделать.

Но сначала давайте сделаем резервную копию для читателей, которые не знакомы с
эта техника. Например, если у вас есть формула ниже

, и вы переместите его в другое место, ссылки изменятся
автоматически на

Но если вы хотите, чтобы он ссылался на A1 и A2 даже после перемещения, вы
можно изменить формулу на

, добавив символы $, как показано.

Более простой способ сделать формулу абсолютной — дважды щелкнуть
ячейку, содержащую исходную формулу, а затем с помощью мыши
выделите формулу, нажмите F4, а затем Enter. Это добавит
соответствующие символы $ в нужных местах для преобразования формулы в
абсолютная ссылка.

Ярлыки

Автосумма в Excel
автоматически вводит сумму любого столбца или строки
ценности.Выберите ячейку ниже или справа от введенных вами значений.
суммирование и нажмите кнопку AutoSum . Ты даже можешь
добавить несколько столбцов и строк значений одновременно,
выделение смежных ячеек и нажатие AutoSum .

Клавиатура AutoSum :
Если вам нравится клавиатура, вы можете сделать то же самое,
нажатие Alt– = (это Alt и клавиши равенства).

Найдите страницу в Word: чтобы быстро перейти к
определенную страницу в длинном документе Word, нажмите клавишу F5, которая
запускает функцию Select и набирает страницу
номер, который вы хотите.

Другой способ вызвать этот экран с помощью
мышь — это дважды щелкнуть поле страницы в нижнем левом углу
окно документа (то, что указывает номер страницы).

Сохраняйте постоянное значение в Excel

При создании формулы в Excel или большинстве других программ для работы с электронными таблицами нам иногда требуется, чтобы одно из значений было динамическим (например, мы можем изменить его в одном месте и воздействовать везде, где на него ссылаются), но оставаться постоянным, когда мы реплицируем эту формулу по строкам или столбцам.

Чтобы сохранить постоянное значение в Excel, выполните следующие действия:

1. Создайте ячейку с постоянным значением, на которое вы хотите ссылаться
2. Создайте формулу в ячейке, которая выполняет ваши вычисления
3. В формуле, в которой вы ссылаетесь на значение, созданное на шаге 1, добавьте «$» перед буквой (представляющей столбец) и числом (представляющим строку).

Пример сохранения постоянного значения в Excel

У нас есть ячейка, скажем, ячейка A1, которая просто содержит текст «Процентная ставка», который представляет собой просто ярлык, чтобы мы знали, с чем имеем дело.

Далее у нас есть ячейка B1, которая содержит фактическую процентную ставку, мы представим наш процент в виде десятичной дроби, так что 10% будет .1.

Теперь у нас есть три столбца:

1. Основная сумма
2. Простые проценты
3. Итого (основная сумма + простые проценты)

, в A3 мы помещаем 1000 долларов, в A4 мы помещаем 2000 долларов, затем выделяем обе ячейки, захватываем нижний правый угол и перетаскиваем вниз еще 8 строк, так что теперь у вас есть значения от 1000 до 10000 долларов.

Затем в B3 мы вводим формулу для расчета процентов, умножая наше первое значение (1000 долларов в ячейке A3) на процентную ставку (0,1 или 10% в B1).

= (A3 * B1)

В ячейке должно отображаться 100 долларов.

Теперь, как и при создании 10 строк с шагом в 1000 долларов, мы можем захватить правый нижний угол и потянуть вниз. Проблема в том, что обе ссылки изменят ячейки, но мы хотим, чтобы только первая (основная сумма) вычислялась со следующей ячейки ниже (A4, A5 и т. Д.).мы хотим, чтобы ячейка процентной ставки всегда рассчитывалась из B1 (а не из B2, B3 и т. д., как это естественно предполагает электронная таблица).

Итак, чтобы исправить это, мы добавляем «$» перед буквой столбца и номером строки для нашей ячейки процентной ставки, изменяя формулу в B3, чтобы теперь она выглядела так:

= (A3 * 1 миллиард долларов)

Теперь, когда мы берем и перетаскиваем, процентная ставка всегда будет рассчитываться на основе значения в B1.

Наконец, чтобы закончить, давайте сделаем еще один форум в C3, чтобы добавить участника к интересу, используя простую формулу = (A3 + B3) в C3.

Теперь перетащите C3 вниз, и вы увидите, как увеличиваются приращения, и теперь мы знаем, сколько простых процентов мы заработаем в год на 1000–10 000 долларов.

Хотите, чтобы все изменилось? Измените .1 в ячейке B1 на .05 (5%), и все изменится по всем направлениям.

Какие ваши любимые советы и рекомендации по работе с Excel? Нужна помощь в том, чтобы разобраться в таблице? Напишите мне в Twitter @vsellis или в Google + на gplus.to/scottellis.

Узнавайте что-то новое каждый день!

Заменить формулу ее результатом

Заменить формулы их расчетными значениями

При замене формул их значениями Excel безвозвратно удаляет формулы.Если вы случайно заменили формулу значением и хотите восстановить формулу, нажмите Отменить сразу после ввода или вставки значения.

1. Выберите ячейку или диапазон ячеек, содержащих формулы.

   Если формула является формулой массива, выберите диапазон, содержащий формулу массива.

   Как выбрать диапазон, содержащий формулу массива

   1. Щелкните ячейку в формуле массива.

   2. На вкладке Home в группе Editing щелкните Find & Select , а затем щелкните Go To .

   3. Щелкните Special .

   4. Щелкните Текущий массив .

2. Нажмите Копировать .

3. Нажмите Вставить .

4. Щелкните стрелку рядом с полем Параметры вставки , а затем щелкните Только значения .

В следующем примере показана формула в ячейке D2, которая умножает ячейки A2, B2 и скидку, полученную из C2, для расчета суммы счета-фактуры для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки в другой лист или книгу, вы можете преобразовать формулу в ее ячейке в ее значение, выполнив следующие действия:

1. Нажмите F2, чтобы отредактировать ячейку.

2. Нажмите F9, а затем нажмите ENTER.

После преобразования ячейки из формулы в значение значение отображается как 1932,322 в строке формул. Обратите внимание, что 1932,322 — это фактическое вычисленное значение, а 1932,32 — это значение, отображаемое в ячейке в денежном формате.

Совет: При редактировании ячейки, содержащей формулу, вы можете нажать F9, чтобы навсегда заменить формулу ее вычисленным значением.

Заменить часть формулы вычисленным значением

Бывают случаи, когда вы хотите заменить только часть формулы ее вычисленным значением.Например, вы хотите зафиксировать значение, которое используется в качестве первоначального платежа по автокредиту. Этот первоначальный взнос был рассчитан на основе процента от годового дохода заемщика. На данный момент эта сумма дохода не изменится, поэтому вы хотите заблокировать авансовый платеж в формуле, которая рассчитывает платеж на основе различных сумм ссуды.

Когда вы заменяете часть формулы ее значением, эта часть формулы не может быть восстановлена.

1. Щелкните ячейку, содержащую формулу.

2. В строке формул выберите часть формулы, которую вы хотите заменить вычисленным значением. Когда вы выбираете часть формулы, которую хотите заменить, убедитесь, что вы включили весь операнд. Например, если вы выбираете функцию, вы должны выбрать полное имя функции, открывающую скобку, аргументы и закрывающую скобку.

3. Чтобы рассчитать выбранную порцию, нажмите F9.

4. Чтобы заменить выбранную часть формулы вычисленным значением, нажмите ENTER.

В Excel в Интернете результаты уже отображаются в ячейке книги, а формула отображается только в строке формул.

Как закрепить строки или столбцы заголовков в Excel

Недавно я помог другу с проектом Excel.Когда я спросил своего друга, как выглядит таблица, он заколебался. Никогда не было хорошим знаком. Он посмотрел на меня и сказал: « Это слишком сложно, ». Оказывается, с данными все в порядке, но ему нужен был « прикрепленный заголовок Excel ». Мне нужно было показать ему, как закрепить строки и столбцы в Excel, чтобы ключевая информация всегда была видна, когда он прокручивает лист.

Это был для меня хороший урок. Это напомнило мне, что у всех нас разный уровень опыта и разная терминология. Например, если вы новичок в Microsoft Excel, вы, вероятно, не будете думать о том, «как заморозить панели».«Это не описание вашей проблемы. Мой друг думал в терминах « липкий заголовок » или « плавающий заголовок Excel ».

Когда дело доходит до электронных таблиц, вы можете «заморозить» или «разделить панели». Разделение панелей немного сложнее, потому что у вас есть несколько представлений ваших данных на листе. Итак, для простоты в этом руководстве мы рассмотрим закрепление панелей.

Зачем блокировать ячейки электронной таблицы

Преимущество блокировки или замораживания ячеек заключается в том, что вы видите важную информацию независимо от прокрутки.Данные разделенной панели остаются неизменными. Ваша электронная таблица может содержать панели с заголовками столбцов, несколькими строками, несколькими столбцами или и тем, и другим.

В противном случае легко потерять фокус на большом листе, если у вас нет заголовков или идентификаторов столбцов. Бывали случаи, когда я вводил данные только для того, чтобы обнаружить, что у меня была одна ячейка, и возникали ошибки в формулах Excel.

Обычно ячейки, которые вы хотите закрепить, представляют собой метки, подобные заголовкам столбцов. С таким же успехом они могут быть целым столбцом, например именами сотрудников.А в некоторых случаях может потребоваться заблокировать несколько столбцов и строк.

Давайте рассмотрим несколько примеров фиксированных панелей.

1 — Как закрепить верхнюю строку

Этот пример закрепления строки, пожалуй, самый распространенный, потому что людям нравится блокировать верхнюю строку, содержащую заголовки столбцов, как в примере ниже. Другое решение — отформатировать электронную таблицу как таблицу Excel.

Закрепление верхнего ряда с заголовками

1. Откройте рабочий лист.
2. Щелкните вкладку View на ленте.
3. На кнопке Freeze Panes щелкните маленький треугольник в правом нижнем углу. У вас должно появиться новое меню с 3 вариантами.

Pin Основные параметры фиксированных панелей

4. Щелкните параметр Freeze Top Row .
5. Прокрутите свой лист до и убедитесь, что первая строка остается заблокированной вверху.

Вы должны увидеть более темную горизонтальную линию под первым рядом.

Сочетание клавиш — заблокировать верхнюю строку

Мне нравится вводить этот ярлык медленно в первый раз, чтобы видеть присвоение букв по мере ввода.В приведенном ниже примере, как только я нажимаю клавишу Alt , я вижу назначения клавиатуры. Некоторые люди предпочитают добавлять команду Freeze Panes на панель инструментов Quick Access , потому что они часто ее используют.

Сочетания клавиш

Alt + w + f + r

2 — Как заблокировать первый столбец

Похожий сценарий, когда вы хотите закрепить столбец. Опять же, часто это первая колонка. Я предполагаю, что Microsoft провела исследование, чтобы выяснить наиболее распространенные параметры в этом подменю.

Я считаю эту опцию полезной, когда у меня есть электронная таблица с множеством столбцов, и мне нужно заполнить данные, и я не хочу использовать форму данных Excel.

PinFreeze первый столбец Excel

1. Откройте рабочий лист Excel.
2. Щелкните вкладку View на ленте.
3. На кнопке Freeze Panes щелкните маленький треугольник в правом нижнем углу. Вы должны получить новое меню с 3 вариантами.
4. Выберите опцию Зафиксировать первый столбец .
5. Прокрутите свой лист до и убедитесь, что левый столбец остается неизменным .

Сочетание клавиш — заблокировать первый столбец

Альтернативный + w + f + c

3 — Как закрепить верхнюю строку и первый столбец

PinThink «Freeze Pane» с точки зрения строк и столбцов

Это мой любимый вариант Freeze Pane. Если вы посмотрите на исходные параметры Freeze Panes от Microsoft, их нет ни для верхней строки, ни для первого столбца.Вместо этого мы будем использовать общую опцию под названием Freeze Panes .

Подтекст гласит: «Держать строки и столбцы видимыми, пока остальная часть листа прокручивается (в зависимости от текущего выбора)». Некоторые люди сбиты с толку, поскольку думают, что им нужно выделить данные, чтобы сделать выбор.

Вместо этого воспринимает выделение как первую ячейку за пределами фиксированного столбца и строки . Если бы я хотел заблокировать верхнюю строку и верхний столбец, эта ячейка выбора была бы B2 или Nevada .Независимо от того, прокручиваю ли я вниз или вправо, первая исчезающая ячейка — это B2.

Ячейка выбора области закрепления

1. Откройте электронную таблицу Excel.
2. Щелкните ячейку B2 .
3. Щелкните вкладку View на ленте.
4. На кнопке Freeze Panes щелкните маленький треугольник в правом нижнем углу. Вы должны получить новое меню с 3 вариантами.
5. Щелкните опцию Freeze Panes .
6. Прокрутите свой лист до и убедитесь, что первая строка остается наверху .
7. Прокрутите свой лист до и убедитесь, что ваш первый столбец остается заблокированным слева.

Сочетание клавиш — закрепить области

Альтернативный + w + f + f

✪ Убедитесь, что вы сначала щелкнули ячейку.

4- Заблокировать несколько столбцов или строк

Иногда я получаю таблицы Excel, где автор помещает описательный текст над данными. Заголовки моих столбцов находятся не в строке 1, а ниже.Или мне нужно заблокировать несколько столбцов слева. В приведенном ниже примере я хочу заблокировать столбцы A и B и строки 1-5.

PinExample of Freeze Pane с несколькими столбцами и строками

Процесс такой же, мне просто нужно щелкнуть ячейку, которая останавливает фиксированную область. В данном случае это будет ячейка C6 или «Поле регионов».

Другой способ думать об этом — ячейка, которая находится в верхнем левом углу, где встречаются ваши заблокированные строки и заблокированные столбцы. Содержимое вверху и слева заморожено.

Все в красных ящиках будет заблокировано. Обратной стороной является то, что вы можете отказаться от большой площади экрана.

Краткие предупреждения

Есть несколько правил, связанных с этой функцией. Если вы не соблюдаете эти правила, вы можете обнаружить, что закрепленные области не работают.

• Если вам не нравятся ваши настройки, вы можете использовать команду Unfreeze Panes .
• Эта функция не будет работать с защищенным листом или Page Layout View .
• Если вы редактируете значение в строке формул , меню Просмотр будет отключено.

Как видите, закрепить панели в Excel легко. Вы можете закрепить верхнюю строку, первый столбец, оба или подмножество данных, в зависимости от того, что вы хотите с ними делать. Гибкость — это то, что делает Microsoft Excel такой отличной программой для систематизации и анализа информации из любой области.

Вам могут понравиться эти уроки по Excel

Переключение между относительными и абсолютными ссылками

По умолчанию ссылка на ячейку — , относительная .Например, когда вы ссылаетесь на ячейку A2 из ячейки C2, вы фактически имеете в виду ячейку, которая находится на два столбца слева (C минус A) и в той же строке (2). Формула, содержащая относительную ссылку на ячейку, изменяется по мере ее копирования из одной ячейки в другую. Например, если вы скопируете формулу = A2 + B2 из ячейки C2 в C3, ссылки формулы в C3 уменьшатся на одну строку и станут = A3 + B3 .

Если вы хотите сохранить исходную ссылку на ячейку при ее копировании, вы «блокируете» ее, помещая знак доллара ( $ ) перед ссылками на ячейки и столбцы.Например, когда вы копируете формулу = $ A $ 2 + $ B $ 2 из C2 в D2, формула остается точно такой же. Это абсолютная ссылка.

В менее частых случаях вы можете сделать ссылку на ячейку «смешанной», поставив перед столбцом или значением строки знак доллара, чтобы «заблокировать» столбец или строку (например, $ A2 или B $ 3) . Чтобы изменить тип ссылки на ячейку:

1. Выберите ячейку, содержащую ссылку на ячейку, которую вы хотите изменить.

2. В строке формул щелкните ссылку на ячейку, которую нужно изменить.

3. Нажмите + T, чтобы перемещаться по комбинациям.

   В следующей таблице показано, что произойдет, если скопировать формулу в ячейку A1, содержащую ссылку. В частности, формула копируется на две ячейки вниз и на две ячейки вправо, в ячейку C3.

Операторы вычислений и порядок операций

Заменить формулу ее результатом в Excel для Mac

Внимание! При замене формулы ее значением Excel безвозвратно удаляет формулу.Если вы случайно заменили формулу значением и хотите восстановить формулу, нажмите Отменить сразу после ввода или вставки значения. Перед заменой формулы ее результатами рекомендуется сделать копию книги.

1. Выберите ячейку, содержащую формулу.

   Если формула является формулой массива, выберите диапазон, содержащий формулу массива.

2. На вкладке Home щелкните Copy .

3. На вкладке Home нажмите Paste .

4. Щелкните стрелку рядом с полем Параметры вставки , а затем щелкните Только значения .

Внимание! При замене формулы ее значением Excel безвозвратно удаляет формулу. Если вы случайно заменили формулу значением и хотите восстановить формулу, на панели инструментов Standard нажмите Undo сразу после ввода или вставки значения. Перед заменой формулы ее результатами рекомендуется сделать копию книги.

1. Выберите ячейку, содержащую формулу.

   Если формула является формулой массива, выберите диапазон, содержащий формулу массива.

2. На панели инструментов Standard нажмите Copy .

3. На панели инструментов Standard нажмите Paste .

4. Щелкните стрелку рядом с полем Параметры вставки , а затем щелкните Только значения .

Панели замораживания и опции просмотра

Урок 17: Закрепление панелей и параметров просмотра

/ ru / excel2013 / functions / content /

Введение

Всякий раз, когда вы работаете с большим количеством данных, может быть сложно сравнить информацию в вашей книге.К счастью, Excel включает в себя несколько инструментов, которые упрощают просмотр содержимого из разных частей книги одновременно, например, возможность заморозить панелей и разделить рабочий лист

Необязательно: загрузите нашу рабочую тетрадь.

Чтобы закрепить строки:

Вы можете захотеть постоянно видеть определенные строки или столбцы на вашем листе, особенно ячеек заголовка . Зафиксировав строк или столбцов на месте, вы сможете прокручивать содержимое, продолжая просматривать замороженные ячейки.

1. Выберите строку под строкой (строками), которую вы хотите заморозить . В нашем примере мы хотим зафиксировать строки 1 и 2 , поэтому мы выберем строку 3 . Выбор строки 3
2. Щелкните вкладку View на ленте .
3. Выберите команду Freeze Panes , затем выберите Freeze Panes из раскрывающегося меню. Щелчок по Freeze Panes
4. Строки будут замороженными на месте, как показано серым строкой .Вы можете прокрутить лист вниз, продолжая просматривать закрепленные строки вверху. В нашем примере мы прокрутили вниз до строки 18 . Замороженные строки

Чтобы закрепить столбцы:

1. Выберите столбец справа от столбца (столбцов), который вы хотите закрепить за . В нашем примере мы хотим закрепить столбец A , поэтому мы выберем столбец B . Выбор столбца B
2. Щелкните вкладку View на ленте .
3. Выберите команду Freeze Panes , затем выберите Freeze Panes из раскрывающегося меню. Щелчок по Freeze Panes
4. Столбец будет замороженным на месте, как показано серой строкой . Вы можете прокрутить на листе, продолжая просматривать закрепленный столбец слева. В нашем примере мы прокрутили до столбца E . Закрепленный столбец

Чтобы разморозить строк или столбцов, щелкните команду Freeze Panes , затем выберите Unfreeze Panes из раскрывающегося меню.

Размораживание строки

Если вам нужно зафиксировать только верхнюю строку (строка 1) или первый столбец (столбец A) на листе, вы можете просто выбрать Freeze Top Row или Freeze First Column из раскрывающегося меню .

Замораживание только верхней строки книги

Другие варианты просмотра

Если ваша книга содержит много контента, иногда бывает сложно сравнивать разные разделы. Excel включает дополнительные параметры, упрощающие просмотр и сравнение ваших книг.Например, вы можете выбрать открыть новое окно для своей книги или разделить рабочий лист на отдельные панели.

Чтобы открыть новое окно для текущей книги:

Excel позволяет одновременно открывать нескольких окон для одной книги. В нашем примере мы будем использовать эту функцию для сравнения двух разных листов из одной книги.

1. Щелкните вкладку View на ленте , затем выберите команду New Window .При нажатии команды «Новое окно»
2. Появится новое окно для книги. Та же книга открывается в двух отдельных окнах.
3. Теперь вы можете сравнивать разные рабочие листы из одной книги в разных окнах. В нашем примере мы выберем рабочий лист 2013 Sales Detail View для сравнения продаж 2012 и 2013 . Выбор рабочего листа в новом окне

Если у вас открыто несколько окон одновременно, вы можно использовать команду Упорядочить все , чтобы быстро их переставить.

Щелчок по Упорядочить все

Чтобы разделить лист:

Иногда вам может понадобиться сравнить разные разделы одной и той же книги, не создавая новое окно. Команда Split позволяет разделить рабочий лист на несколько панелей, которые прокручиваются отдельно.

1. Выберите ячейку , в которой вы хотите разделить рабочий лист. В нашем примере мы выберем ячейку C7 . Выбор ячейки C7
2. Щелкните вкладку View на ленте , затем выберите команду Split .Щелчок по команде Разделить
3. Книга будет разделена на на разные панели . Вы можете прокручивать каждую панель отдельно, используя полосы прокрутки , что позволяет сравнивать различные разделы книги. Разделенный рабочий лист

После создания разделения вы можете щелкнуть и перетащить вертикальные и горизонтальные разделители, чтобы изменить размер каждый раздел.

Чтобы удалить разделение, снова щелкните команду Разделить .

Вызов!

1. Откройте существующую книгу Excel.Если хотите, можете воспользоваться нашим учебным пособием.
2. Попробуйте закрепить строку или столбец на месте. Если вы используете этот пример, закрепите две верхние строки (строки 1 и 2).
3. Попробуйте открыть новое окно для своей книги.
4. Используйте команду Split , чтобы разделить рабочий лист на несколько панелей.

/ ru / excel2013 / sorting-data / content /

.