Excel как закрепить значение в формуле excel: Как закрепить в формуле ячейку в Excel — MS Office Excel — Работа на компьютере: инструкции и советы — Образование, воспитание и обучение

8 приёмов для ускоренной работы в Excel

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

Вместе с аналитиком Laba Group Валерией Петренко рассмотрим 8 простых функций, которые повысят вашу продуктивность в работе с программой.  

#1. Специальная вставка 

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

Специальная вставка позволяет выбрать, какие элементы ячейки необходимо перенести. После того как вы скопировали ячейку, нажмите CTRL + ALT + V. Далее появится окно: 

Выбирая Специальная вставка — Значения, мы переносим только сами значения, но не формулы либо формат скопированной ячейки.

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

#2. Добавить несколько строк 

Если нужно добавить новую строку между уже существующими, поможет комбинация СTRL, Shift, +. Нажимая на эти клавиши несколько раз, вы добавляете соответствующее число строк. 

Кроме того, можно выделить нужное количество строк и на вкладке Главная кликнуть на Вставить:

В этом случае вы добавите столько же строк, сколько изначально выделили.

#3. Отменить либо повторить действие

Одна из самых популярных комбинаций клавиш — CTRL+Z, с ее помощью можно отменить последнее действие. Но существует еще CTRL+Y, которая, наоборот, повторяет действие. Можно использовать их совместно — и переключаться вперед-назад между своими правками. Или применять CTRL+Y отдельно, чтобы повторить свое последнее действие. 

Так, если вы только что вставили дополнительную строку в таблицу, нажмите CTRL+Y — и добавите еще одну. Аналогичный результат дает клавиша F4 (либо Fn+F4). Например, применив заливку цветом к одной ячейке, нажмите F4, чтобы сделать то же самое с другой.

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

#4. Прокрутить до конца списка

Чтобы не тратить время и не скролить бесконечно — просто зафиксируйте курсор на любой ячейке вашего списка и используйте комбинацию CTRL + стрелки вверх/вниз/вправо/влево соответственно.

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

Предупреждение: если у вас есть пробелы в данных, это действие просто приведет вас к первому пробелу:

#5. Закрепить заглавную строку/столбец

Проскролили список вниз — и забыли, какой из столбцов за что отвечает? В Excel можно закрепить, например, верхнюю строку или первый столбец, а также несколько строк и столбцов. Для этого выделяем нужную строку/столбец и переходим на вкладку Вид — Закрепить области: 

Даже когда вы докрутите до конца списка, шапка таблицы останется видимой:

#6. Удалить дубликаты

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

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

Но можно не прибегать к таким радикальным методам, как удаление дубликатов, а просто пометить повторяющиеся ячейки форматированием. Для этого нужно выделить необходимый диапазон (конкретный столбец или всю таблицу) и на вкладке Главная перейти к Условному форматированию — Правила выделения ячеек — Повторяющиеся значения:

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

#7. Сортировка

Переупорядочить таблицу поможет сортировка. Можно применять ее только по одному столбцу или настроить многоступенчатую сортировку, например, сначала по номеру магазина, а потом — по номеру чека. Для этого на вкладке Главная переходим в Сортировка и фильтр — Настраиваемая сортировка:

Далее в новом окне выбираем столбцы, по которым нужно провести сортировку:

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

#8. Найти и заменить

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

Чтобы заменить значения, можно использовать эту же комбинацию и перейти на вкладку Заменить в открывшимся диалоговом окне, либо же сразу использовать комбинацию CTRL + H:

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

#MS Excel

#Программы

#Советы эксперта

#Гайд

Последние материалы

Статья

Делайте много мелких покупок вместо одной большой

6 способов выжать из денег максимум счастья.

Читать

Статья

Самый популярный пароль 2022 года — password, и взломать его можно за секунду

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

Читать

Статья

«Я маркетолог, даже когда над моей головой носятся Шахеды»

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

Читать

Простые приёмы для эффективной работы в Excel

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

1. Быстрое добавление новых данных в диаграмму

Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

2. Мгновенное заполнение (Flash Fill)

Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.

Так же можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.

3. Копирование без нарушения форматов

Вы, скорее всего, знаете про «волшебный» маркер автозаполнения — тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.

4. Отображение данных из таблицы Excel на карте

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps. Это можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

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

5. Быстрый переход к нужному листу

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

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

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

6. Преобразование строк в столбцы и обратно

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

1. Выделите диапазон.
2. Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
3. Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose).

В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

7. Выпадающий список в ячейке

Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и т. д.), то это можно легко организовать при помощи выпадающего списка:

1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data — Validation).
3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

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

8. «Умная» таблица

Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home — Format as Table), то наш список будет преобразован в «умную» таблицу, которая (кроме модной полосатой раскраски) умеет много полезного:

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

9. Спарклайны

Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.

10. Восстановление несохранённых файлов

Пятница. Вечер. Долгожданный конец ударной трудовой недели. Предвкушая отдых, вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет».

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

На самом деле, есть неслабый шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» — «Последние» (File — Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks). В Excel 2013 путь немного другой: «Файл» — «Сведения» — «Управление версиями» — «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks). Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

11. Сравнение двух диапазонов на отличия и совпадения

Весьма часто при работе в Excel возникает необходимость сравнить два списка и быстро найти элементы, которые в них совпадают или отличаются. Самый быстрый и наглядный способ сделать это:

1. Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
2. Выберите на вкладке «Главная» — «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения» (Home — Conditional formatting — Highlight Cell Rules — Duplicate Values).
3. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

12. Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт — перелёт», и вот оно, долгожданное «попадание»!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert — What If Analysis — Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Как заблокировать ссылки на формулы ячеек в Excel при использовании таблиц данных

Быстрая навигация

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

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

Почему мы используем таблицы данных в первую очередь?

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

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

Сводные формулы, такие как SUMIF () , COUNTIF () , MAX () , мин. () и Среднее () могут быть предназначены для справки. всю таблицу или определенные столбцы, а затем автоматически изменять их диапазоны, когда таблицы увеличиваются или уменьшаются!

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

Как работают обычные ссылки на таблицы данных

Ссылки на столбцы таблицы данных

Когда вы работаете с таблицами данных в Excel, ссылки на ячейки выглядят немного иначе, чем обычно  A1 комбинация букв и цифр для столбцов и строк. Это связано с тем, что каждая строка таблицы действует так, как будто она находится в своей собственной электронной таблице с 1 строкой. Это означает, что ссылки должны относиться только к имени таблицы и столбцу. Строка считается самой собой.

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

 =  СУММА  (  Таблица1[Продажи]  ) 

Выход для этой формулы:

  3167  

Обратите внимание, что он не запрашивает начальную или конечную строку. Он просто запрашивает имя столбца и таблицы.

Теперь добавим данные в таблицу:
Формула для суммирования столбца Продажи осталась прежней:

 =  СУММ  (  Таблица1[Продажи]  ) 

Однако теперь вывод для этой формулы обновился автоматически:

  6450  

Без каких-либо действий вычисления учитывают дополнительные данные! Нет необходимости изменять ссылки в формуле SUM() !

Пример таблицы данных с несколькими ссылками

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

 =  СУММЕСЛИМН  (  [Продажи]  ,  [Регион]  ,  [@Регион]  ) 

Должно получиться так:

3

3

Обратите внимание, что окончательный термин – критерий СУММЕСЛИМН () – использует @ (символ «at»). Это обозначение означает, что вы имеете в виду значение столбца Region в текущей строке . Просто нажмите ВВЕДИТЕ , чтобы заполнить этой формулой весь столбец, и вы увидите результат этой ссылки в заполненной таблице.

Вычисление СУММЕСЛИ () суммирует все Продажи для каждого Региона , давая соответствующую сумму для каждой строки из региона Север (а также для каждого из остальных).

Теперь, когда вы видите, какими мощными могут быть таблицы данных Excel, вы, вероятно, захотите их использовать… Чтобы быстро и надежно создавать сложные формулы с использованием таблиц данных, вам необходимо иметь возможность блокировать ссылки, поэтому мы узнать дальше… Нажмите вперед!

Как построить блокирующие ссылки

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

Пример обычных относительных и абсолютных (заблокированных) ссылок на ячейки

Возьмем простой пример:

Здесь мы создаем формулу для расчета квартального дохода на основе объема и цены за единицу. Объем будет меняться каждый квартал в новой колонке, но цена за единицу останется прежней, поэтому мы блокируем ссылку, нажимая F4 или введите знаки доллара для $F$2 вручную.

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

Когда мы отпускаем кнопку, вычисляются формулы для каждого квартала. Ссылка на ячейку объема (выделена синим цветом) остается «относительной» ссылкой, которая перемещается вместе с ячейкой формулы. Однако ссылка на цену за единицу (выделена красным) стала «абсолютной», то есть 9 0021 заблокировал в исходном месте.

Создание абсолютных (блокирующих) ссылок в таблицах данных

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

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

Как построить ссылки блокировки в таблицах данных

Пример данных

Давайте расширим нашу таблицу региональных продаж до полного 6-месячного периода. Так могли бы выглядеть ваши данные, если бы вы импортировали их из базы данных или другого программного обеспечения для создания отчетов. Это очень удобно для машинного чтения, но не для человека. В этом примере мы собираемся назвать таблицу в Excel как DataTable .

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

Эта таблица будет называться Сводная таблица . Чтобы заполнить эту таблицу данными, мы собираемся использовать другую простую формулу СУММЕСЛИМН (), но мы будем работать внутри таблицы и искать информацию внутри другой таблицы…

Построение формулы, которая относится к заблокированным Столбцы и строки таблицы данных

Начиная со столбца North и строки Jan-16 , постройте следующую формулу:

 =  СУММЕСЛИМН  ( Таблица данных[[Продажи]:[Продажи]] ,  Таблица данных[[Регион]:[Регион]] ,  B$1 ,  Таблица данных[[Месяц]:[Месяц]] ,  PivotTable[@[Month]:[Month]]  ) 

Это будет выглядеть так:

В этой формуле мы блокируем ссылки тремя разными способами:

1. Ссылка на весь заблокированный столбец в Таблица данных.
2. Ссылка на текущую строку заблокированного столбца в таблице данных.
3. : Ссылка на заблокированную строку заголовка таблицы данных.

Каждый из них использует немного отличающийся синтаксис, поэтому я покажу каждый из них по порядку:

1. Блокировка целых столбцов таблицы данных

Обычные ссылки на столбцы таблицы данных выглядят так, когда вы находитесь внутри одной и той же таблицы:

  [Продажи]  

Когда вы находитесь вне таблицы, в которой находится столбец, они выглядят следующим образом:

  DataTable[Продажи]  

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

  DataTable[[Продажи]:[Продажи]]  

2.

Ссылка на текущую строку заблокированного столбца таблицы данных

Нормальные текущие ссылки на строки для таблиц данных выглядят так, когда вы находитесь внутри той же таблицы:

  [@Month]  

Когда вы находитесь вне таблицы, в которой находится столбец, они выглядят следующим образом:

  PivotTable[@Month]  

Подобно блокировке всего столбца, как показано выше, хитрость блокировки текущая ссылка на строку должна рассматриваться как диапазон, но символ @ используется только в начале ссылки на диапазон:

  Сводная таблица[@[Месяц]:[Месяц]]  

3. Блокировка ссылки на заголовок таблицы данных

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

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

  B$1  

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

Завершенная формула таблицы данных

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

Новая формула в ячейке E7 читает:

 =  СУММЕСЛИМН  (  Таблица данных[[Продажи]:[Продажи]]  ,  Таблица данных[[Регион]:[Регион]]  ,  E$1  ,  Таблица данных[[Месяц] ]]  ,  Сводная таблица[@[Месяц]:[Месяц]]  ) 

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

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

Получите надстройку Excel для отображения «Блокировка ссылок на таблицы данных» на F4

Этот процесс добавления диапазонов ко всем вашим ссылкам на таблицы данных, чтобы сделать их абсолютными, может быть хлопотным, если вам нужно делать это часто… К счастью , Джон Акампора из Excel Campus создал надстройку, которая сопоставляет эти диапазоны блокировки с клавиатурой как F4 , поэтому они работают так же, как обычные абсолютные ссылки в Excel.

Вы можете проверить его надстройку здесь: Абсолютные структурированные ссылки в формулах таблиц Excel

Эндрю Робертс уже более десяти лет решает бизнес-задачи с помощью Microsoft Excel. Excel Tactics призван помочь вам освоить его.

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

Как заблокировать ячейки для редактирования и защитить формулы

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

Уровень навыков: Новичок

Видеоурок

Смотрите на YouTube и подписывайтесь на наш канал

Загрузите файл Excel

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

Lock-Cells-and-Sheet-Protection-Quote-Example. zipDownload

Защита вашей работы от нежелательных изменений

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

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

1. Блокировка/разблокировка ячеек.
2. Защита рабочего листа.

Вот как запретить пользователям изменять некоторые ячейки.

Шаг 1. Заблокируйте и разблокируйте определенные ячейки или диапазоны

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

Откроется окно Format Cells (сочетание клавиш для этого окна: Ctrl + 1 . ). Выберите вкладку с надписью Защита .

Затем убедитесь, что установлен флажок Locked .

Заблокировано — это значение по умолчанию для всех ячеек в новом листе/книге.

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

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

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

Шаг 2. Защита рабочего листа

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

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

Откроется окно Protect Sheet . Если вы хотите, чтобы ваш лист был защищен паролем, у вас есть возможность ввести пароль здесь. Добавление пароля не является обязательным. Нажмите «ОК».

Если вы решили ввести пароль, вам будет предложено подтвердить ввод после того, как вы нажмете OK.

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

Вы можете снять защиту с листа так же, как вы его защитили, щелкнув правой кнопкой мыши вкладку листа. Альтернативный способ защиты и снятия защиты с листов — использование кнопки «Защитить лист» на вкладке «Обзор» ленты.

Текст кнопки отображает напротив текущего состояния. Он говорит «Защитить лист», когда лист не защищен, и «Снять защиту листа», когда он защищен.

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

3 дополнительных совета по блокировке ячеек и защите листов

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

1. Предотвращение выделения заблокированных ячеек

Этот совет поможет вашим пользователям быстрее и проще вводить данные в таблицу.

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

Чтобы внести это изменение, просто снимите флажок «Выбрать заблокированные ячейки» в окне «Защитить лист».

После нажатия OK вы сможете выбрать только разблокированные ячейки.

2. Добавьте кнопку для блокировки ячеек на панель быстрого доступа

Это позволит вам быстро просмотреть заблокированные настройки для ячейки или диапазона.

На вкладке Home на ленте вы можете открыть раскрывающееся меню под кнопкой Format и увидеть параметр Lock Cell .

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

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

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

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

Смешанное состояние блокировки

Если вы выберете диапазон, содержащий как заблокированные, так и разблокированные ячейки, вы увидите сплошной флажок для флажка «Заблокировано» в окне «Формат ячеек». Это означает смешанное состояние.

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

3. Используйте другое форматирование для заблокированных ячеек

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

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

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

Заключение

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

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