Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Ячейка в Excel не считает

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

Устраняем проблему

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

При протягивании не работают формулы

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

  1. Нажмите кнопку F9, чтобы начать пересчет листа.
  2. Изменить формат ячейки в целом. Для этого выделите ячейку и щелкните по ней правой кнопкой мыши. Откроется меню, в котором нужно выбрать пункт «Формат ячейки» и изменить его на «Общий».
  3. Откройте параметры расчета формулы и убедитесь, что отметки стоят так, как показано на скриншоте.

Формула не считает

Есть несколько причин, почему формула в Excel не считается:

  • Форматирование ячейки некорректно. Чтобы изменить его, выделите нужный диапазон ячеек и выберите «Форматировать ячейки».

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

  • Формула или ее синтаксис были введены неверно. В этом случае вместо значения отображается формула. Это следует тщательно проверить.

Некоторые из причин, по которым Excel не считает формулы, приведены здесь.

Программа не считает формулу, введённую в ячейку

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

Не происходит подсчет суммы

Есть два варианта, почему приложение Excel не подсчитывает сумму:

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

Вот почему количество не имеет значения.

Не верно суммируются данные ячеек

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

Отключен перерасчет

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

  1. Откройте параметры программы. Там нужно перейти в раздел «Формулы». Вверху будет блок «Настройки расчетов», а в нем — настройка «Расчеты в книге», где должна быть выбрана позиция «Автоматически». Если это не так, установите нужный параметр и нажмите «OK».

Проблемы с работой формул в Microsoft Excel

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

Читайте так же:
Как изменить цвет вкладки листа в зависимости от значения ячейки?

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

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

На этом этапе мы рассмотрим расширенную фильтрацию.

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

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

Определение сферы действия критериев

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

  • Состоять, по крайней мере, из двух строк, первая из которых должна содержать все или некоторые названия полей списка.
  • Остальные строки должны содержать критерии фильтрации.

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

Рисунок 1. Диапазон критериев в списке

Выберите команду Данные | Фильтр | Расширенный фильтр. На рисунке 1 показано диалоговое окно Расширенный фильтр. 2).

Рис. 2. Диалоговое окно расширенного фильтра

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

Множественные критерии отбора

При наличии более одной строки в диапазоне критериев критерии в каждой строке соединяются оператором OR. На рисунке 3 мы видим два ряда критериев с диапазоном критериев A1:D3.

Рисунок 3. Этот диапазон критериев содержит два набора критериев

В этом примере строки в отфильтрованном списке отвечают следующим требованиям:

  • В поле Месяц содержится значение Январь И в поле Товар — значение Товар В .
  • ИЛИ в поле Месяц содержится значение Февраль . И при этом значения в поле Всего превышают 1000.

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

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

  • Текстовые или числовые критерии. При фильтрации используется сравнение с числом или строковой (текстовой) константой с помощью операторов, таких как равно ( = ), больше чем ( > ), не равно ( <> ) и т.д.
  • Вычисляемые критерии. Фильтрация, использующая вычисления любого рода.

Текстовые или численные критерии

Список текстовых и числовых операторов сравнения приведен в таблице 1.

В следующей таблице приведены примеры строковых (текстовых) критериев.

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

Рисунок 4. Этот список будет отфильтрован с помощью вычисленного критерия

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

В ячейке А2 содержится следующая формула: = С5 — В5 + 1 >= 30

Эта формула возвращает булево значение типа True или False. Формула относится к ячейкам, содержащим данные в первой строке списка, после строки заголовка. При фильтрации списка будут отображаться только проекты с продолжительностью (Дата окончания — Дата начала + 1) больше или равной 30 дням. Другими словами, операция сравнения основана на вычислении.

Если необходимо отфильтровать список так, чтобы были показаны только проекты, которые используют ресурсы больше среднего, примените следующую формулу: = D5 > СРЗНАЧ(D:D)

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

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

  • Не используйте заголовки полей списка в диапазоне критериев. Создайте новый заголовок или просто оставьте пустую ячейку.
  • Можно использовать любое количество вычисляемых критериев, а также сочетания вычисляемых критериев с невычисляемыми.
  • Не обращайте внимание на значения, возвращаемые формулами в диапазоне критериев. Они ссылаются на первую строку списка.
  • Если Ваша вычисляемая формула ссылается на значение вне списка, используйте абсолютные, а не относительные ссылки. Например, вместо C1 используйте ссылку $C$1 .
  • При создании формул вычисляемых критериев используйте первую строку списка (не строку заголовков). Используйте относительные, а не абсолютные ссылки. Например вместо $C$5 используйте ссылку C5 .

Другие расширенные параметры фильтрации

В диалоговом окне Расширенный список представлены две дополнительные опции:

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

Копирование выбранных линий

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

Отображать только уникальные строки

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

Далее мы рассмотрим, как данные можно использовать в списках.

Защита данных в Excel, уровни защиты данных

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

Как защитить ячейки в Excel от изменения?

Файл, созданный в Microsoft Excel, основным компонентом которого является рабочий лист, называется рабочей книгой. Таким образом, все рабочие книги Excel состоят из рабочих листов. Рабочая книга не может содержать менее одного рабочего листа. Рабочие листы, в свою очередь, состоят из ячеек, расположенных в вертикальных столбцах и горизонтальных рядах. Ячейки рабочего листа содержат различные типы информации о формате чисел, выравнивании, отображении и направлении текста, имени шрифта, типе шрифта, размере и цвете шрифта, типе линии и цвете границы, цвете фона и безопасности. Вы можете просмотреть все эти данные, выбрав Format Cells из контекстного меню правой кнопки мыши. В появившемся диалоговом окне на вкладке «Защита» есть две опции: «Защитить ячейку» и «Скрыть формулы». По умолчанию флажок Защитить установлен для всех ячеек, а флажок Скрыть формулы снят. Установка флажка «Защитить ячейку» не означает, что ячейка уже защищена от изменений, это означает только то, что ячейка будет защищена после активации защиты листа.

защита ячеек в Excel

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

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

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

Как установить защиту листа (элементов листа) в Excel?

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

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

Защита отдельных элементов книги Excel (структуры и окон)

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

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

Защитить книгу можно с помощью меню Инструменты/Защита/Защита книги Excel 2003.

как защитить книгу в Excel 2003

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

как защитить книгу в Excel 2007

В Excel 2010 щелкните значок Защитить книгу на вкладке Рецензирование в группе Правка.

как защитить книгу в Excel 2010

Во всех вышеперечисленных случаях появляется диалоговое окно «Защита структуры и окон».

защита структуры и окон в Excel

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

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

Выбор защиты окна запрещает изменение размера и положения открытой книги, а также перемещение, изменение размеров и закрытие окон.

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

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

Обратите внимание! Если кнопки «Защитить лист» и «Защитить рабочую книгу» неактивны, поле «Разрешить нескольким пользователям редактировать файл одновременно» на вкладке «Редактирование» отмечено. Чтобы снять этот флажок, перейдите к пункту меню Сервис / Доступ к рабочей книге. (если вы работаете в Excel 2003) или нажмите кнопку «Доступ к рабочей книге» в группе кнопок «Ревизии» (если вы работаете в Excel 2007/2010/2013).

Защита паролем всего файла книги Excel от просмотра и внесения изменений

Этот метод защиты данных Excel обеспечивает оптимальную безопасность, ограничивая доступ к файлу и предотвращая его несанкционированное открытие. Файл защищен паролем, длина которого не должна превышать 255 символов. Вы можете использовать все символы, пробелы, цифры и буквы, как русские, так и английские, но пароли, написанные русскими буквами, не распознаются правильно при использовании Excel на компьютерах Macintosh. Только пользователи, знающие пароль, могут получить доступ к защищенным паролем книгам. Вы можете указать два отдельных пароля для открытия (просмотра) файла и для внесения изменений в файл. Защита паролем для открытия и просмотра файла использует шифрование. Пароль для изменения файла не зашифрован.

Существует два способа установить пароль для открытия файла в Excel 2007. В меню Офис/Подготовка/Подготовка/Шифрование документов

как установить пароль на открытие в Excel 2007

После нажатия кнопки «Зашифровать документ» появится окно «Зашифровать документ», в котором необходимо ввести пароль

установка пароля на открытие в Excel 2007

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

установка пароля для защиты файла при открытии

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

установка пароля на открытие при сохранении файла

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

установка пароля на открытие файла в Excel 2010

Окно ввода пароля такое же, как и в Excel 2007. Как и в Excel 2007, можно вводить пароли для открытия файла и внесения в него изменений, а также отдельные пароли при сохранении файла.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector