Программа Microsoft Exell

Функции Excel

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

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

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

Рис. 1. Мастер функций

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

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

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

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

Текстовые функции Excel предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ .

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

Функции "Проверка свойств  и значений" предназначены для определения данных, хранимых в ячейке. Эти функции Excel проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ .

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

Упражнение 

Вычисление  величины среднего значения для каждой строки в файле Образование.хls.

1. Выделите  ячейку F3 и нажмите на кнопку  мастера функций. 

2. В первом  окне диалога мастера функций  из категории Статистические  выберите функцию СРЗНАЧ , нажмите на кнопку Далее .

3. Во втором  диалоговом окне мастера функций  должны быть заданы аргументы.  Курсор ввода находится в поле  ввода первого аргумента. В  это поле в качестве аргумента  число! введите адрес диапазона  B3:D3 (рис. 2).

4. Нажмите ОК .

5. Скопируйте  полученную формулу в ячейки F4:F6 и сохраните файл (табл. 1).

Рис. 2. Ввод аргумента  в мастере функций 

Таблица 1. Таблица результатов  расчета с помощью "Мастера  функций Excel"

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

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

Защита  ячеек Excel

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

Закладка Защита содержит опции Защищаемая ячейка и Скрыть формулы.

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

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

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

3. В меню Сервис выберите команду Защита, а затем команду Защитить лист.

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

Связывание  данных между листами Excel

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

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

• гибкость в оформлении рабочих листов;
• возможность связывать данные.

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

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

Трехмерные  ссылки Excel

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

Лист!: Лист2!В11  
Квартал!: Квартал4!А1:А10

Установки для просмотра 

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

Для установки  правил отображения элементов окна Excel выберите закладку Вид и установите требуемые параметры (рис. 1).

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

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

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

Рис. 1. Закладка Вид команды Параметры

Фиксация  панелей 

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

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

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

1. Выделите  подходящую ячейку, строку или  столбец. 

2. Выберите Окно, Закрепить области.

Переместите бегунок и просмотрите таблицу  при зафиксированных строках  заголовка.

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

Разделение  окон

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

Позиция активной ячейки, строки или столбца определяет тип разделения (при выделении  строки - вдоль листа, столбца - поперек, ячейки - на четыре части).

1. Выделите  подходящую ячейку, строку или  столбец. 

2. Выберите Окно, Разделить.

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

Как только окно будет разделено, команда Разделить изменится на команду Снять разделение . Для удаления разделения окна нужно выбрать эту команду.

Просмотр  макета

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

Выберите Файл, Предварительный просмотр или щелкните кнопку на панели инструментов Стандартная.

Макет рабочей  книги будет показан в окне предварительного просмотра. Документ можно просмотреть, используя линейки  прокрутки и панель инструментов предварительного просмотра (рис. 1). Для  управления масштабом просмотра  нажмите кнопку Масштаб.

Рис. 1. Кнопки управления предварительным просмотром

Упражнение

Предварительный просмотр документа 

1. Откройте  книгу Список сотрудников. 

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

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

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

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

5. Просмотрите  следующую или предыдущую страницу  с помощью кнопок Далее и Назад .

6. Нажмите  на кнопку Страница для перехода в диалоговое окно Параметры страницы .

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

Верхний и нижний колонтитулы 

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

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

Закладка Колонтитулы окна диалога Параметры страницы имеет область для ввода верхнего и нижнего колонтитулов (рис. 2).

Рис. 2. Диалоговое окно Верхний  колонтитул

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

Таблица 1. Кнопки диалогового  окна Верхний колонтитул

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

Упражнение 

Создание  колонтитулов

1. Откройте  файл Список_сотрудников.х1s на листе Общий список.

2. Выполните  команду Параметры страницы из меню Файл, перейдите на закладку Колонтитулы.

3. Очистите  верхний колонтитул. Для этого  откройте раскрывающийся список  в поле Верхний колонтитул и выберите из списка элемент Нет.

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

5. В окне  диалога Верхний колонтитул выберите левую секцию и введите текст: Фирма " Москва".

6. Щелкните  в правую секцию и нажмите  на кнопку Дата, которая поместит в колонтитул текущую дату.

7. Чтобы отформатировать  текст в колонтитуле, выделите  его, нажмите в окне диалога  кнопку Шрифт -

8. Выберите  подходящее форматирование и  нажмите кнопку ОК .

9. Нажмите  кнопку ОК в окне диалога Верхний колонтитул и перейдите в диалоговое окно Параметры страницы, где будет показан созданный верхний колонтитул.

10. Для создания  нижнего колонтитула нажмите  кнопку Создать нижний колонтитул.

11. Перейдите  в правую секцию и нажмите  на кнопку номера страницы, перед и после кода поставьте дефис (-).

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

13. В диалоговом  окне Параметры страницы будут показаны созданные колонтитулы.

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

Параметры печати

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

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

Рис. 3. Закладка Страница команды Параметры  страницы.

Таблица 2. Опции настройки  страницы