Редактор электронных таблиц Microsoft Excel
Запустите программу Microsoft Excel. На первый взгляд многие пункты горизонтального меню и кнопки на панели инструментов знакомы. Рабочая область имеет вид таблицы, состоящей из ячеек одинакового размера. Для выделения ячейки достаточно щелкнуть по ней кнопкой мыши. Курсор мыши в рабочей области имеет вид светлого креста.
Для ввода текста в ячейку достаточно выделить ячейку и сразу же вводить текст. После ввода текста нужно подтвердить изменения в ячейке. Для этого нажмите Enter (или щелкните мышью по любой другой ячейке, или перейдите в другую ячейку клавишами управления курсором на клавиатуре). Выделите ячейку. Текст в ячейке выравнивается кнопками на панели инструментов По левому краю, по центру, по правому краю.
Строки таблицы нумеруются числами 1, 2, 3 …, столбцы – буквами A, B, C … .
Упражнение 1. Форматирование таблиц
Скопируйте таблицу вместе с заголовком «Сравнительная таблица…» в задании 4 из документа Word в открытую книгу Excel. Выполним форматирование этой таблицы.
1. Выделите столбцы А – E, при помощи команды Формат – Столбец – Ширина установите 20 пт ширину столбцов.
2. В ячейке А1 находится заголовок таблицы, выделите ячейку и выполните команду Формат – Ячейки– Выравнивание – переносить по словам. Поместите его над всеми столбцами таблицы, для этого выделите ячейки А1:Е1 и нажмите на панели инструментов кнопку Объединить и поместить в центре. Для ячейки с заголовком выполните команду
3. Выделите всю таблицу и выполните команду Формат – Строка – Автоподбор высоты. См. рис.
4. Для ячеек B4:C8 задайте денежный формат числа с двумя знаками после запятой. (Формат – Ячейки – Число – Числовые форматы: Денежный – Обозначение: $ – Число десятичных знаков: 2)
Упражнение 2. Автоматическое заполнение ячеек значениями
Заполнение ячеек последовательностями чисел
В одну ячейку впишите число 1, в следующую ячейку впишите – 2, выделите обе ячейки, подведите курсор мыши к нижнему правому углу активной ячейки (курсор поменяет знак на черный крестик – маркер заполнения) нажмите на левую кнопку мыши
и не отпуская растяните вниз. 
Создайте на листе 2 таблицу по образцу
В ячейки А3:А7 внесите названия автозапчастей. Заполните столбец Продано и Цена в $.
Упражнение 3. Ввод формул, относительная и абсолютная адресация
Относительная адресация ячеек
При заполнении ячеек готовой последовательностью, например, из упражнения 2, произошла относительная адресация. Размножая ячейки по столбцу вниз, замечаем, что в каждой нижней ячейки номер столбца в формуле меняется на следующий (см. таблицу)
| 1 доллар | 25,60р. | |||
| Автозапчасть | Продано | Цена в $ | Цена в руб. | Сумма |
| Масляный фильтр | =Лист1!B4 | |||
| Сальник | =Лист1!B5 | |||
| Прокладка поддона | =Лист1!B6 | |||
| Прокладка крышки клапанного механизма | =Лист1!B7 | |||
| Ремень ГРМ | =Лист1!B8 |
Формулы, как и другие объекты Excel, можно копировать. Если не предпринять специальных мер, то адреса ячеек в формуле при копировании изменятся. Причем изменятся они относительно местоположения ячейки с первоначальной формулой.
Абсолютная адресация ячеек
Абсолютные адресав формулах – это адреса, которые не изменяются при копировании формул. Чтобы сделать адрес абсолютным, необходимо в адресе ячейки поставить два знака $: первый – перед буквой, чтобы не изменялся столбец, второй – перед цифрой, чтобы не изменялся номер строки $А$1, $F$10, $H$4 и т.п. Например, в формуле A2*$B$2 адрес A2 является относительным (т.е. будет меняться при копировании данной формулы), а адрес $B$2 – абсолютным (при копировании меняться не будет). При вводе адреса с клавиатуры нет необходимости писать знак $ вручную. Нажмите на клавишу F4, и EXCEL сделает это сам. По первому нажатию он превратит B2 в $B$2, по второму – в B$2, по третьему – в $B2.
В исходную таблицу в ячейки С3:С7 напишите формулу для вычисления Цены в руб в зависимости от курса доллара в ячейке Е1: =C3*$E$1
В ячейках Е3:Е7 введите формулу Продано*Цена в руб: =D3*B3.
| 1 доллар | 25,60р. | |||
| Автозапчасть | Продано | Цена в $ | Цена в руб. | Сумма |
| Масляный фильтр | 12,00 | 307,20р. | 7 987,20р. | |
| Сальник | 6,00 | 153,60р. | 2 304,00р. | |
| Прокладка поддона | 21,00 | 537,60р. | 5 913,60р. | |
| Прокладка крышки клапанного механизма | 12,00 | 307,20р. | 13 824,00р. | |
| Ремень ГРМ | 36,00 | 921,60р. | 1 843,20р. |
Подсчитайте итоговую сумму при помощи команды Автосумма 
.
Упражнение 4. Применение формул и построение графиков
Система электронных таблиц EXCEL содержит большой набор всевозможных формул: математических, финансовых, статистических, временных и т.д. Для построения графиков воспользуемся формулами из Мастера функций.
На новом листе в ячейку А1 введите заголовок «Построение графика функции y(x)=sin(2x)*cos(x/3». Для значений переменных x выберем последовательность
0 (0,15) 14,55 (Читается так: от 0 до 14,55 сшагом 0,15)
Заполнение таблицы. В ячейку А3 введите число 0, в ячейку А4 введите число 0,15, выделите ячейки А3, А4 и размножьте их до 14,55.
В ячейку В3 введем формулу с помощью мастера функций. На панели инструментов найдите кнопку Вставка функции 
и нажмите ее. Появится диалог, в котором выберите категорию функций Математические. В поле Функция найдите SIN. Переходите на второй шаг.
Диалог второго шага. За пустое место окна диалога зацепите левой кнопкой мыши и перенесите окно в удобное место, чтобы оно не закрывало нужные ячейки. После этого в поле диалога Число надо установить значение переменной функции sin, напишите: 2*, затем левой кнопкой мыши щелкните в ячейку А3 и в поле появится надпись: 2*А3. Теперь в строке формул написана формула вида: =SIN(2*A3).
Аналогично в ячейку С3 напишите формулу: =COS(A3/3), в ячейку D3: =B3*C3
Размножьте ячейки столбцов В, С и D до конца таблицы.
Построение графика. Выделите ячейки с D3 по D100. Нажмите кнопку Вставка диаграммы. На первом шаге выберите Тип: график. Нажмите кнопку Далее.
На втором шаге на вкладке Ряд можно задать подписи оси X, для этого установите курсор в поле подписи оси X и выделите диапазон ячеек на листе, соответствующий этим значениям, в данном примере это ячейки с А3 по А27, в поле добавится запись =Лист4!$A$3:$A$100, нажмите кнопку Далее.
На третьем шаге в поле Название диаграммы введите «Построение графика», в поле Ось Х (категорий) – Х, Ось Y (значений) – Y(Х). Переходите на последний шаг.
Диаграмму надо построить на текущем листе.
Упражнение 5. Статистические и логические функции
Функции счета предназначены для подсчета числа ячеек, удовлетворяющих определенным условиям.
Функция СЧЁТпозволяет подсчитать количество ячеек в диапазоне, содержащих числа. В качестве аргументов перечисляются диапазоны, ячейки которых будут подсчитываться.
=СЧЁТ(Значение1;Значение2;...)
Подсчитываются ячейки с числами, а также ячейки с формулами и функциями, результатом которых является число.
Функция СЧЁТЗпозволяет подсчитать количество непустых ячеек в диапазоне, т.е. ячеек, в которых содержатся какие-нибудь данные. В качестве аргументов перечисляются диапазоны, ячейки которых будут подсчитываться.
=СЧЁТЗ(Значение1;Значение2;...)
Функция СЧЁТЕСЛИпозволяет подсчитать количество ячеек в диапазоне, данные в которых удовлетворяют заданному условию, например, количество ячеек, числа в которых больше двух, или количество ячеек, которые содержат определённую запись. Аргументами функции являются диапазон ячеек, значения которых будут сравниваться со значением в условии и условие на значение ячеек. Условие обязательно начинается со знака сравнения (> – больше, < – меньше, = – равно, <> – неравно, <= – меньше или равно, >= – больше или равно). При вводе функции вручную условие записывается в кавычках. Текст в условии набирается без кавычек.
=СЧЁТЕСЛИ(диапазон;”условие”)
Функция СУММЕСЛИ позволяет подсчитать сумму значений ячеек в диапазоне, данные в которых удовлетворяют заданному условию, например, сумму значений ячеек, числа в которых меньше десяти, или сумму значений ячеек, которые содержат определённую запись. Аргументами функции являются диапазон ячеек, значения которых будут сравниваться со значением в условии, условие на значение ячеек и диапазон суммируемых значений. Функция относится к категории математических функций.
=СУММЕСЛИ(диапазон;”условие”; диапазон_суммирования).
Постройте в Excel электронную таблицу, как указано ниже (табл. 1)
Введите числа месяца с 1-го по 31-е. Внесите фамилии сотрудников в список (не менее 10 фамилий). Введите название текущего месяца в заголовок табеля. Выделите цветом столбцы, соответствующие нерабочим дням недели.
Проставьте для каждого сотрудника:
количество часов, отработанных за день, или
о, если он находился в отпуске
б, если в этот день сотрудник болеет
п, если прогуливает
Таблица 1
Для подсчета дней явок необходимо подсчитать количество ячеек, содержащих числа. Для этого воспользуемся формулой СЧЕТ. В ячейку AJ3 введите формулу
= СЧЁТ(Е3:AI3)
Для подсчета количества дней, проведенных в отпуске, воспользуйтесь формулой СЧЕТЕСЛИ. В качестве критерия введите букву о.В ячейку AK3 введите формулу
=СЧЁТЕСЛИ(Е3:AI3;"о")
Аналогично рассчитывается количества дней по болезни и прогулы.
Для общего количества отработанных часов воспользуйтесь функцией СУММ.
Самостоятельно
определите количество сотрудников, у которых оклад не превышает 4500 р.
посчитайте сумму окладов сотрудников, фамилии которых начинаются на “С
Наиболее употребляемые логические функции И, ИЛИ, ЕСЛИ находятся в категории Логические Мастера функций.
Логические функции, как видно из их названия, позволяют установить правильность высказывания при наличии различных условий и принять нужное решение.
Функция И принимает два значения ИСТИНАи ЛОЖЬ.Значение ИСТИНА она принимает тогда, когда верны все входящие в нее логические выражения, и значение ЛОЖЬ, когда хотя бы одно из логических выражений не верно.
Запись функции И
=И(логическое1;логическое2;...)
Функция И может содержать до 30 логических выражений.
Функция ИЛИпринимает два значения ИСТИНАи ЛОЖЬ.Значение ИСТИНА она принимает, когда верно хотя бы одно входящее в нее логическое выражение; значение ЛОЖЬ – когда все логические выражения не верны.
Запись функции ИЛИ
=ИЛИ(логическое1; логическое2;...)
Функция ИЛИ может содержать до 30 логических выражений.
Функция ЕСЛИсодержит логическое выражениеи принимает два значения: Значение_если_истинаи Значение_если_ложь.Эти значения задает сам пользователь. Функция принимает Значение_если_истина, если логическое выражение истинно, и Значение_если_ложь, если логическое выражение ложно. Вид функции ЕСЛИ:
=ЕСЛИ(Логическое выражение; Значение_если_истина; Значение_если_ложь)
Значение_если_истина и Значение_если_ложь могут быть числом, текстом в кавычках (" "), арифметическими выражениями.
Нахождение решения квадратного уравнения.
В создании формул использовались темы: присвоение имен ячейкам, абсолютная адресация ячеек, логические функции.
Обратите внимание, что ячейкам A4, A5, A6 и E4 заданы имена a, b, c и D соответственно.
Упражнение 6. Функции поиска и связи таблиц
При сложных расчетах данные могут размещаться в двух и более таблицах. Например, первая таблица – основная, где проводятся расчеты, а во второй таблице располагаются справочные данные. При этом необходимо, чтобы автоматически осуществлялась связь таблиц, автоматический поиск данных в справочной таблице и их использование в таблице расчетов (основной).
Удобно располагать каждую таблицу на отдельном листе книги-файла. Можно дать свои имена листам с помощью меню Формат - Лист - Переименование. Прямое связывание таблиц осуществляется путем ссылки на ячейку таблицы, например: Таблица2!А3:D3 -обращение к блоку ячеек А3:D3таблицы на листе с именемТаблица2.
Для поиска данных в справочной таблице и передачи их в основную таблицу используется функция ПРОСМОТР,которая“работает” с двумя векторами (т.е. столбцами или строками таблицы). Она просматривает вектор, находит указанное значение, переходит в соответствующую позицию второго вектора и возвращает значение оттуда. Синтаксис функции: