Глава 3. Защита информации, числовые форматы и операторы Excel

Защита информации

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

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

- можно применить защиту к элементам книги, ограничив просмотр отдельных листов и/или изменение данных на листе.

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

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

Защита информации от просмотра и изменения основана на использовании паролей.

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

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

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

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

Задание 1. Защита файлов от несанкционированного просмотра.

Ваши действия:

1. Откройте книгу Лабораторные работы.xlsx из своей личной папки.

2. Выполните команду Файл, Сохранить как.

3. В окне Сохранение документа нажмите кнопку Сервис выберите команду Общие параметры. Появится диалоговое окно Общие параметры (рис. 3.1).

Рис. 3.1. Диалоговое окно установки пароля

4. Введите пароль для открытия файла. После ввода пароля и нажатия кнопки ОК всегда появляется окно подтверждения пароля (рис. 3.2), в котором необходимо пароль ввести еще раз. Это сделано во избежание случайного неправильного ввода пароля.

Рис. 3.2. Диалоговое окно для повторного ввода пароля

5. Нажмите на клавишу ОК.

Снять пароль можно только после открытия файла. Если вы ввели оба пароля, то при открытии файла на экране последовательно будут появляться два диалоговых окна подтверждения, заполните оба (рис. 3.3). Введите пароль и нажмите клавишу ОК.

Рис. 3.3. Окно ввода пароля при открытии книги

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

Задание 2. Защита листов.

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

Вы можете защитить:

- структуру книги (при этом листы защищены от удаления, переименования, перемещения и вставки);

- окна (при этом окна книги защищены от изменения размеров, перемещения и закрытия);

- содержимое ячеек текущего листа книги;

- все графические объекты текущего листа;

- сценарии.

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

Ваши действия:

1. В группе Изменения вкладки Рецензирование нажмите команду Защитить лист.

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

Рис. 3.4. Диалоговое окно Защита листа

Задание 3. Защита книги

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

Ваши действия:

1. Выполните команду Рецензирование, Изменения, Защитить книгу (рис. 3.5).

Рис. 3. Диалоговое окно Защита структуры и окон

2. В диалоговом окне Защита структуры и окон установите флажок структуру для защиты структуры книги и флажок окна для защиты окон книги. В поле Пароль введите пароль и нажмите кнопку ОК.

3. В окне подтверждения пароля введите его еще раз.

При попытке выполнить с книгой недопустимое действие выйдет соответствующее сообщение.

4. Чтобы снять защиту с книги в группе Изменения вкладки Рецензирование нажмите кнопку Защитить книгу и выберите команду Защита структуры и окон (см. рис. 13.25).

5. В окне Снять защиту книги (рис. 28) введите пароль и нажмите кнопку ОК.

Задание 4. Защита листов от просмотра.

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

Ваши действия:

1. Сделайте щелчок по ярлыку скрываемого листа правой кнопкой мыши.

2. В контекстном меню выберите команду Скрыть.

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

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

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

4. В контекстном меню выберите команду Отобразить.

5. В диалоговом окне Вывод на экран скрытого листа (рис. 3.7) сделайте двойной щелчок по имени отображаемого листа или выберите лист и нажмите кнопку ОК.

Рис. 3.7. Окно вывода на экран скрытых листов

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

Под числами в Microsoft Excel понимаются собственно числа, включая числа с десятичными и/или простыми дробями и числа с указанием символа процентов, а также даты и время.

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

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

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

Точно так же как отформатированное число отображается на листе, оно будет и напечатано.

Для установки числовых форматов используют вкладку Число диалогового окна Формат ячеек (рис. 3.8).

Рис. 3.8. Вкладка Число для установки числовых форматов

В группе Число вкладки Главная также расположены кнопки для установки некоторых видов числовых форматов: финансового формата, процентного формата, а также для установки разделителя разрядов и управлением количества отображаемых десятичных разрядов (рис. 3.9).

Рис. 3.9. Команды раздела Число вкладки Главная

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

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

Рис. 3.10. Выпадающее меню выбора числового формата

Независимо от количества отображаемых разрядов, числа хранятся с точностью до 15 разрядов. Если число имеет больше 15 значащих знаков, то разряды после 15-го преобразуются в нули. Очень маленькие (меньше 10^-30) и очень большие (больше 10²⁰) числа, а также числа, которые не помещаются в ячейку, отображаются в экспоненциальной форме.

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

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

Наибольшее число, которое можно ввести в ячейку составляет 9,99999999999999*10³⁰⁷. Точность представления чисел – 15 разрядов (значащих цифр).

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

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

Для удобства представления больших чисел группы разрядов при вводе можно отделять пробелами. Например, число 12345678 можно ввести как 12 345 678. В строке формул при этом число будет отображаться без пробелов между разрядами.

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

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

Задание 5. Исследовать форматы – числовой, денежный, финансовый.

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

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

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

Ваши действия:

1. Откройте книгу Упражнения.xlsx из своей личной папки.

2. Создайте лист с именем Упр.3.

3. Введите числа и примените к ним разные форматы, как показано на рис. 3.11.

4. Можно использовать комбинации клавиш для установки основных форматов. Клавиатурные комбинации приведены в табл. 3.1.

Таблица 3.1

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

Числовой (два знака после запятой)	Shift+Ctrl+!
Процентный (без дробной части)	Shift+Ctrl+%

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

Рис. 3.11. Применение числового, денежного и финансового форматов

Ввод даты и времени

Microsoft Excel воспринимает даты, начиная с 1 января 1900 года. Даты до 1 января 1900 года воспринимаются как текст. Наибольшая возможная дата – 31 декабря 9999 года.

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

Например, для ввода даты 12 августа 1918 года с клавиатуры в ячейку следует ввести: 12.8.1918 или 12-8-1918 или 12/8/1918.

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

Например, для ввода даты 12 апреля 1961 года с клавиатуры в ячейку достаточно ввести: 12.4.61 или 12-4-61 или 12/4/61.

Для дат текущего десятилетия год можно указывать одной цифрой.

Например, для ввода даты 2 ноября 2002 года с клавиатуры в ячейку достаточно ввести: 2.11.2 или 2-11-2 или 2/11/2.

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

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

Например, для ввода даты 2 ноября сего года с клавиатуры в ячейку достаточно ввести: 2.11 или 2-11 или 2/11.

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

Текущую дату можно ввести комбинацией клавиш Ctrl + Shift + 4.

Независимо от способа ввода и последующего форматирования дата в строке формул всегда отображается в полном формате: ДД.ММ.ГГГГ.

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

Например, для ввода времени 14 часов 12 минут 00 секунд в ячейку достаточно ввести: 14:12.

Для отображения времени суток в 12-часовом формате следует ввести букву a или p (обязательно в английской раскладке клавиатуры), отделенную пробелом от значения времени, например 9:00 p. После ввода время будет отображено с обозначением РМ.

Чтобы ввести текущее время, можно нажать комбинацию клавиш Ctrl + Shift + 6.

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

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

Задание 6. Исследовать форматы даты и времени.

Дата и Время – записанные в ячейки рабочего листа Excel хранит в виде чисел: дата представляет собой число дней, отсчитанное от 01 января 1900 года (эта дата называется базовой), а время – долю суток, отсчитываемую от 0 часов.

Ваши действия:

1. На листе Упр.3 в ячейки А20:В33 введите одну и ту же дату – 25 октября 2016 года.

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

3. Ко всем ячейкам столбца В примените формат числа – Общий.

Обратите внимание, что 25 октября 2016 года – это 42668 -й день от базовой даты (рис. 3.12).

 

Рис. 3.12. Применение разных форматов даты

Операторы ссылок

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

Для описания ссылок на диапазоны ячеек используются следующие операторы.

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

Оператор объединения – это точка с запятой (;), который ссылается на объединение нескольких диапазонов.

Оператор пересечения – это пробел, который ссылается на общие ячейки диапазонов.

Оператор диапазона

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

Задание 7. Использовать оператор диапазона.

Ваши действия:

1. На листе с именем Упр.3 введите исходные данные, как показано на рис. 3.13.

Рис. 3.13. Примеры операторов диапазона и объединения

2. Выделите данные за 1 квартал (диапазон ячеек В36:В38) и назначьте денежный формат – рубли с 2-мя знаками после запятой.

3. Выделите данные за 2 квартал (диапазон ячеек С36:С38) и назначьте денежный формат – евро с 1-м знаком после запятой.

4. Выделите данные за 3 квартал (диапазон ячеек D36:D5) и назначьте денежный формат – доллар с 3-мя знаками после запятой.

5. Выделите данные за 4 квартал (диапазон ячеек E3:E5) и назначьте денежный формат – фунты стерлинги без указания дробной части.

Для быстрого выполнения некоторых действий с применением функций без запуска мастера функций можно использовать команду Сумма. Эта команда расположена в группе Редактирование вкладки Главная. Другое месторасположение этой команды – группа команд Библиотека функций вкладки Формулы (здесь она называется Автосумма).

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

6. Для вычисления суммы значений в ячейках В36:В38 в таблице на рис. 3.13 выделите ячейку В39 и нажмите команду Сумма.

7. Для подтверждения ввода формулы следует нажать клавишу Enter или еще раз нажать команду Сумма.

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

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

Среднее – расчет среднего арифметического;

Счетчик – определение количества численных значений;

Максимум – нахождение максимального значения;

Минимум – нахождение минимального значения.

Оператор объединения

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

Задание 8. Использовать оператор объединения.

Ваши действия:

1. Выделите ячейку С41.

2. Введите знак равенства в ячейку с клавиатуры или воспользуйтесь клавиатурной комбинацией Alt + =. В ячейку Excel автоматически вставит функцию СУММ() с мигающим курсором внутри круглых скобок.

3. Выделите с помощью мыши диапазон ячеек В36:Е36.

4. При нажатой клавише Ctrl выделите второй диапазон ячеек В38:Е38. Excel автоматически вставит между этими двумя диапазонами оператор объединения.

5. Нажмите на клавишу Enter. В ячейке С41 получится следующая формула (см. рис. 3.13):

= СУММ(B36:E36;B38:E38)

Оператор пересечения

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

Задание 9. Использовать оператор пересечения.

В этом примере, ячейка D37 является общей для двух диапазонов. Формула возвращает значение из ячейки, расположенной на пересечении двух диапазонов B37:E37 и D36:D38, другими словами, значение из ячейки D37.

Ваши действия:

1. Выделите ячейку С42.

2. Введите знак равенства.

3. Выделите с помощью мыши диапазон ячеек B37:E37.

4. Нажмите на пробел.

5. Выделите диапазон ячеек D36:D38.

6. Нажмите на клавишу Enter. В ячейке С42 получится следующая формула:

=(B37:E37 D36:D38)

Самостоятельная работа 3. Использование форматов.

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

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

Ваши действия:

1. В книге Лабораторные работы.xlsx на листе Лаб.3 выполните следующие задания:

- Создайте таблицу по приведенному образцу (рис. 3.14).

- Установите ориентацию листа – Альбомная.

- Для повторяющихся элементов форматирования используйте команду Формат по образцу.

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

- В столбцах Дата приобретения, Начислено-Дата, Выплачено - Дата установите разные форматы даты.

2. Сохраните файл и закройте MS Excel.

 

 

Рис. 3.14. Применение различных денежных форматов и форматов даты

Контрольные вопросы

1. Перечислите уровни защиты в MS Excel.

2. Как защитить файл от несанкционированного просмотра или изменения?

3. Как защитить лист и ячейки листа от изменений?

4. Как защитить структуру книги и окон от изменений?

5. Назовите числовые форматы.

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

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

8. Чем отличается денежный формат от финансового?

9. Какой клавиатурной комбинацией клавиш можно ввести в ячейку функцию суммы?

10. Какой клавиатурной комбинацией клавиш можно ввести в ячейку дату?

11. Какой клавиатурной комбинацией клавиш можно ввести в ячейку время?

12. Какой клавиатурной комбинацией клавиш можно установить в ячейке числовой формат с двумя знаками после запятой?

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

Тесты

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

A. Символьные.

B. Процентные.

C. Пользовательские.

D. Денежные.

E. Валютные.

F. Дробные числа.

G. Целые числа.

H. Даты.

I. Гипертекстовые.

2. В ячейку вводится 20.20. Что будет отображено в ячейке после подтверждения ввода?

A. Дата 1 января 2020.

B. Число 20,20.

C. Дата 1 января 1920 года.

D. Текст 20.20.

3. В ячейку вводится 2.2. Что будет отображено в ячейке B2, имеющей формат даты, после подтверждения ввода?

A. Дата 1 января 2002 года.

B. Число 2,2.

C. Дата 2 февраля текущего года.

D. Текст 2.2.

4. В ячейку вводится 2:2. Что будет введено в ячейку после подтверждения ввода?

A. Дата 2 февраля текущего года.

B. Число 1.

C. Время 2:02.

D. Текст 2:2.

5. В какой последовательности можно вводить дату?

A. Год – месяц – число месяца.

B. Год – число месяца – месяц.

C. Месяц – число месяца – год.

D. Число месяца – месяц – год.

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

A. Точка.

B. Запятая.

C. Пробел.

D. Двоеточие.

7. Каким символом при вводе числа в ячейку следует разделять целую и дробную десятичную части?

A. Точка.

B. Запятая.

C. Пробел.

D. Двоеточие.

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

A. Точка.

B. / ("слэш").

C. Запятая.

D. Пробел.

E. Двоеточие.

9. Какое из указанных значений Excel не распознает как дату?

A. 20.02

B. 14.07.1789

C. 01.01.1900

D. 01.01.2000

10. В ячейку вводится 2.20. Какие данные будут в ячейке после подтверждения ввода?

A. 1 февраля 2020 года.

B. Время 2 часа 20 минут.

C. Число 2,2.

D. Дата 20 февраля текущего года.

11. В ячейку вводится 20:20. Какие данные будут в ячейке после подтверждения ввода?

A. Дата 20 февраля текущего года.

B. Время 20 часов 20 минут.

C. Число 20,2.

D. Дата 20 февраля 1900 года.

12. При вводе числа после подтверждения ввода в ней оказались символы "решётка". Что это означает?

A. Среди цифр числа оказалась буква.

B. В этой ячейке имеется циклическая ссылка.

C. Ширины столбца мало для представления числа в данном формате.

D. Среди цифр числа оказался пробел.

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

A. Alt + =

B. Shift + =

C. Ctrl + =

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

A. Alt + Ctrl + 6

B. Shift + Ctrl + 6

C. Alt + Ctrl + 4

D. Shift + Ctrl + 4

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

A. Alt + Ctrl + 6

B. Shift + Ctrl + 6

C. Alt + Ctrl + 4

D. Shift + Ctrl + 4