Как снять ограничения с ячейки в excel: Как ограничить строки и столбцы на листе Excel

Как ограничить строки и столбцы на листе Excel

Автор Глеб Захаров На чтение 4 мин. Просмотров 4.6k. Опубликовано 02.08.2019

Отображать только то, что вы хотите увидеть

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

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

Эти инструкции относятся к Excel 2019, 2016, 2013, 2010 и Excel для Office 365.

Ограничить количество строк в Excel с помощью VBA

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

Изменение области прокрутки является временной мерой; он сбрасывается при каждом закрытии и повторном открытии книги.

В этом примере вы измените свойства листа, чтобы ограничить количество строк до 30 и количество столбцов до 26

.

1. Открыть пустой файл Excel.

2. Щелкните правой кнопкой мыши вкладку листа в правом нижнем углу экрана для Лист 1 .

3. Нажмите Показать код в меню, чтобы открыть окно редактора Visual Basic для приложений (VBA) .

4. Найдите окно Свойства листа в левом нижнем углу окна редактора VBA.

5. Найдите свойство Область прокрутки в списке свойств листа.

6. Нажмите на пустое поле справа от области прокрутки .

7. Введите диапазон a1: z30 в поле.

8. Сохранить лист.

9. Нажмите «Файл»> «Закрыть» и вернитесь в Microsoft Excel.

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

Снятие ограничений прокрутки

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

шаги со 2 по 4 выше, чтобы открыть Свойства листа в окне VBA editor и удалить диапазон, указанный для прокрутки. Область свойство.

Изображение отображает введенный диапазон как $ A $ 1: $ Z $ 30 . При сохранении книги редактор VBA добавляет знаки доллара, чтобы сделать ссылки на ячейки в диапазоне абсолютными.

Скрыть строки и столбцы в Excel

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

Вот как скрыть строки и столбцы за пределами диапазона A1: Z30 :

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

2. Нажмите и удерживайте клавиши Shift и Ctrl на клавиатуре.

3. Нажмите и отпустите клавишу Стрелка вниз на клавиатуре, чтобы выбрать все строки от 31 строки до нижней листа.

4. Нажмите правой кнопкой мыши в заголовках строк , чтобы открыть контекстное меню .

5. Выберите Скрыть в меню, чтобы скрыть выбранные столбцы.

6. Нажмите на заголовок столбца для столбца AA и повторите шаги со второго по пятый, чтобы скрыть все столбцы после столбца Z .

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

7. Сохранить книгу; столбцы и строки вне диапазона от A1

   до Z30 будут скрыты, пока вы их не отобразите.

Показать строки и столбцы в Excel

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

Чтобы отобразить строку 31 и выше и столбец Z и выше:

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

2. Затем, щелкнув правой кнопкой мыши, прокрутите вниз до скрытого раздела.

3. Нажмите Главная вкладка на ленте .

4. В разделе Ячейки нажмите Формат > Скрыть и показать > Показать строки , чтобы восстановить скрытые строки.

   Вы также можете щелкнуть правой кнопкой мыши заголовок строки и выбрать «Показать» в раскрывающемся меню.

5. Нажмите на заголовок столбца для столбца AA – или последнего видимого столбца – и повторите шаги два-четыре выше, чтобы отобразить все столбцы.

Правильное округление данных Excel для получения правильных сумм :: think-cell

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

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

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

=ROUND(x,0) примера выше. Суммы, которые отличаются от исходного значения на 1 и более, выделены полужирным шрифтом:

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

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

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

22.1

Использование округления think-cell

22.2

Ограничения округления think-cell

22.3

Устранение неполадок с формулами TCROUND

22.1 Использование округления think-cell

Округление think-cell полностью интегрируется с Microsoft Excel и предоставляет набор функций, которые похожи на стандартные функции округления Excel. Вы легко можете применять эти функции к собственным данным, используя кнопки на панели инструментов на вкладке Формулы в think-cell.

22.1.1 Параметры округления

Как и функции Excel, функции округления think-cell принимают два параметра.

x

Значение, которое необходимо округлить. Это может быть константа, формула или ссылка на другую ячейку.

n

Точность округления. Значение этого параметра зависит от используемой функции. Параметры функций think-cell эквивалентны параметрам соответствующих функций Excel. Примеры см. в таблице ниже.

Округление think-cell позволяет округлять значения не только до целых чисел, но и до любых кратных чисел. Например, если вы хотите представить данные в последовательности 5-10-15…, просто округляйте значения до чисел, кратных пяти. Используя раскрывающееся меню на панели инструментов think-cell, просто введите или выберите нужную точность округления. Затем think-cell выберет требуемые функцию и параметры. В следующей таблице представлен ряд примеров округления определенных значений x с использованием панели инструментов вместе с соответствующим параметром n.

	100	50	2	1	0,01
1,018	0	0	2	1	1,02
17	0	0	18	17	17,00
54,6	100	50	55	54	54,60
1234,1234	1200	1250	1234	1234	1234,12
8776,54321	8800	8800	8776	8777	8776,54

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

Для получения оптимальных результатов и минимального отклонения от базовых значений, следует использовать TCROUND, когда это возможно. Используйте более ограничительные функции TCROUNDDOWN, TCROUNDUP или TCROUNDNEAR, только если это необходимо.

Внимание! Не следует использовать недетерминированные функции, такие как RAND(), с какими-либо формулами TCROUND. Если функции возвращают разные значения при каждом вычислении, округление think-cell будет совершать ошибки при вычислении значений.

22.1.2 Макет вычисления

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

22.1.3 Размещение функций TCROUND

Так как функции TCROUND предназначены для управления выходными данными ячейки, они должны быть крайними функциями:

Неправильно:	=TCROUND(A1, 1)+TCROUND( SUM(B1:E1), 1 )
Правильно:	=TCROUND( A1+SUM(B1:E1), 1 )
Неправильно:	=3*TCROUNDDOWN(A1, 1)
Правильно:	=TCROUNDDOWN(3*A1, 1)

Если вы введете что-то в строках неправильных примеров, think-cell уведомит вас о значении ошибки Excel #VALUE!.

22.2 Ограничения округления think-cell

Округление think-cell всегда ищет решение для произвольных сумм с промежуточными итогами и итоговыми значениями. Округление think-cell также предоставляет подходящие решения для других вычислений, использующих умножение и числовые функции. Однако в математических целях существование согласованного округляемого решения не может гарантироваться, если используются операторы, отличные от +, — и SUM.

22.2.1 Умножение на константу

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

Точный результат вычисления для ячейки C1: 3×1,3+1,4=5,3. Чтобы получить этот результат, можно округлить значение 1,4 до 2:

Однако округление think-cell может только изменить значение, округлив его до большего или меньшего значения. Большее отклонение от исходных значений не поддерживается. Поэтому для определенных сочетаний входных значений невозможно найти согласованное решение для округления. В этом случае функция TCROUND предоставляет значение ошибки Excel #NUM!. В следующем примере показана нерешаемая задача:

Точный результат вычисления для ячейки C1: 6×1,3+1,4=9,2. Округление ячеек A1 и B1 даст следующий результат: 6×1+2=8 или 6×2+1=13. Фактический результат нельзя округлить до 8 или 13, а выходные данные округления think-cell будут выглядеть следующим образом:

Примечание. Функция AVERAGE Excel интерпретируется округлением think-cell как комбинация суммирования и умножения на константу. Кроме того, сумма, в которой одно слагаемое используется несколько раз, математически эквивалентно умножению на константу, и существование решения не гарантируется.

22.2.2 Общее умножение и другие функции

Если функции TCROUND используются для соответствующих ячеек и промежуточные результаты связаны только операторами +, -, SUM и AVERAGE, слагаемые и (промежуточные) итоговые значения объединяются в одной задаче округления. В этих случаях округление think-cell найдет решение, которое обеспечивает согласованность для всех связанных ячеек, если такое решение существует.

Так как TCROUND — обычная функция Excel, ее можно объединить с любыми функциями и операторами. Но если вы используете функции, отличные от указанных выше, для соединения результатов операторов TCROUND, округление think-cell не может объединить компоненты в одной общей задаче. Вместо этого компоненты формулы будут считаться отдельными задачами, каждая из которых будет решаться независимо друг от друга. Затем результаты будут использоваться как входные данные для других формул.

Во многих случаях результаты округления think-cell по-прежнему будут рациональными. Однако в некоторых случаях использование операторов, отличных от +, -, SUM и AVERAGE, приводит к получению округленных результатов, которые существенно отличаются от вычисления без округления. Рассмотрим следующий пример:

Точный результат вычисления для ячейки C1 в этом случае: 8,6×1,7=14,62. Так как ячейки A1 и B1 связаны умножением, округление think-cell не сможет объединить формулы из этих ячеек в общую задачу. Вместо этого после обнаружения ячейки A1 как допустимых входных данных, ячейка B1 будет вычислена независимо, а результат будет считаться константой для оставшейся задачи. Так как других ограничений нет, значение 1,7 из ячейки B1 округляется до ближайшего целого числа (2).

«Точный» результат вычисления для ячейки C1 в этом случае: 8,6×2=17,2. Теперь эту задачу попытается решить функция округления think-cell. Существует согласованное решение, для которого необходимо округлить 17,2 до 18. Результат будет выглядеть следующим образом:

Обратите внимание, что округленное значение в ячейке C1, которое равно 18, сильно отличается от исходного значения 14,62.

22.3 Устранение неполадок с формулами TCROUND

При использовании округления think-cell вы можете столкнуться с двумя ошибками: #VALUE! и #NUM!.

22.

3.1 #VALUE!

Ошибка #VALUE! указывает на синтаксические проблемы, такие как неправильно введенные формулы или недопустимые параметры. Например, второй параметр TCROUND должен быть целым числом. Кроме того, уделите внимание правильному использованию разделителей. Например, в международной версии Excel формула выглядит так: =TCROUND(1.7, 0), а в немецкой версии Excel ее следует записать следующим образом: =TCROUND(1,7; 0).

Еще одна ошибка, связанная с округлением think-cell, — это размещение вызова функции TCROUND: нельзя использовать функцию TCROUND с другой формулой. Убедитесь, что TCROUND — это крайняя функция в формуле ячейки. (См. раздел Размещение функций TCROUND.)

22.3.2 #NUM!

Ошибка #NUM! возникает из-за числовых проблем. Если результат функции TCROUND равен #NUM!, это значит, что задача, определенная данным набором формул, не имеет математического решения. (См. раздел Ограничения округления think-cell.)

Если формулы, окруженные функциями TCROUND, содержат только операторы +, — и SUM и для всех операторов TCROUND используется одинаковая точность (второй параметр), решение будет гарантированно существовать и будет найдено округлением think-cell. Однако в следующих случаях существование согласованного округленного решения не гарантируется.

• Формулы содержат другие операции, такие как умножение или числовые функции. Кроме того, суммы, в которых одно слагаемое используется несколько раз, математически эквивалентны умножению.
• Вы должны использовать разные точности во втором параметре функции TCROUND.
• Вы часто используете функции TCROUNDDOWN, TCROUNDUP и TCROUNDNEAR.

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

• Используйте более высокую точность для некоторых или всех операторов TCROUND.
• Не используйте TCROUND с умножением или числовыми функциями, отличными от +, — и SUM.
• Используйте одинаковую точность (второй параметр) для всех операторов TCROUND.
• Используйте TCROUND вместо функций TCROUNDDOWN, TCROUNDUP и TCROUNDNEAR, где это возможно.

Запаролить столбцы в Excel — Как защитить или снять защиту ячейки в Excel от редактирования

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

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

1. ограничение на ввод некорректной информации;
2. предохранение выборочного количества или определённой группы значений от редактирования;
3. установка различных прав для разных юзеров или групп;
4. ограничение прав к одному или нескольким страницами книги Excel.

Содержание

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

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

Ограничение от редактирования

• Всех элементов рабочей области — нажмите Ctrl + «A»;
• Конкретных — выберите их вручную, используя Ctrl для добавления ячейки в перечень и Shift для того, чтобы включить в него сразу несколько по горизонтали, вертикали или поперёк;
• Определённых групп элементов, например, только клетки с формулами, примечаниями или любые другие.

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

Ограничение прав к клеткам, выбранным вручную:

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

Как установить защиту листа в Excel

Далее необходимо:

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

Ограничение разных диапазонов для разных пользователей

1. Перейти во вкладку «Рецензирование», нажать «Разрешить изменение диапазонов».
2. В новом окне создать область — ввести перечень значений и пароль для доступа к ним.
3. Сделать требуемое количество произвольных областей и разрешить доступ любому количеству юзеров.
4. Установить функцию, чтобы изменения вступили в силу.

Ограничение одного или нескольких листов от изменения и форматирования

1. Перейти в «Рецензирование», указать «Защитить лист» в области «Изменения».
2. Ввести пароль, если необходимо.
3. Сохранить изменения.

Как установить пароль для защиты от редактирования

1. Укажите клетки или листы, к которым хотите ограничить доступ.
2. Перейдите в «Рецензирование» и кликните «Разрешить изменение диапазонов».
3. В новом окне создайте диапазон или укажите существующий.
4. Укажите пароль, сохраните настройки.

Как снять защиту

1. Укажите перечень клеток, к которому требуется восстановить доступ (нажмите Ctrl + «A», чтобы выбрать все).
2. Вызовите окно «Формат ячеек», кликнув правой клавишей мыши по области элементов.
3. Перейдите в «Защита» и настройте пункты «Скрыть формулы» и «Защищаемая ячейка».
4. Сохраните изменения.

Итог

Как обойти ограничение Excel и сделать выпадающий список зависимым

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

Рис. 1. Состав выпадающего списка зависит от содержания соседней ячейки

Скачать заметку в формате Word или pdf, примеры в формате Excel2007

Команда Проверка данных находится на вкладке Данные, область Работа с данными.

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

• Возможно, в настоящее время вводятся данные. Во время ввода данных в ячейку команда Проверка данных недоступна. Чтобы завершить ввод данных, нажмите клавишу ВВОД или ESC.
• Возможно, лист защищен или является общим. Если лист защищен или является общим, изменить параметры проверки данных невозможно. Снимите защиту или отмените режим «общий».
• Возможно, таблица Excel связана с узлом SharePoint. Невозможно добавить проверку данных в таблицу Excel, которая связана с узлом SharePoint. Чтобы добавить проверку данных, необходимо удалить связь таблицы Excel или преобразовать ее в диапазон.

К сожалению, Excel в своем стандарте позволяет делать списки только на основе:

• имени массива
• диапазона ячеек
• прямого перечисления элементов списка (рис. 2).

Примечание. Элементы списка вводите через стандартный разделитель элементов списка Microsoft Windows (в русском Excel по умолчанию это точка с запятой).

Рис. 2. Возможные источники списка: вверху – имя массива; посередине – диапазон ячеек; внизу – элементы списка

Попытка ввести формулу в поле Источник диалогового окна Проверка вводимых значений заканчивается неудачей (рис. 3). Видно, что Excel не воспринял значение ячейки D2 ("цвет"), как имя массива, и просто включил это значение в качестве единственного элемента списка.

Рис. 3. Недопустимый источник списка – формула

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

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

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

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

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

И всё же в Excel есть одна функция непрямого действия. На английском языке у нее говорящее название – INDIRECT. На русском – название функции ни о чем – ДВССЫЛ… В чем же заключается непрямое действие? В отличие от других функций Excel, ДВССЫЛ возвращает не значение, хранящееся в ячейке, а ссылку, хранящуюся в ячейке. Непонятно? Сам «продирался» через это с трудом 🙂 Попробую пояснить. Вот что написано в справке Excel: ДВССЫЛ – возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого (рис. 4).

Рис. 4. Как работает функция ДВССЫЛ: вверху – формулы, внизу – значения

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

Итак, еще раз, ДВССЫЛ возвращает ссылку, а не значение, хранящееся в ячейке. Ссылка немедленно вычисляется, и выводится ее значение (или содержимое). Именно это свойство позволит нам ввести непрямую ссылку на соседнюю ячейку так, что вернется не значение, хранящееся в этой соседней ячейке (как на рис. 3), а ссылка, хранящаяся в ячейке, эта ссылка тут же вычисляется, и получается имя массива (рис. 5).

Рис. 5. Формирование списка, зависящего от значения в левой ячейке

Примечание. Ссылка в формуле =ДВССЫЛ(D2) должна быть относительной (D2), а не абсолютной ($D$2). Подробнее об этом см. раздел Тип ссылок на ячейки в формулах для проверки данных заметки Excel. Проверка данных.

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

Удалить раскрывающийся список — служба поддержки Office

1. Выберите ячейку в раскрывающемся списке.

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

2. Щелкните Data > Data Validation .

3. org/ListItem»>

   На вкладке Настройки щелкните Очистить все .

4. Нажмите ОК

Если вам нужно удалить все проверки данных с рабочего листа, включая раскрывающиеся списки, но вы не знаете, где они находятся, вы можете использовать диалоговое окно Перейти к специальному . Нажмите Ctrl + G > Special , затем Data Validation > All или Same и повторите шаги, описанные выше.

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

1. org/ListItem»>

   Выберите ячейку в раскрывающемся списке.

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

2. Щелкните Data > Data Validation .

3. На вкладке Настройки щелкните Очистить все .

4. Нажмите ОК

Если вам нужно удалить все проверки данных с рабочего листа, включая раскрывающиеся списки, но вы не знаете, где они находятся, вы можете использовать диалоговое окно Перейти к специальному . Нажмите Ctrl + G > Special , затем Data Validation > All или Same и повторите шаги, описанные выше.

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

1. Выберите ячейки в раскрывающемся списке.

2. Щелкните Data > Data Validation .

3. На вкладке Настройки щелкните Очистить все .

4. Щелкните ОК .

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

Два быстрых метода удаления правил проверки данных в Excel

Сьюзан Харкинс показывает вам быстрый метод одновременного удаления правил проверки для нескольких диапазонов в Excel.

Проверка — это мощная функция, которая позволяет контролировать данные, которые пользователи могут вводить в лист Excel. Скорее всего, как только вы установите эти правила, вы не захотите их удалять, но если вы это сделаете, вы можете сделать это так же, как вы их установили:

1. Щелкните вкладку Данные.
2. В группе «Инструменты для работы с данными» щелкните раскрывающееся меню «Проверка данных» и выберите «Проверка данных».В Word 2003 выберите «Проверка» в меню «Данные».
3. На вкладке «Параметры» выберите «Любое значение» в раскрывающемся списке «Разрешить». Или просто нажмите «Очистить все».
4. Щелкните OK.

Это мышечный путь; теперь давайте рассмотрим другой метод:

1. Выберите пустую ячейку и нажмите [Ctrl] + C.
2. Выберите диапазон, из которого вы удаляете проверку.
3. Нажмите [Alt] + E + S + N, чтобы открыть диалоговое окно «Специальная вставка» с выбранным параметром «Проверка».
4. Щелкните OK.

Признаюсь, второй метод мне не легче первого. На самом деле, я считаю это немного громоздким. Однако я видел, как кто-то использовал его несколько дней назад, и она летела — так что ваш пробег может отличаться!

Если вы работаете с несколькими диапазонами, вы можете выбрать их все и сразу удалить все правила проверки. Вот быстрый пример с использованием листа, показанного ниже. Для диапазонов DateWorked (A2: A8) и Lunch (D2: D8) применяются простые, но разные правила проверки (само правило не имеет значения.) Если вы выберете оба диапазона, Excel не позволит вам работать с правилами проверки, потому что они разные, но позволит удалить правила проверки. Выберем диапазоны и посмотрим, что получится:

1. В раскрывающемся списке «Поле имени» выберите DateWorked.
2. Нажмите [Ctrl].
3. В раскрывающемся списке «Имя» выберите «Обед». На этом этапе у вас есть выбор из нескольких диапазонов.
4. Щелкните вкладку Данные.
5. В раскрывающемся списке «Проверка данных» выберите «Проверка данных».В Word 2003 выберите «Проверка» в меню «Данные».
6. Когда Excel отобразит предупреждающее сообщение, нажмите кнопку ОК, чтобы удалить параметры проверки для обоих диапазонов. Нажмите Отмена, если не хотите удалять настройки.

Использование элемента управления «Поле имени» для выбора несмежных диапазонов — полезный совет сам по себе!

Создание раскрывающегося списка в Excel для ограничения данных

Что нужно знать

• Введите данные, которые должны отображаться в раскрывающемся списке. Он может быть на том же листе или на другом.
• Выберите ячейку, в которой должен отображаться раскрывающийся список, а затем выберите Data > Data Validation > Settings > Allow > List .
• Щелкните поле Источник , выберите диапазон списка и выберите ОК . Чтобы удалить, перейдите к Data > Data Validation > Settings > Clear All .

В этой статье объясняется, как создавать раскрывающиеся списки или меню в Excel, чтобы ограничить данные, которые могут быть введены в определенную ячейку, предварительно заданным списком записей. Использование раскрывающегося списка для проверки данных упрощает ввод данных, предотвращает ошибки и ограничивает количество мест для ввода данных. Инструкции относятся к Excel 2019, 2016, 2013, 2010; и Excel для Mac.

Создать раскрывающийся список

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

Чтобы создать раскрывающийся список:

1. Выберите ячейку B3 , чтобы сделать ее активной.

2. Выберите Data .

3. Выберите Data Validation , чтобы открыть диалоговое окно Data Validation.

4. Выберите вкладку Настройки .

5. В разделе Разрешить щелкните стрелку вниз.

6. Выберите Список .

7. Поместите курсор в текстовое поле «Источник».

8. Выделите ячейки с E3 по E10 на листе, чтобы добавить данные из этого диапазона ячеек в список.

9. Выберите OK . За исключением Excel для Mac, где вы выбираете Готово .

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

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

Удалить раскрывающийся список в Excel

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

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

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

2. Выберите Data .

3. Выберите Data Validation , чтобы открыть диалоговое окно Data Validation.

4. Выберите вкладку Настройки .

5. Выберите Очистить все , чтобы удалить раскрывающийся список.

6. Выберите OK , чтобы закрыть диалоговое окно и вернуться на рабочий лист.

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

Спасибо, что сообщили нам!

Расскажите, почему!

Другой Недостаточно подробностей Сложно понять

Как разблокировать защищенные таблицы | Small Business

Вы не сможете редактировать или копировать содержимое защищенного листа Excel, пока не снимете защиту.Если для разблокировки рабочего листа пароль не требуется или вы знаете его, вы можете легко снять защиту в Excel. Если вы не знаете пароль и у вас также есть разрешение на разблокировку документа, вы можете снять защиту без пароля, используя другое приложение для работы с электронными таблицами, такое как Apache OpenOffice или Google Sheets. Однако обратите внимание, что эти методы работают только тогда, когда сам лист защищен, и не помогут, если весь файл защищен паролем.

Разблокировать рабочие листы в Excel

Запустите Excel и откройте защищенный рабочий лист.

Выберите вкладку «Обзор» и нажмите «Снять защиту с листа».

Введите пароль, если будет предложено.

Удаление защиты с помощью OpenOffice

Загрузите и установите бесплатный пакет Apache OpenOffice (ссылка в разделе «Ресурсы»).

Щелкните плитку «OpenOffice Calc» на начальном экране или дважды щелкните ярлык «OpenOffice» на рабочем столе и выберите «Электронная таблица».

Откройте файл Excel, содержащий защищенный лист.

Щелкните меню «Инструменты», выберите «Защитить документ», а затем щелкните «Лист», чтобы разблокировать электронную таблицу. OpenOffice не требует пароль.

Выберите меню «Файл», нажмите «Сохранить как», а затем измените тип файла на «Excel 97/2000 / XP (. xls)». OpenOffice не сохраняет файлы в новом формате XLSX, но в большинстве случаев у вас не возникнет проблем с использованием старого формата. В качестве меры предосторожности избегайте перезаписи исходного файла.

Введите новое имя файла и нажмите «Сохранить».В окне предупреждения нажмите «Сохранить текущий формат».

Закройте OpenOffice и откройте новый файл в Excel.

Снять защиту с помощью Google Таблиц

Перейдите на веб-сайт Google Диска и войдите в свою учетную запись Google. (ссылка в разделе «Ресурсы»).

Нажмите кнопку «Создать», а затем выберите «Таблица».

Щелкните меню «Файл», а затем выберите «Открыть».

Выберите «Загрузить», нажмите «Выбрать файл». На компьютере »выберите файл Excel и нажмите« Открыть ».»Google Таблицы откроют лист со снятой защитой.

Щелкните меню» Файл «, выберите» Загрузить как «, а затем выберите» Microsoft Excel (.xlsx) «.

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

Ссылки

Ресурсы

Предупреждения

• Информация в этой статье относится к Excel 2013 и OpenOffice 4. Она может незначительно или значительно отличаться от других версий.

Биография писателя

Алан Сембера начал писать для местных газет Техаса и Луизианы. Его профессиональная карьера включает в себя работу в качестве компьютерного техника, редактора информации и составителя налоговой декларации. Шембера теперь полный рабочий день пишет о бизнесе и технологиях. Он имеет степень бакалавра журналистики Техасского университета A&M.

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

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

Разделов:

Что такое проверка данных в Excel?

Как добавить подтверждение данных

Типы проверки данных

Ссылки на ячейки

Удалить подтверждение данных

Справочные сообщения по вводу

Пользовательские сообщения об ошибках

Банкноты

Что такое проверка данных в Excel?

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

Если вы попытаетесь ввести значение, которого нет в этом списке, вы получите такое сообщение об ошибке:

Как добавить проверку данных в ячейку

1. Выберите нужную ячейку.
2. Перейдите на вкладку «Данные» и нажмите кнопку «Проверка данных» (в Excel 2003 выберите «Данные»> «Проверка» в верхнем меню).
3. Выберите нужный тип проверки данных, в котором указано Разрешить.
4. Здесь я выбрал Список.
5. Заполните остальные отображаемые параметры, в данном случае только поле «Источник».
6. Нажмите OK, и вы добавили проверку данных в ячейку в Excel!

Типы проверки данных и способы их использования

Самая сложная часть проверки данных — это знать, что вводить в поле «Источник», особенно для пользовательского типа.

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

Any Value — Разрешить ввод любого значения в ячейку

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

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

Целое число — в ячейку можно вводить только целые числа

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

Вы можете ограничить ввод ячеек числом от 1 до 5, от 500 до 1000 или как хотите, если это целое число.

Поместите наименьшее число, которое вы хотите разрешить, в поле Минимум и наибольшее число, которое вы хотите разрешить, в поле Максимум , например:

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

Значение по умолчанию для целых чисел ограничивает ввод значением между двумя числами. Если вы хотите это изменить, просто измените параметр в раскрывающемся меню Data :

Decimal — в ячейку можно вводить только числа, включая числа с десятичными знаками

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

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

Теперь давайте введем 1,5 в поля «Минимум » и 5,5 в поля «Максимум » .

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

Это означает, что мы можем сделать что-то вроде этого:

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

Список — ограничить ввод в ячейку заранее определенным списком значений (включая список раскрывающегося меню)

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

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

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

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

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

Вы можете ввести диапазон ячеек, содержащий список значений:

Или вы можете просто ввести список значений непосредственно в поле Source :

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

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

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

Дата — ограничить ввод ячейки датой

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

Заполните поля Дата начала и Дата окончания датой в формате, понятном Excel, или ссылкой на ячейку с датой, и вот она:

Значение по умолчанию для дат — ограничить ввод значением между двумя датами.Если вы хотите это изменить, просто измените параметр в раскрывающемся меню Data :

Время — ограничить ввод ячейки по времени

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

Введите время в Время начала и Время окончания :

Обратите внимание, что мой Excel использует AM и PM, но 24-часовой формат также будет работать без проблем.

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

Длина текста — ограничение ввода текста в ячейку и управление его длиной

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

Положим 5 для Минимум и 10 для Максимум :

Это означает, что мы должны поместить в ячейку минимум 5 символов, но не можем поместить в ячейку более 10 символов.

Обратите внимание, что в подсчет символов включены пробелы.

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

Custom — ограничение ввода в ячейку всего, что вы хотите в Excel

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

Здесь у вас есть только одно поле для работы, поле Формула .

Вы должны ввести в это поле формулу или функцию, которая имеет значение ИСТИНА или ЛОЖЬ. То есть формула должна быть логической формулой, которая всегда будет возвращать только ИСТИНА или ЛОЖЬ.

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

Это говорит о том, что Excel разрешит ввод в ячейку ТОЛЬКО, когда ячейка D2 содержит число больше 1, а ячейка D3 содержит число больше 5.

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

Ссылки на ячейки при проверке данных

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

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

Удалить подтверждение данных

Чтобы удалить проверку данных, просто выберите нужную ячейку или ячейки и перейдите в окно проверки данных (вкладка «Данные»> «Проверка данных») и нажмите кнопку Очистить все в нижнем левом углу окна:

Справка по вводу данных в ячейку

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

Перейдите в окно проверки данных, а затем на вкладку Входное сообщение :

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

Вот пример для нашего примера проверки пользовательских данных выше:

Это сообщение появляется только при выборе ячейки, а не при наведении курсора мыши на ячейку:

Пользовательские сообщения об ошибках

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

Вот сообщение об ошибке по умолчанию:

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

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

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

Затем вы можете ввести заголовок и сообщение:

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

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

Параметр Информация также позволяет нам сохранить неверное значение после его появления:

Единственный, который фактически остановит ввод неверных значений, — это стиль Stop :

Банкноты

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

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

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

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

Совместите это с защитой ячеек и листов, и вы сможете создавать надежные файлы Excel.

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

запирающих ячеек и защитных листов

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

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

Обзор

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

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

Кроме того, мы обсудим различные варианты в окне «Защитить лист», которые вы можете выбрать.Наконец, мы вкратце обсудим защиту книг.

Вкладка «Защита» в окне параметров формата

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

Самый простой способ открыть окно «Параметры формата» из всех версий Excel — это щелкнуть правой кнопкой мыши меню.Щелкните внутри ячейки или выделите несколько ячеек, щелкните правой кнопкой мыши и выберите Форматировать ячейки … , как показано на изображении.

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

Отметим, что в Excel 2007 и более новых версиях некоторые параметры форматирования можно найти на ленте. Например, параметры «выравнивание» и «число» находятся на вкладке «Главная».

Учебное пособие на KeynoteSupport.com

Закрытые камеры

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

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

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

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

Чтобы разблокировать все ячейки листа за один шаг, щелкните в верхней левой ячейке листа, как показано на изображении, чтобы выбрать все ячейки листа. Затем щелкните правой кнопкой мыши, выберите «Форматировать ячейки …» и на вкладке «Защита» щелкните поле, чтобы снять флажок «Заблокировано». Затем нажмите ОК. Вкладка Защита показана ниже.

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

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

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

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

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

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

Excel 2007, 2010 и более поздние версии

Чтобы защитить рабочий лист в новых версиях Excel, начиная с Excel 2007, щелкните вкладку «Рецензирование», выберите «Защитить рабочий лист» (или «Защитить лист») и нажмите «ОК». См. Изображение справа. Вы можете ввести пароль перед тем, как нажать OK, но будьте очень осторожны. См. «О паролях» ниже.

Если рабочий лист уже защищен, на вкладке «Рецензирование» отображается значок «Снять защиту с листа» (или «Лист»).

Excel 2003, 2000 и более ранние версии

В более ранних версиях Excel нажмите «Инструменты»> «Защита», нажмите «Защитить лист» и нажмите «ОК» — при желании сначала введите пароль — см. «О паролях» ниже. Если рабочий лист уже защищен, пункт меню гласит «Снять защиту с листа».

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

A KeynoteSupport.com Учебник

Варианты защиты

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

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

Если вы единственный пользователь рабочего листа, вы можете предпочесть снять флажок «Выбрать заблокированные ячейки», СОХРАНИТЬ «Выбрать разблокированные ячейки» и проверить некоторые другие параметры, чтобы у вас было больше возможностей для внесения изменений в лист, не нарушая заблокированные ячейки или снимая защиту листа.

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

Что произойдет, если щелкнуть заблокированную ячейку? Как только вы попытаетесь ввести данные, удалить данные, отформатировать ячейку и т. Д., Excel отобразит сообщение, которое гласит: «Ячейка или диаграмма, которые вы пытаетесь изменить, защищены и поэтому доступны только для чтения» и объясняет, как снять защиту с листа.

Защита рабочей книги

Книгу также можно защитить. Следуйте инструкциям по защите рабочего листа, но вместо этого ищите «книгу». Защита книги не позволяет пользователям изменять только структуру книги. Листы нельзя вставлять, переименовывать, добавлять, удалять, перемещать или копировать.

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

↑ Вернуться к началу

Советы по проверке данных Excel и устранение неполадок

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

ПРИМЕЧАНИЕ : Если вам нужна помощь с настройкой раскрывающегося списка, перейдите на страницу раскрывающихся списков Excel

Раскрывающийся список открывается с выбранным пустым местом

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

Получите образец файла в разделе загрузок.

Причина: пустые ячейки в списке источников

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

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

ПРИМЕЧАНИЕ : Другая проблема может возникнуть, если есть пробелы в списке источников — недопустимые записи могут быть разрешены в камерах.

Исправление

: использование динамического списка источников

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

1. Лучшим вариантом является именованная таблица Excel
2. Другой вариант — динамический именованный диапазон, основанный на формуле

1.Именованная таблица Excel

Чтобы увидеть шаги по созданию таблицы Excel, вы можете посмотреть это короткое видео. На странице «Создание таблицы Excel» есть письменные инструкции.

2. Динамический именованный диапазон с формулой

Чтобы увидеть шаги по настройке динамического именованного диапазона, вы можете посмотреть это короткое видеоурок. Формула СМЕЩЕНИЯ показана под видео.

Формула СМЕЩЕНИЯ, используемая в этом примере:

• = СМЕЩЕНИЕ (Цены! $ B $ 2,0,0, COUNTA (Цены! $ B: $ B) -1,1)

Отсутствующие элементы в раскрывающемся списке

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

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

Причина: раскрывающееся меню на основе статического списка

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

Если в ячейку B5 ввести новый элемент, он не появится в раскрывающемся списке

Исправление

: изменение источника проверки данных

Чтобы устранить проблему с отсутствующим элементом, выполните следующие действия:

• Выберите ячейки проверки данных
• На вкладке «Данные» ленты Excel щелкните «Проверка данных».
• На вкладке «Настройки» измените адрес диапазона * в поле «Источник», чтобы включить новые элементы.
• Щелкните OK, чтобы завершить изменение.

* Имя в поле источника

Вместо адреса вы можете увидеть имя в поле «Источник», например:

Чтобы исправить это:

• На вкладке «Формулы» ленты Excel щелкните «Диспетчер имен».
• Выбрать имя в списке
• В поле «Ссылается на» измените адрес, чтобы включить новые элементы.
• Щелкните галочку, чтобы завершить изменение, затем закройте Диспетчер имен

Предотвращение: использование динамических списков источников

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

1. Именованная таблица Excel
2. Динамический именованный диапазон, основанный на формуле

См. Видео выше, в котором показаны шаги для обоих вариантов

Отсутствующие стрелы

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

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

Вот несколько причин отсутствия стрелки для проверки данных. Щелкните ссылку для подробностей:

Только активная ячейка

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

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

• Щелкните вкладку Разработчик на ленте и щелкните Вставить
• Щелкните поле со списком в элементах управления формой
• На листе перетащите, чтобы добавить поле со списком нужного размера.
• Щелкните правой кнопкой мыши поле со списком и выберите Управление форматированием
• В поле Диапазон ввода введите имя или адрес списка
• Нажмите ОК

Поиск предметов

Если объекты скрыты на листе, раскрывающийся список проверки данных стрелки также будут скрыты.

Чтобы сделать объекты видимыми, используйте сочетание клавиш — Ctrl + 6

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

• Щелкните вкладку Файл на ленте и щелкните Параметры
• Щелкните категорию Advanced
• Прокрутите вниз примерно наполовину до раздела Параметры отображения для этого Рабочая тетрадь.
• В настройке «Для объектов показать:» щелкните Все
• Нажмите ОК

Раскрывающийся список

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

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

Excel 2013 Windows 8

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

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

1. Выберите ячейку со списком проверки данных
2. Щелкните за пределами окна Excel (например, щелкните Рабочий стол или щелкните в окне браузера)
3. Щелкните окно Excel, появится стрелка, и вы сможете выберите элемент из списка.

Морозильные панели

Параметр Freeze Panes может вызвать проблемы со стрелками раскрывающегося списка в все версии Excel.Были дополнительные проблемы в Excel 97 и ранее.

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

Спасибо Джону Констеблю за этот совет.

В Excel 97, если раскрывающийся список Проверка данных находится в закрепленной области окна, стрелка раскрывающегося списка не отображается когда ячейка выбрана.В качестве обходного пути используйте Window | Split вместо Окно | Стоп-окна

ПРИМЕЧАНИЕ. Эта проблема была исправлена ​​в более поздних версиях.

• Без замороженного стекла
• 
  С замороженными стеклами

Коррупция

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

Или попробуйте восстановить файл при открытии:

1. На ленте щелкните Файл, а затем щелкните Открыть
2. Щелкните Компьютер, затем щелкните Обзор
3. Выберите файл с отсутствующими стрелками проверки данных
4. В нижней части окна «Открыть» щелкните стрелку справа от Кнопка открытия
5. Нажмите «Открыть и восстановить»
6. При появлении запроса нажмите «Восстановить».

Удалено макросом

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

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

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

Безопасные макросы : Для безопасного удаления других фигур без удаления стрелок проверки данных см. Макросы для удаления объектов на веб-сайте Рона де Брюэна.

 Sub DeleteShapesALL ()
ПРЕДУПРЕЖДЕНИЕ: удаляет стрелку значения данных.
'если это видно
Дим ш как форма
Dim ws как рабочий лист
Установить ws = ActiveSheet
Для каждой ши в ws.Shapes
  ш.Удалить
Следующий ш
Конечный переводник 

Допустимые записи запрещены

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

Например, этот список позволяет выбрать Да или Нет.

Однако, если вы наберете no , появится сообщение, что это недопустимо.

Причина: список с разделителями

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

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

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

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

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

Разрешены недопустимые записи

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

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

— Пустые ячейки в списке источников

— Предупреждение об ошибке отключено

Пустые ячейки в списке источников

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

Примечание : Эта проблема с пустыми ячейками не возникает, если исходным списком является адрес диапазона , например 1 австралийский доллар: 10 австралийских долларов

Причина: пустые ячейки в именованном диапазоне

На снимке экрана ниже столбец Manager имеет раскрывающийся список с 5 имен.

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

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

Исправление: выключить игнорировать пустые

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

1. Выберите ячейку, содержащую список проверки данных
2. Выбрать данные | Проверка
3. На вкладке Настройки снимите галочку с Игнорировать пустая коробка .
4. Нажмите ОК

Предупреждение об ошибке

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

Исправление

: включение предупреждения об ошибке

Чтобы включить оповещение:

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

Проблема с неверными данными в круге

Если вы используете функцию «Круг недопустимые данные» в Excel, вы можете иногда видеть неожиданные результаты.

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

Максимальное количество элементов в раскрывающемся списке

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

• Список может отображаться до 32 767 элементов из списка на рабочий лист.
• Если вы вводите элементы в диалоговом окне проверки данных (разделенный list) ограничение составляет 256 символов , включая разделители.

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

Размер выпадающего шрифта и длина списка

Размер шрифта проверки данных и длину списка изменить нельзя.

Размер шрифта

• Шрифт в раскрывающемся списке проверки данных: Tahoma, размер 8.
• Форматирование ячейки не влияет на шрифт в раскрывающемся списке.

Длина списка

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

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

Список источников на другом листе

ПРИМЕЧАНИЕ : это влияет только на старые версии Excel

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

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

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

Выпадающий список с символами

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

Причина: выпадающий шрифт

В раскрывающемся списке всегда отображается шрифт Tahoma . Это НЕ , на которое влияет форматирование:

• Ячейки исходного списка ИЛИ
• Ячейка с выпадающим списком

Исправление: использовать символы Tahoma

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

Получите образец файла в разделе загрузок.

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

Для создания списка символов:

1. На листе выберите ячейку, с которой вы хотите начать список символов
2. Нажмите клавишу Alt и на цифровой клавиатуре введите число для символ, который вы хотите вставить.Несколько примеров показаны в список ниже, и вы можете поэкспериментировать, чтобы найти другие символы.
   Примечание. Чтобы увидеть все коды, перейдите в список альтернативных кодов в Википедии.
3. Нажмите Enter и введите другие символы в ячейки ниже. в В списке, показанном выше, клавиша Alt использовалась с номерами 30, 29 и 31, для создания списка со стрелками вверх и вниз и двунаправленной стрелкой.

Для создания выпадающего списка с символами:

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

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

Показать раскрывающийся список

• Мышь: щелкните стрелку ячейки
• Клавиатура: нажмите Alt + стрелка вниз

Прокрутка элементов списка

Мышь

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

Клавиатура

• Нажимайте клавиши со стрелками вверх или вниз для непрерывной прокрутки
• Нажимайте клавиши со стрелками вверх или вниз для прокрутки по одному элементу за раз
• Коснитесь клавиши Home или End, чтобы перейти к началу или концу списка.
• Нажмите кнопку Page Up или Page Down для перехода на одну страницу вверх или вниз
• Нажмите клавишу Page Up или Page Down для непрерывной прокрутки страницы

Выпадающие списки на защищенном листе

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

• ячейка заблокирована
• лист защищен

ПРИМЕЧАНИЕ : В Excel 2000 и более ранних версиях

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

Выпадающие списки и события изменений

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

Чтобы увидеть пример, перейдите к образцам рабочих листов странице, а под заголовком Filters найдите Product List Категория и загрузите ProductsList.zip файл.

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

Другой вариант в Excel 97 — использовать событие Calculate для запуска код. Для этого обратитесь к ячейке с проверкой данных в формуле на листе, например = МАТЧ (C3, CategoryList, 0) . Затем добавьте код фильтра для события Calculate рабочего листа. Чтобы увидеть пример, перейдите на страницу с образцами рабочих листов и под заголовком Filters найдите Product List by Category , и загрузите ProductsList97Calc.zip файл.

Временно расширить раскрывающийся список

Раскрывающийся список Проверка данных — это ширина ячейки, в которой он находится, минимум до 3/4 «. Вы можете использовать событие SelectionChange временно расширить столбец, когда он активен, а затем сузить его когда вы выбираете ячейку в другом столбце.

Например, с ячейками проверки данных в столбце A:

 Private Sub Worksheet_SelectionChange (значение ByVal как диапазон)
  Если Target.Счетчик> 1, затем выйти из подпрограммы
   Если Target.Column = 1, то
       Target.Columns.ColumnWidth = 20
   Еще
       Столбцы (1) .ColumnWidth = 5
   Конец, если
Конец подписки
 

Чтобы добавить этот код на рабочий лист:

1. Щелкните правой кнопкой мыши вкладку листа и выберите «Просмотреть код».
2. Скопируйте код и вставьте его в модуль кода.
3. Измените ссылку на столбец с 4 в соответствии с вашим листом.

Увеличить раскрывающийся список

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

Если вы уменьшите масштаб на листе, проблема усугубится. Например, на этом снимке экрана показан раскрывающийся список с масштабом 80%.

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

1) Используйте макрос для отображения поля со списком или списка

2) Постоянно увеличивайте масштаб листа (вручную)

3) Временно увеличьте масштаб с помощью макроса

Использовать поле со списком или поле со списком

Чтобы упростить чтение элементов проверки данных, вы можете использовать программирование с полем со списком или списком, чтобы показать записи.Шрифт в них может быть любого размера, и вы также можете настроить их так, чтобы одновременно отображалось более 8 элементов по умолчанию.

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

Постоянное изменение настройки масштабирования

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

Вот снимок экрана с раскрывающимся списком с уровнем масштабирования 100%. В строке 2 ячейки отформатированы шрифтом Cambria, размер 12.

• Чтобы упростить чтение раскрывающегося списка, отрегулируйте масштаб рабочего листа до 120% или другого предпочтительного параметра.
• Затем, чтобы остальная часть листа выглядела «без увеличения», уменьшите размер шрифта в ячейках листа и сузьте столбцы.

Вот тот же лист с уровнем масштабирования 120%.Шрифт в строке 2 был уменьшен с 12 до 10, поэтому он выглядит примерно того же размера, что и раньше.

Временное изменение настройки масштабирования

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

Ниже приведены 3 примера макросов:

— Масштаб при выделении одной конкретной ячейки

— Увеличение при выборе одной из определенных ячеек

— Масштабирование при любой ячейке с проверкой данных список выбран

Увеличение масштаба при выделении определенной ячейки

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

 Private Sub Worksheet_SelectionChange (значение ByVal как диапазон)
  Если Target.Address = "$ A $ 2", то
    ActiveWindow.Zoom = 120
  Еще
    ActiveWindow.Zoom = 100
  Конец, если
Конечный переводник 

Чтобы добавить этот код на рабочий лист:

1. Щелкните правой кнопкой мыши вкладку листа и выберите «Просмотреть код».
2. Скопируйте код и вставьте его в модуль кода.
3. Измените ссылку на ячейку с $ A $ 2, чтобы она соответствовала вашему листу.

Увеличение масштаба при выделении определенных ячеек

Если несколько ячеек имеют список проверки данных, следующий код изменит настройку масштабирования на 120%, когда любая из этих ячеек выбрано. В этом примере в ячейках A1, B3 и D9 есть проверка данных.

 Private Sub Worksheet_SelectionChange (значение ByVal как диапазон)
  Если Target.Cells.Count> 1, то выйдите из Sub
  Если пересечение (Target, Range ("A1, B3, D9")) равно ничто, тогда
    ActiveWindow.Масштаб = 100
  Еще
    ActiveWindow.Zoom = 120
  Конец, если
Конечный переводник 

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

Следующий код изменит настройку масштабирования на 120% при любом выбирается ячейка со списком проверки данных.

 Private Sub Worksheet_SelectionChange (значение ByVal как диапазон)
  Уменьшить l Увеличить до тех пор, пока
  Dim lZoomDV As Long
  Dim lDVType As Long
  lZoom = 100
  lZoomDV = 120
  lDVType = 0

  Заявление.EnableEvents = False
  При ошибке Возобновить Далее
  lDVType = Target.Validation.Type
  
    При ошибке GoTo errHandler
    Если lDVType <> 3 Тогда
      С ActiveWindow
        Если .Zoom <> lZoom То
          .Zoom = lZoom
        Конец, если
      Конец с
    Еще
      С ActiveWindow
        Если .Zoom <> lZoomDV Тогда
          .Zoom = lZoomDV
        Конец, если
      Конец с
    Конец, если

exitHandler:
  Application.EnableEvents = True
  Выход из подводной лодки
errHandler:
  Перейти к exitHandler
Конечный переводник 

Получить файлы примеров

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

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

для недопустимых записей

Выпадающие символы : В этой книге есть пример выпадающего списка с символами шрифта Tahoma: Данные Список валидации с символами

Не пропустите наши советы по Excel

Не пропустите мои последние советы и видео по Excel! Нажмите OK, чтобы получать мой еженедельный информационный бюллетень с советами по Excel и ссылками на другие новости и ресурсы Excel.