Ограничение ввода данных в ячейку Excel

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

Как часть ограничения ввода данных на число (которое может быть целым числом, десятичной, датой или временем), вы также указываете допустимые значения для этого типа номера (целое число от 10 до 100 или дата между 1 января 2012 года и 31 декабря 2012 года, например).

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

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

Чтобы использовать функцию проверки данных, поместите курсор ячейки в ячейку, где вы хотите ограничить тип ввода данных, который вы можете там сделать, а затем нажмите кнопку «Проверка данных» на вкладке «Данные» ленты (или нажмите Alt + AVV). Откроется диалоговое окно «Проверка данных» с выбранной вкладкой «Настройки».

Затем щелкните раскрывающуюся кнопку, прикрепленную к раскрывающемуся списку Разрешить, и выберите один из следующих элементов:

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

Целый номер , чтобы ограничить запись целое число, которое попадает в определенный диапазон или соответствует определенным параметрам, которые вы указываете

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

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

Дата , чтобы ограничить запись до даты, которая находится в определенном диапазоне, или до или до определенной даты

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

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

Пользовательский , чтобы ограничить запись параметрами, указанными конкретной формулой, введенной в другую ячейку рабочего листа > Чтобы указать входное сообщение после выбора всех элементов на вкладке «Настройки», откройте вкладку «Вводные сообщения» диалогового окна «Проверка данных», где вы вводите короткий заголовок для входного сообщения (например,

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

I ). После выбора типа предупреждения вы затем вводите заголовок для своего диалогового окна в текстовое поле «Заголовок» и вводите текст предупреждающего сообщения в поле «Сообщение об ошибке».

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

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

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

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

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

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

Чтобы найти ячейки, к которым была применена проверка данных, откройте диалоговое окно «Перейти» (Ctrl + G или F5), а затем нажмите кнопку «Специальная» и нажмите кнопку «Проверка данных» в диалоговом окне «Перейти к специальному».

Нажмите кнопку «То же» в разделе «Проверка данных», чтобы Excel перешел к следующей ячейке, которая использует те же параметры проверки данных, что и активная ячейка. Оставьте кнопку «Все» в разделе «Проверка данных» выбрана для перехода к следующей ячейке, использующей любые настройки проверки данных.

Чтобы избавиться от настроек проверки данных, назначенных для определенного диапазона ячеек или ячеек, выберите ячейку или диапазон, откройте диалоговое окно «Проверка данных» (Alt + AVV), а затем нажмите кнопку «Очистить все», прежде чем нажимать «ОК».

rf-webmaestro · Опубликовано 11.05.2018 · Обновлено 08.02.2019

Иногда при работе с таблицами в редакторе Excel появляется уведомление, что «набор значений, которые могут быть введены в ячейку, ограничен». Самая главная проблема в том, что из-за этого вы не можете ввести нужную вам информацию. На самом деле ничего страшного тут нет. Это ситуацию можно исправить в несколько шагов. В этой статье мы подробно рассмотрим, из-за чего именно появляется это окно и что можно сделать в этой ситуации.

Условие проверки

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

1. Нажмите на любую клетку.
2. Перейдите на вкладку «Данные».
3. Кликните на выделенный инструмент.
4. Выберите пункт «Проверка данных».

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

Рассмотрим эти форматы более внимательно.

Любое значение

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

Целое число

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

Например, если выбрать вариант «между», то вы увидите, что в этом окне появятся два дополнительных поля: «Минимум» и «Максимум».

Если выбрать «больше» какой-то величины, то вам нужно будет ввести минимальный допустимый порог. Именно поэтому вы увидите только одно поле – «Минимум», поскольку «Максимум» неограничен.

Действительное

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

Список

Этот формат наиболее интересный.

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

1. Заполните чем-нибудь несколько клеток. Неважно чем.

1. Нажмите на какую-нибудь клетку. Перейдите на знакомую вам вкладку. Кликните на иконку «Работа с данными». Выберите выделенный инструмент.

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

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

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

Время

Аналогично и тут. Только здесь указывается одно время (без даты).

Длина текста

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

Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.

Другой

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

Сообщение об ошибке

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

1. Повторите описанные выше шаги по вызову окна «Проверка вводимых значений».
2. Выберите какой-нибудь тип данных. В качестве примера мы укажем, что необходимо вводить «Действительное» число, которое больше 10.

1. Теперь переходим на вкладку «Сообщение об ошибке».
2. Выбираем вид отображения:
3. «Останов»;
4. «Предупреждение»;
5. «Сообщение».
6. Указываем заголовок и текст уведомления.
7. Для сохранения проверки ввода значений нажмите на «OK»

В качестве примера мы укажем следующие настройки.

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

В данном случае в поле «Вид» был указан формат «Сообщение». В этом случае вы можете вводить любые числа. Но каждый раз будете видеть подобное уведомление.

Если сделать тип «Останов», то ввести неверное значение не удастся.

А теперь попробуйте убрать введенные настройки и оставить пустые поля.

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

Но обратите внимание на то, что текст сообщения отличается!

Отличие версий Microsoft Excel

Описанная выше инструкция подходит для современных редакторов 2010, 2013 и 2016 годов. По сравнению со старыми программами существуют некоторые отличия.

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

В старом Excel 2003 ошибка точно такая же.

Сообщение для ввода данных

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

1. Кликните на любую клетку, для которой вы хотите задать какие-нибудь правила.
2. Откройте в меню раздел «Данные».
3. Выберите инструмент «Работа с данными».
4. Нажмите на иконку «Проверка данных».

1. В появившемся окне перейдите на вкладку «Сообщение для ввода».
2. Укажите любой текст в указанных полях.
3. Для продолжения нажмите на «OK».

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

Как отключить эту ошибку

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

1. Выберите ячейку, в которой вы не можете указать нужное вам значение.
2. Перейдите на панели инструментов на вкладку «Данные».
3. Нажмите на инструмент «Работа с данными».
4. Кликните на иконку «Проверка данных».

1. Для того чтобы убрать все настройки, достаточно нажать на кнопку «Очистить всё».
2. Сохраняем изменения кликом на «OK».

1. Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.

Примеры от компании Microsoft

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

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

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

Видеоинструкция

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

В этом курсе:

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

Скачивание примеров

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

Выберите данные >проверка данных.

На вкладке Параметры в списке Тип данных выберите подходящий вариант:

Целое число , чтобы ограничить ячейку для приема только целых чисел.

» Дес .», чтобы ограничить ячейки как допустимые только десятичными числами.

Список, чтобы данные выбирались из раскрывающегося списка.

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

Time (время ): Ограничьте ячейки, чтобы она принимала только время.

Длина текста, чтобы ограничить длину текста.

Другой, чтобы задать настраиваемую формулу.

В списке Значение выберите условие:

больше или равно

меньше или равно

На вкладке Параметры в списке Тип данных выберите подходящий вариант:

Задайте остальные обязательные значения с учетом параметров Тип данных и Значение. Например, если выбрать значение между, выберите минимум: и максимум: значения в ячейках (ах).

Установите флажок Игнорировать пустые ячейки, если нужно пропускать пустые ячейки.

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

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

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

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

• Какую версию вы используете?
• Более новые версии
• Office 2011

Ограничение ввода данных

Выделите ячейки, для которых нужно ограничить ввод данных.

На вкладке данные нажмите кнопку Проверка данных > Проверка данных.

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

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

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

Запрос на ввод допустимых записей

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

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

На вкладке данные нажмите кнопку Проверка данных > Проверка данных.

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

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

В поле название введите название сообщения.

В поле введите сообщение, которое вы хотите отобразить.

Вывод сообщения об ошибке при вводе недопустимых данных

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

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

На вкладке данные нажмите кнопку Проверка данных > Проверка данных.

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

На вкладке сообщение об ошибке в поле название введите название сообщения.

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

Выполните одно из следующих действий:

На вкладке Style (стиль ) всплывающее меню выберите

Требовать от пользователей исправить ошибку перед продолжением

Предупреждайте пользователей о том, что данные недействительны, и попросите их выбрать «Да» или » нет «, чтобы указать, следует ли продолжать работу

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

Выделите ячейки, для которых нужно ограничить ввод данных.

На вкладке данные в разделе Сервиснажмите кнопку проверить.

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

Во всплывающем меню Разрешить выберите тип данных, которые вы хотите разрешить.

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

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

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

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

На вкладке данные в разделе Сервиснажмите кнопку проверить.

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

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

В поле название введите название сообщения.

В поле введите сообщение, которое вы хотите отобразить.

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

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

На вкладке данные в разделе Сервиснажмите кнопку проверить.

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

На вкладке сообщение об ошибке в поле название введите название сообщения.

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

Выполните одно из следующих действий:

На вкладке Style (стиль ) всплывающее меню выберите

Требовать от пользователей исправить ошибку перед продолжением

Предупреждайте пользователей о том, что данные недействительны, и попросите их выбрать «Да» или » нет «, чтобы указать, следует ли продолжать работу

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

Добавление проверки данных в ячейку или диапазон ячеек

Примечание: Первые два шага, описанные в этом разделе, предназначены для добавления проверки данных любого типа. Шаги 3-7 предназначены специально для создания раскрывающегося списка.

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

На вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных.

На вкладке Параметры в разделе Разрешить нажмите кнопку Список.

В поле Источник введите значения, разделенные точкой с запятой. Например, введите » низкий», «средний», «высокий».

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

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

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

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

Удаление проверки данных — выделите ячейки, содержащие условия, которые вы хотите удалить, а затем перейдите к разделу данные > проверка данных и в диалоговом окне Проверка данных нажмите кнопку Очистить все , а затем нажмите кнопку ОК.

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

Разрешить вводить только целые числа из определенного диапазона

Выполните действия, описанные в 1-2 выше.

В списке Разрешить выберите значение Целое число.

В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.

Введите минимальное, максимальное или определенное разрешенное значение.

Можно также ввести формулу, которая возвращает числовое значение.

Например, допустим, что вы проверяете значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу =2*F1 в поле Минимальное значение.

Разрешить вводить только десятичные числа из определенного диапазона

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Десятичный.

В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.

Введите минимальное, максимальное или определенное разрешенное значение.

Можно также ввести формулу, которая возвращает числовое значение. Например, для задания максимального значения комиссионных и премиальных в размере 6% от заработной платы продавца в ячейке E1 выберите пункт Меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимальное значение.

Примечание: Чтобы предоставить пользователю возможность вводить процентные значения, например 20%, в поле Разрешить выберите пункт десятичные , выберите необходимый тип ограничения в поле данные , введите минимальное, максимальное или конкретное значение в десятичном формате, например . 2, а затем Выбери ячейку для проверки данных в процентах, выделяя ячейку и выбирая процентный формат в группе число на вкладке Главная .

Разрешить вводить только даты в заданном интервале времени

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Дата.

В поле Данные выберите необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше.

Введите начальную, конечную или определенную разрешенную дату.

Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные, потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения.

Разрешить вводить только время в заданном интервале

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Время.

В поле Данные выберите необходимый тип ограничения. Например, для разрешения времени до определенного времени дня выберите ограничение меньше.

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

Например, если в ячейке E2 задано время начала (8:00), а в ячейке F2 — время окончания (17:00) и вы хотите ограничить собрания этим промежутком, выберите между в поле Данные, а затем введите =E2 в поле Время начала и =F2 в поле Время окончания.

Разрешить вводить только текст определенной длины

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Длина текста.

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

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

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

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите необходимый тип данных.

В поле Данные выберите необходимый тип ограничения.

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

Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить, ограничение «Меньше или равно» в списке «Данные», а в поле Максимальное значение введите >= =E1.

В примерах ниже при создании формул с условиями используется настраиваемый вариант. В этом случае содержимое поля «Данные» не играет роли.

Снимки экрана в этой статье взяты Excel 2016; но функциональность одинакова в Excel Online.

Значение в ячейке, содержащей код продукта (C2), всегда начинается со стандартного префикса «ID-» и имеет длину не менее 10 (более 9) знаков.

= И (LEFT (C2; 3) = «ID-«; ДЛСТР (C2) >9)

Ячейки, содержащие название товара (D2), содержат только текст.

Значение в ячейке, содержащей чью-то дату рождения (B6), было больше числа лет, указанного в ячейке B4.

Примечание: Необходимо сначала ввести формулу проверки данных в ячейку A2, а затем скопировать эту ячейку в ячейки A3:A10 так, чтобы второй аргумент СЧЁТЕСЛИ соответствовал текущей ячейке. Часть A2)=1 изменится на A3)=1, A4)=1 и т. д.

Адрес электронной почты в ячейке B4 содержал символ @.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

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

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

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

Установка и снятие защиты ячеек стандартными средствами Excel

Защита ячеек производится в два этапа, для того чтобы защитить ячейки от изменений, необходимо:

Этап первый — выделить ячейки и сделать их защищаемыми;

Для этого достаточно после выделения ячеек нажать сочетание клавиш Ctrl+1 либо кликнуть правой кнопкой мыши в области выделенных ячеек, выбрать из контекстного меню пункт «Формат ячеек…», зайти на вкладку «Защита» и установить флажки в полях «Защищаемая ячейка» и/или «Скрыть формулы».

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

Этап второй — установить защиту листа.

Защите листа в различных версиях Excel посвящена отдельная публикация, здесь же следует напомнить, что установить защиту листа в Microsoft Excel 2003 можно в меню Сервис/Защита/Защитить лист, а в Excel 2007/2010/2013 лист защищается на вкладке «Рецензирование», в группе кнопок «Изменения» нажатием кнопки «Защитить лист». При необходимости можно отметить галочками разрешения для пользователей и ввести пароль.

Существует возможность быстрой защиты всех листов сразу. Защита ячейки/ячеек вступает в силу сразу после выполнения второго этапа.

Как защитить все ячейки листа? Как снять защиту со всех ячеек листа?

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

После того как все ячейки выделены, необходимо вызвать диалоговое окно «Формат ячеек» любым удобным способом (например горячими клавишами Ctrl+1), перейти на вкладку «Защита» и установить (либо снять) флажки в полях «Защищаемая ячейка» и «Скрыть формулы». Опция «Скрыть формулы» позволяет скрывать не только формулы, скрывается любая информация, отображаемая в строке формул.

Выборочная защита ячеек в Excel

С помощью защиты ячеек, табличное пространство можно четко разграничить на области, которые изменять можно и которые изменять нельзя, сделать это достаточно просто, как мы уже убедились. Неудобство возникает если требуется выборочная защита ячеек, когда установить защиту необходимо например, только на ячейки с примечаниями или на ячейки с формулами. В Excel 2007 и выше это неудобство решается при помощи инструмента под названием «Выделение группы ячеек». Чтобы им воспользоваться, достаточно на вкладке «Главная», в группе кнопок «Редактирование» в меню кнопки «Найти и выделить» выбрать пункт «Выделение группы ячеек…» после чего выбрать необходимые опции и нажать кнопку «ОК» (). После того, как все нужные ячейки выделены, остается лишь сделать ячейки защищаемыми, после чего установить защиту на лист.

Установка и снятие защиты ячеек при помощи надстройки

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

Использование надстройки позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо с панели инструментов Excel;

2. выбирать по своему усмотрению один из четырех режимов обработки данных;

3. на выбор пользователя устанавливать либо снимать свойство «Защищаемая ячейка» и «Скрыть формулы» по семи условиям (на пустые ячейки, на ячейки с числовыми и текстовыми значениями, с формулами, с примечаниями, с заливками и рамками).

видео по установке/снятию защиты ячеек

макрос (надстройка) для выборочной установки и снятия свойства «защищаемая ячейка»

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

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

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

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

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

Любая формула начинается со знака (=). Если в ячейку введена формула, то по умолчанию ячейка будет показывать результат расчета.

Ввод данных в ячейку

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

Ввод данных

1. Щелкните по ячейке.
2. Введите данные, например Отчет за 2001 год.
3. Нажмите клавишу Enter.
4. Для отмены ввода нажмите Esc.

При вводе в строке формул щелкните кнопку Enter- √ для подтверждения, а для отмены ввода нажмите кнопку Отмена — ×.

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

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

Числа, которые слишком велики, чтобы быть показанными внутри текущей ячейки, будут отображаться как последовательность символов # # # #. Чтобы показать числовое значение в ячейке, надо увеличить ширину столбца (см. раздел «Форматирование ячеек»).

Удалить содержимое ячейки можно с помощью клавиши Delete.

Выделение ячеек

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

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

• одна или несколько ячеек;
• один или несколько столбцов;
• одна или несколько строк;
• один или несколько рабочих листов.

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

Табл. 17 иллюстрирует некоторые из возможных комбинаций.

Элементы	Обозначения	Область определения
Ячейка Группа ячеек	В2 или В2:В2 В2:В23 В2:05	1 ячейка 22 ячейки 12 ячеек
Строка Строки	1 1:2	1 строка 2 строки
Столбец Столбцы	А:А А:С	1 столбец 3 столбца
Рабочий лист	Лист1 Лист1:ЛистЗ	1 рабочий лист 3 рабочих листа
Множество	А2:С2,С4:D6,В8	10 ячеек

Таблица 17. Обозначения областей таблицы

Ячейки могут быть выделены с помощью мыши или клавиатуры или их комбинации (табл. 18). Выделенные ячейки будут отличаться цветом.

Область выделения	Способ выделения
Отдельная ячейка	Щелкните мышью в ячейке
Группа ячеек	Щелкните, мышью в ячейке; не отпуская мышь, перетащите от первой ячейки до последней или щелкните первую ячейку и при нажатой клавише Shift щелкните по последней ячейке
Столбец	Щелкните по заголовку столбца
Соседние столбцы	Щелкните по первому заголовку столбца; не отпуская мышь, перетащите от первого заголовка столбца к последнему или щелкните по первому заголовку столбца и при нажатой клавише Shift щелкните по последнему заголовку столбца
Строка	Щелкните по заголовку строки
Соседние строки	Щелкните по заголовку строки; не отпуская мышь, перетащите от первого заголовка строки к последнему или щелкните по первому заголовку строки и при нажатой клавише Shift щелкните по последнему заголовку строки
Все ячейки в текущем рабочем листе	Щелкните по кнопке на пересечении заголовков строк и столбцов
Несмежные столбцы	Выберите первый столбец, нажмите Ctrl и выберите следующие столбцы
Несмежные строки .	Выберите первую строку, нажмите Ctrl и выберите следующие строки
Несмежные ячейки	Выберите первую группу ячеек, нажмите Ctrl и выберите следующую группу ячеек

Таблица 18. Выделение областей таблицы

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

Редактирование содержимого ячейки

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

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

Указатель мыши может быть использован для перемещения в место редактирования. Кроме того, в режиме редактирования могут использоваться следующие клавиши:

Клавиша	Действие
F2	Активизация режима редактирования
→	Перемещение вправо на один символ
←	Перемещение влево на один символ
End	Перемещение в конец символьной строки
Home	Перемещение в начало символьной строки
Ctrl + →	Перемещение на одно слово вправо
Ctrl + ←	Перемещение на одно слово влево
Delete	Удаление символа справа от места вставки
Backspace	Удаление символа слева от места вставки
Enter	Ввод отредактированной информации в ячейку
Esc	Отмена любых изменений содержимого ячейки

Так же как и в Word, нажатие на клавишу Insert осуществляет переключение между режимами вставки и замещения.

Отмена и повторение действий

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

Команда Отменить (Undo) в меню Правка (Edit) контекстно зависима. Когда пользователь набирает или редактирует данные в строке формул, в меню Правка будет предложена команда, соответствующая последней выполняемой операции.

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

После выбора команды Отменить в меню Правка команда изменится на команду Вернуть (Redo).

Вставка строк и столбцов

Дополнительные строки или столбцы могут быть вставлены по мере необходимости в любом месте таблицы. Команда Вставить (Insert) в меню Правка (Edit) может использоваться для вставки нового столбца слева от текущего столбца или новой строки над текущей строкой.

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

1. Выделите столько столбцов или строк, сколько необходимо вставить.
2. Выберите Вставка, Строки (Insert, Rows) или Вставка, Столбцы (Insert, Columns) или нажмите комбинацию клавиш Ctrl и + на цифровой клавиатуре.

Для удаления строк или столбцов:

1. Выберите строки или столбцы для удаления.
2. Выберите Правка, Удалить (Edit, Delete) или нажмите комбинацию клавиш Ctrl и — на цифровой клавиатуре.

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

Перемещение и копирование данных

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

Перемещение и копирование содержимого ячеек можно осуществить двумя способами:

• командами меню Правка (Edit);
• перетаскиванием с помощью мыши.

Как только пользователь выделит ячейку и выберет команду Вырезать или Копировать (Cut или Сору) в меню Правка (Edit), Excel произведет копирование содержимого ячейки в буфер обмена.

При перемещении данные исходных ячеек будут вставлены на новое место.

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

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

Использование команды Вставить (Paste) в меню Правка (Edit) после выбора команды Вырезать (Cut) будет отключать границу.

Использование команды Вставить (Paste) после команды Копировать (Сору) не будет отключать границу, т.е. пользователь может продолжать указывать другие места назначения для вставки данных и применять команду Вставить снова.

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

Нажатие клавиши Esc будет отменять операцию копирования в буфер и отключать границу.

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

Перемещение и копирование с помощью меню

1. Выделите область ячеек для копирования.
2. Для перемещения щелкните кнопку Вырезать — или выберите команду Вырезать из меню Правка (Edit, Cut). Excel показывает границу, окружающую область вырезания.
3. Для копирования щелкните по кнопке Копировать — или выберите Правка, Копировать (Edit, Copy). Excel показывает границу, окружающую область копирования.
4. Выберите место (верхнюю ячейку области) вставки.
5. Щелкните по кнопке Вставить — нажмите Enter или выберите Правка, Вставить (Edit, Paste).

Возможность Перетаскивания (Drag and Drop) позволяет перемещать или копировать содержимое выделенных ячеек с помощью мыши. Эта возможность особенно полезна при .перемещении и копировании на небольшие расстояния (в пределах видимой области рабочего листа).

Перемещение и копирование перетаскиванием

1. Выберите область ячеек для перемещения.
2. Переместите указатель мыши на обрамление выделения.
3. Перетащите выделение с помощью указателя на новое место. Область ячеек будет перемещена на новое место.
4. Если при перетаскивании удерживать нажатой клавишу Ctrl, область ячеек будет скопирована на новое место.

Специальное копирование данных

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

Команда Специальная вставка (Piste Special) часто используется и для копирования атрибутов форматирования ячейки.

1. Выделите ячейку или ячейки для копирования.
2. Выберите Правка, Копировать (Edit, Copy).
3. Выделите ячейку или ячейки, в которые будут помещены исходные данные.
4. Выберите Правка, Специальная вставка (Edit, Paste Special). Диалоговое окно Специальная вставка содержит несколько параметров для вставки данных (рис. 83).

Рис. 83. Специальная вставка

1. Установите необходимые параметры, например форматы (при колировании форматов изменяется только форматирование, а не значение ячеек).
2. Выберите ОК.

Первая группа параметров диалогового окна Специальная вставка (Paste Special) позволяет выбрать содержимое или атрибуты форматирования, которые необходимо вставлять. При выборе параметра Все (АИ) вставляются содержимое и атрибуты каждой копируемой ячейки на новое место. Другие варианты позволяют вставлять разные комбинации содержимого и/или атрибутов.

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

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

Таблица 19. Параметры команды Специальная вставка

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

Упражнение

Выполнение вычислений с помощью команды «Специальная вставка»

Введите данные, как показано в табл. 20.

	А	В	С	D	Е	F	G	Н
1
2	5	2					1	2
3	12	3					10	3
4	8	2					15	4

Таблица 20. Исходные данные

1. Выделите область для копирования А2:А4.
2. Выберите Правка, Копировать (Edit, Copy).
3. Щелкните ячейку В2 (верхний левый угол области, в которую будут помещены данные).
4. Выберите Правка, Специальная вставка (Edit, Paste Special).
5. Установите параметр Умножить.
6. Нажмите ОК. Обратите внимание, что на экране осталась граница области выделения.
7. Щелкните ячейку С2, которая будет началом области вставки.
8. Выберите Правка, Специальная вставка (Edit, Paste Special) и установите параметр Транспонировать.
9. Скопируйте самостоятельно форматы столбца G в столбец Н и получите табл. 21.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Спарклайны

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

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

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

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

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

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

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

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

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

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

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

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