Компьютер для секретаря и офис-менеджера

Для упрощения ввода функций в Excel предусмотрен специальный мастер функций, который можно вызвать нажатием кнопки/* на стандартной панели инструментов (рис. 4.4).

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

В списке Категория можно выбрать категорию функций (например, Логические).

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

Примеры функций MS Excel

Рассмотрим некоторые из популярных функций программы MS Excel.

Функция СРЗНАЧ (список аргументов) – вычисляет среднее арифметическое значений всех аргументов. Например, функция СРЗНАЧ(A1:A5) подсчитает среднее значение чисел, записанных в ячейках A1, A2…, A5.

Функция МАКС (список аргументов) – выберет максимальное число из списка аргументов, а функция МИН (список аргументов) – выберет минимальное число из списка аргументов. Например, МАКС (A1:A5) выберет максимальное из чисел, записанных в диапазоне А1, A2…, A5.

Математическая функция КОРЕНЬ (число) – подсчитает квадратный корень из числа.

Функции SIN (число), СOS (число), ТАN (число) – вычисляют, соответственно, синус, косинус, тангенс числа, заданного в радианах.

Совет.

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

При решении некоторых задач значение ячейки необходимо заполнить одним из нескольких способов в зависимости от выполнения или невыполнения одного или нескольких условий. При этом используются логические функции, взятые из логической (булевой) алгебры. Рассмотрим пример использования логической функции IF (ЕСЛИ) – рис. 4.5.

Рис. 4.5. Пример применения логической функции ЕСЛИ.

В данном примере студенту начисляется стипендия в зависимости от успешной сдачи экзамена по математике, и в формуле =ЕСЛИ(C3>=4;«есть стипендия»;«нет стипендии») проверяется условие наличия у студента по этому предмету четырех баллов или выше.

Защита данных

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

Рис. 4.6. Окно для установки защиты данных.

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

Примечание.

Для защиты листа нужно выполнить команду Сервис Защита > Защитить лист и ввести пароль. С этого момента при попытке что-то исправить на листе будет появляться сообщение «Ячейка или диаграмма защищена от изменений». Для снятия защиты используется тот же пункт меню.

Работа со списками

Список – это таблица, данные в которой расположены, как в базе данных, то есть в такой таблице каждый столбец имеет свой заголовок, который называется именем поля (рис. 4.7). Все имена полей располагаются в первой строке таблицы, и на каждое имя отводится одна ячейка. Отдельный столбец такой таблицы называется полем данных, а каждая строка – записью.

Рис. 4.7. Пример списка.

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

Примечание.

Базы данных состоят из записей. Запись, в свою очередь, состоит из полей (строк) и заголовков (столбцов). Пример базы данных – телефонная книга. В полях здесь ФИО и номера телефонов абонентов, а заголовки удобно использовать для поиска данных, их сортировки и т. п.

Фильтрация данных. Автофильтр

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

Таблица 4.1. Работники ПАТП-2

Если выбрать первую ячейку (ФИО) и выполнить команду Данные Фильтр Автофильтр, то таблица примет вид, как на рис. 4.8.

Рис. 4.8. К списку применен автофильтр.

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

Рис. 4.9. Осуществлен выбор данных из списка.

Внимание!

Обратите внимание, что стрелка-условие, изменила свой цвет с черного на синий. Теперь для того, чтобы вернуть все назад (то есть убрать условие), нужно опять нажать на ту же стрелку и выбрать пункт «Все». Установленный автофильтр также можно снять (отключить в меню командой Данные Фильтр Автофильтр).

Рассмотрим еще один вариант – использование пользовательского автофильтра по сложному условию. Для этого после применения к табл. 4.1 команды Данные Фильтр Автофильтр раскроем список Год и фильтром выберем всех лиц, которые родились позже 1960 года. Для этого после нажатия на стрелку Год рождения следует выбрать строчку Условие (рис. 4.10).

Рис. 4.10. Окно выбора условия.

Сортировка данных

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

и Сортировка по убыванию:

Более сложную сортировку данных из списка выполняют командой Данные Сортировка (рис. 4.11).

Рис. 4.11. Окно Сортировка диапазона.

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

Упражнение 4.3. Учет товаров

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

Шаг 1. Создание исходной таблицы

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

Рис. 4.12. Исходная таблица рыболовных товаров.