Создание сайта «Электронные таблицы Excel»

Пример собственных  имен ячеек: Итоги_года, Системный_баланс и т.д.

 Собственные (пользовательские) имена могут содержать листы  книги Excel:

• имя не должно быть заключено в квадратные скобки [];
• длина имени не превышает 31 символа;
• имя листа не может содержать двоеточие(:), слэш (/) и обратную косую черту(\), знак вопроса (?), звездочку (*).

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

60. Введите в ячейку J6 текст Евро =.
61. Введите в ячейку К6 значение 0,0468 – стоимость одного рубля в пересчете на евро.
62. Выделите ячейку К10.
63. Подготовьтесь к вводу формулы, введите =.
64. Укажите ячейку J10,а затем введите *1000*.
65. Укажите на ячейку К6.
66. Нажмите клавишу F4. Это приведет к замене относительной ссылки К6 на абсолютную $K$6.
67. Скопируйте с помощью автозаполнения эту формулу в ячейки К11:К13. Обратите внимание, что формула в ячейке К11 выглядит как = J11*1000*$K$6, абсолютный адрес ячейки К6 остался прежним. Для тренировки самостоятельно заполните оставшиеся ячейки в строке Прибыль.

Форматирование данных и защита информации в Excel

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

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

• кнопки панелей инструментов Стандартная и Форматирование;
• команду меню окна ФОРМАТ – Ячейки;
• команду контекстного меню Формат ячеек.

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

• число;
• выравнивание;
• шрифт;
• граница;
• вид;
• защита.

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

На вкладке Шрифт задаются параметры символов для выделенного текста.

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

• по горизонтали (по значению, по центру выделения и т.д.);
• по вертикали;
• ориентация (поворот текста, расположение текста в ячейке по вертикали).

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

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

Автоформатирование. Excel 2000 предлагает около 20 стандартных образцов оформления таблицы с использованием различных шрифтов, способов обрамления и заливок. Для автоформатирования необходимо:

• выделить нужный диапазон ячеек или щелкнуть по любой ячейке форматируемой таблицы;
• ввести команду ФОРМАТ – Автоформат;
• в диалоговом окне автоформат выбрать подходящий вариант оформления.

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

Для применения условного форматирования нужно:

1. Выделить необходимые ячейки.
2. Ввести команду ФОРМАТ - Условное форматирование.
3. В диалоговом окне Условное форматирование выполнить одно из следующих действий:
• Выбрать параметр Значение и ввести нужное значение или формулу, поставив перед ней знак равенства (=);
• Выбрать параметр Формула и в поле справа ввести нужную формулу.
4. Щелкнуть по кнопке Формат и выбрать тип шрифта, его цвет, подчеркивание, рамку, а так же цвет заливки и узор.

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

 

Защита информации таблицах Excel

 

B Excel 2000 имеются разнообразные  способы защиты информации, которые позволяют:

1. ограничить доступ к документу в целом;
2. ограничить возможность внесения изменений в документ;
3. ограничить доступ к отдельным фрагментам документа – рабочим листам или ячейкам;
4. скрыть отдельные фрагменты электронной таблицы;
5. скрыть файл документа – не отображать его название в окне папки и Проводника;
6. предотвратить заражение документа макровирусами.

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

• СЕРВИС – Защита – Защитить книгу;
• СЕРВИС – Защита – Защитить книгу и дать общий доступ;
• СЕРВИС- Доступ к книге;
• ОКНО – Скрыть.

При сохранении документа  можно ввести три варианта ограничения  доступа к файлам рабочих книг:

1. ввести пароль на открытие файла;
2. ввести пароль для разрешения на изменение данных
3. запретить изменение данных – разрешить открывать файл только для чтения и сохранять его только под другим именем.

Для защиты файлов рабочих  книг при сохранении документа нужно  ввести команду ФАЙЛ – Сохранить как, в диалоговом окне Сохранение документа щелкнуть по кнопке СЕРВИС и выбрать команду Общие параметры, а затем в диалоговом окне Параметры сохранения вести пароли установить нужные переключатели.

Если в диалоговом окне Защита книги установлен флажок Структуру, то запрещается:

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

Если установлен флажок Окна, то запрещается:

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

Снять защиту книги и  ограничения доступа можно с  помощью команды СЕРВИС – Защита – Снять защиту книги. Если был установлен пароль, то чтобы снять защиту, необходимо знать пароль.

Команда СЕРВИС – Защита – Защитить книгу и дать общий доступ используется для защиты элементов рабочей книги при работе с ней нескольких пользователей.

Команда ОКНО – Скрыть позволяет скрыть от пользователя всю книгу, но оставить доступ к её содержанию. Кроме того, в Excel имеется возможность заблокировать отдельные или все связи или ссылки в рабочей книге.

Защита рабочих  листов. Защиту отдельных рабочих листов можно установить с помощью команды СЕРВИС – Защита – Защитить лист (рис. 17). При этом файл рабочей книги можно свободно открывать, а в незащищенные листы вносить изменения.

 

рис.17

 

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

• содержимого ячеек;
• графических объектов – для запрета их перемещения и редактирования;
• сценариев.

Если в диалоговом окне Защитить лист установлен флажок Содержимое, то запрещаются:

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

Если установлен флажок Сценарии, то запрещаются:

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

Для того чтобы скрыть рабочий лист или его отдельные  части (строки или столбцы), используют команды ФОРМАТ – Лист (Строка, Столбец) – Скрыть. Для их отображения – команды ФОРМАТ – Лист (Строка, Столбец) – Отобразить.

Защита ячеек. Для защиты отдельных ячеек используется команда меню ФОРМАТ – Ячейки. (рис.18)

 

рис. 18

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

Средства автоматического обобщения и анализа данных электронной таблицы

Excel предоставляет разнообразные  способы для автоматического обобщения и анализа данных:

1. Автоматические вычисления;
2. Средства для работы с базами данных и со списками, позволяющие создавать, добавлять, удалять и находить записи в базах данных, осуществлять сортировку и фильтрацию (выборку) записей, работать с базами данных в режиме формы и с помощью специальных функций;
3. Средства автоматического подведения общих и промежуточных итогов;
4. Возможность создания и использования структуры таблицы;
5. Средства для обобщения данных;
6. Возможность создания и модификации сводных таблиц, отчетов и диаграмм;
7. Средства условного анализа (анализа “что, если”), к которым относятся:
• Подбор параметра;
• Надстройка Поиск решения;
• Сценарии;
• Таблицы подстановки.
8. Различные надстройки, мастера, шаблоны, например Мастер суммирования, подстановок, шаблонов, Мастер обновления связей, надстройка Пакет анализа и др.

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

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

Сортировка  данных. Сортировка осуществляется непосредствен в электронной таблице. Строки, столбцы и ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем порядке (от 1 до 9, от А до Я) или в убывающем порядке (от 9 до 1, от Я до А). Сортировать можно как текстовые, так и числовые данные. Если в сортируемом столбце содержатся и текстовые и числовые данные, то после сортировки числовые значения будут расположены перед текстом. Сортировка данных в электронных таблицах производятся с помощью команд меню ДАННЫЕ – Сортировка или с помощью кнопок [А^Я↑], [Я^А↓] на Стандартной панели инструментов.