Содержание

Введение

Лабораторная работа №1

Лабораторная работа №2

Лабораторная работа №3

Литература

Введение

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

Методические указания содержат 3 лабораторные работы по Excel, соответствующие теме 4: "Табличные процессоры" программы учебной дисциплины "Компьютерная подготовка". В результате выполнения лабораторных работ учащиеся овладевают основными навыками по построению и обработке таблиц в Excel, осваивают работу со списками, учатся использовать встроенные функции.

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

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

Лабораторная работа №1

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

Задание 1. Создать таблицу вида:

п/н	ФИО	Дата рождения	январь				Июнь
			З/п	Налог	Взносы	К выдаче	З/п	Налог	Взносы	К выдаче
Итого:

Зарплата за каждый месяц вводится. Налог рассчитывается как 12% от зарплаты; взносы – 1% от зарплаты; "к выдаче": зарплата-налог-взносы.

Порядок работы.

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

В первой строке идут названия столбцов таблицы. В ячейку А1 вводим "Номер", в ячейку В2 – "ФИО", а "Дата рождения" введем в две ячейки:С1 и С2.Далее идут названия месяцев 1-го полугодия, каждый из которых является шапкой для подтаблицы "з/п" (D2), "налог" (E2), "взносы" (F2), "к выдаче" (G2). Для того, чтобы "шапка" получилась, надо сделать активными сразу 4 ячейки, т.е. выделить диапазон: при нажатой кнопке мыши протянуть указатель от первой ячейки до последней данного диапазона. Затем выполнить Формат – Ячейки – Выравнивание – Объединение ячеек. Здесь же добавить выравнивание по центру и выбрать полужирный шрифт. И только после этого набрать название месяца. Далее мы видим, что в таблице названия месяцев идут так, как и в календаре. Поэтому можно воспользоваться одной из замечательных возможностей Excel – автозаполнением. Опять выделяем подряд 4 ячейки с уже записанным словом "январь". Устанавливаем курсор в правый нижний угол (он приобретает вид черного крестика – это маркер заполнения), нажав на левую клавишу мыши протягиваем его до ячейки АА. Получаем таблицу с заполненными названиями месяцев с января по июнь.

Теперь вводим соответствующие названия столбцов: "з/п" (D2), "налог" (E2), "взносы" (F2), "к выдаче" (G2) и поступаем аналогично названиям месяцев: выделяем подряд 4-ре ячейки, устанавливаем курсор в правый нижний угол ячейки G2, нажав на левую клавишу мыши протягиваем указатель мыши до ячейки АА2. Таким образом, мы скопировали названия столбцов. Осталось заполнить таблицу конкретными значениями.

Рис. 1.

После того, как основные данные внесены в таблицу, надо произвести некоторые расчеты. Например, налог должен быть рассчитан в данной таблице как 12 % от зарплаты. Поэтому, например, в ячейку Е3 вводим формулу, для этого набираем знак "=". Все формулы в Excel подчиняются одному общему правилу: начинается формула со знака равенства, который как раз и указывает Excel, что это формула. За знаком равенства следуют операнды – это могут быть значения, имена функций, ссылки на ячейки, диапазоны, имена диапазонов. Операнды разделяются одним или несколькими операторами – символами, которые комбинируют операнды или управляют ими, например , знаки (+) или больше (>). Таким образом, чтобы посчитать налог в ячейку Е3 вводим =D3*0,12. Ячейка, содержащая формулу, называется зависимой ячейкой: ее значение зависит от значения другой, влияющей, ячейки. Если изменить ячейку, на которую ссылается формула, эта формула может вернуть другое значение. Например, формула =D3*0,12 умножает значение ячейки D3 на число 0,12.При каждом изменении значения ячейки D3 значение формулы будет пересчитываться.

Далее заполняем ячейку F3. Так как "взносы" составляют 1% от зарплаты, то в ячейку F3 вводим =D3*0,01. Формула для значения "к выдаче" не должна составить у вас труда. Для того чтобы не набирать адрес ячейки, можно просто щелкнуть на нужной ячейке мышкой и тогда адрес ячейки сам появится в строке формул.

Задание 2. Добавить столбец, в котором рассчитать суммарную зарплату за полгода и для каждого столбца: "Зарплата", "Налог", "Взносы", "К выдаче" заполнить поле "Итого".

Порядок работы.

В столбце АВ в первой строке делаем запись "суммарная зарплата". В ячейку АВ3 заносим формулу =D3+H3+L3+P3+T3+X3. Тем самым, мы нашли суммарную зарплату за полгода для первого человека из нашего списка. Для остальных людей из списка надо скопировать данную формулу, т.к. она будет аналогичной.

Чтобы заполнить поле "Итого" воспользуемся функцией Автосумма. При выделении диапазона ячеек (в одной строке или столбце) и нажатии на панели инструментов кнопки Автосумма [∑] в пустую ячейку, следующую за диапазоном, будет вставлена формула подсчета суммы этих ячеек. Если кнопка нажата, но диапазон не выделен, то Excel предполагает, что автосумму нужно вставить в текущую ячейку листа и на основании "своих" умозаключений предлагает один из имеющихся на листе диапазонов, выделив его тонкой "живой" рамкой. Для текущей ячейки предлагается формула подсчета суммы значений ячеек, расположенных в строке слева или в столбце выше данной ячейки. Если Excel не угадал и пользователь имел в виду другой диапазон, он может изменить диапазон по своему усмотрению. После нажатия <Enter> Excel окончательно вставляет в текущую ячейку формулу суммирования выбранных ячеек.

Задание 3. Разделить таблицу на две части (горизонтально и вертикально по очереди). Отменить разделение.

Порядок работы.

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

В зависимости от требуемого типа разделения листа применяются два способа разделения:

– использовать горизонтальную или вертикальную разделительную кнопку (вешку разбивки) для расщепления листа на две панели в требуемой позиции;

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

Рис. 2.

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

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

Чтобы разделить лист с помощью вешек разбивки, нужно:

Спозиционировать курсор мыши на требуемой вешке. Курсор примет вид двунаправленной стрелки.

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

Протянуть указатель в требуемую позицию разделения.

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

Чтобы отменить расщепление, нужно перенести вешку (линию разделения) в ее исходную позицию или выбрать команду Снять разделение в меню Окно.

Задание 4. Закрепить номера и фамилии, чтобы не сдвигались при прокрутке.

Порядок работы.

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

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

– при перемещении вверх и вниз по таблице нижние панели синхронизированы;

– при перемещении влево и вправо по таблице правые панели синхронизированы;

– левая верхняя панель всегда постоянна.

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

Задание 5. Рассортировать по фамилиям (по алфавиту).

Порядок работы.

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

Итак, выделяем таблицу, начиная с ячейки В3 и заканчивая АС12 следующим образом:

– указываем левую верхнюю ячейку диапазона;

– нажимаем клавишу F8 (Excel включит режим расширения, в строке состояния появится индикация ВДЛ;

– щелкаем нижнюю правую ячейку диапазона АС12.

Excel выделит весь диапазон. Режим расширения продолжает действовать до его отключения повторным нажатием клавиши F8.

Выбираем в меню Данные – Сортировка. В окне Сортировка диапазона выбираем нужный столбец и указываем, как сортировать: по возрастанию или убыванию.

Задание 6. Скрыть столбцы с января по июнь, оставив итоги за полгода.

Порядок работы.

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

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

– в меню Формат выбрать Столбец – Скрыть.

– Нажать<Ctrl + 0(ноль)>.

– Щелкнуть правой кнопкой на заголовке столбца и затем выбрать Скрыть в контекстном меню.

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

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

– в меню Формат выбрать команду Столбец – Отобразить;

–Щелкнуть правой кнопкой мыши на выделении и выбрать Отобразить в контекстном меню.

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

Порядок работы.

Сначала выделяем столбцы с фамилиями и датами рождений. Щелкаем правой клавишей мыши и в контекстном меню выбираем Копировать. Затем щелкаем на ярлычке нужного нам (например, следующего) листа. Устанавливаем курсор на ячейку, начиная с которой мы хотим поместить копируемую информацию. Опять щелкаем правой клавишей мыши и в контекстном меню выбираем Вставить. С данными об итоговой зарплате будем действовать сначала подобным образом: выделяем, копируем, переходим на другой лист. Осталось только вставить скопированные данные. Для этого в контекстном меню выбираем Специальная вставка и выбираем кнопку Значения, т.к. копируемые данные вычисляются по формулам, привязанным к своему листу.

Рис. 3.

Задание 8. Удалить и вставить несколько строк.

Порядок работы.

Чтобы вставить в электронную таблицу целую строку или столбец, нужно проделать следующие шаги:

– в области предполагаемой вставки выделить столько столбцов или строк, сколько предполагается вставить;

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

Для удаления строк или столбцов:

– выделить строки или столбцы для удаления;

– в меню Правка выбрать команду Удалить.

Excel удаляет строки или столбцы и соответственно сдвигает оставшиеся данные.

Задание 9. Занести в ячейку на 1-м листе курс доллара и подсчитать итоговую зарплату каждого человека в долларах, используя ячейку с курсом. Присвоить имя ячейке. Расположить зарплату в долларах в отдельном столбце.

Порядок работы.

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

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

– не рекомендуется применять в именах символы операторов(+,-,*,/,<,>,&), т.к. это может внести путаницу в формулах;

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

Процедура создания имени диапазона включает следующие шаги:

Выделить диапазон для именования.

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

ввести имя диапазона в поле Имя.

Нажать кнопку Добавить. Excel добавит имя в список имен.

Нажать кнопку Закрыть для возврата в экран листа.

Назначенное имя диапазона будет доступно в любом листе книги.

Присвоим имя курс_доллара ячейке С15. Введем значение курса и отформатируем его Формат – ячейки – денежный – английский (США). Рядом со столбцом Суммарная зарплата за полгода (АВ) в ячейку АС3 введем формулу =АВ3/курс_доллара.

Распространим эту формулу для всех членов списка. Таким образом, мы подсчитали

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

Задание 10. Изменить название листа.

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

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

Как объединить ячейки?

Как ввести формулу в ячейку?

Как разделить таблицу на 4-ре части?

Как выполнить сортировку данных одного столбца по убыванию?

Лабораторная работа №2

Цель работы: получить практические навыки по использованию функций ЕСЛИ(), СЧЕТЕСЛИ(), СУММЕСЛИ(), МАКС(), МИН(), СРЗНАЧ(), И(). Ознакомится с работой формул массивов.

Задание 1. Сформировать таблицу вида:

Сведения о сотрудниках предприятия:

ФИО	Должность	Год.доход	Подоходный налог

Подоходный налог рассчитывается так:

А) Если годовой доход <=12 МРОТ, то 0% (МРОТ – минимальный размер оплаты труда. Его записать в отдельную ячейку вне таблицы – 100 руб.);

Б) Если12*МРОТ< годовой доход<=20000, то 12%;

В) Если 20000<годовой доход<50000, то подоходный налог равен 2400+20% от суммы, превышающей 20000;

Г) Если годовой доход >50000, то подоходный налог =4000+45% от суммы, превышающей 50000.

Порядок работы.

Введем произвольные фамилии (не менее 10). Должности будем выбирать из списка: секретарь, экономист, юрист, инженер, лаборант. Колонку таблицы "Годовой доход" заполним произвольным образом, но с учетом должности. Для заполнения колонки "Подоходный налог" нам придется использовать функцию ЕСЛИ().

В Microsoft Excel определено большое количество стандартных формул, именуемых функциями. Функции используются для различной сложности вычислений и текстовых операций. Функции выполняют вычисления по входным данным (задаваемым величинам) – аргументам – в указанном порядке, в соответствии с правилами синтаксиса. Список аргументов может состоять из чисел, текста, логических величин, массивов, ссылок. Кроме того, аргументы могут быть как константами, так и формулами. Эти формулы в свою очередь, могут содержать другие функции (до 7 уровней вложения). При вводе значений аргументов необходимо следить за соответствием типов аргументов.

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

Общий синтаксис функции следующий:

=Имя_функции(аргумент 1; аргумент 2;…)

Синтаксис функции ЕСЛИ:

=ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)

Функция ЕСЛИ() проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.

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

ЕСЛИ(С3>50000;(С3-50000)*0,45+4000;

ЕСЛИ(С3>20000;(С3-20000)*0,2+2400;

ЕСЛИ(С3>12*МРОТ; С3*0,12; 0)))

Здесь МРОТ – поименованная ячейка, содержащая минимальный размер оплаты труда (см. условие задания).

После правильного ввода функции ЕСЛИ() в ячейку D3 остается размножить эту формулу для оставшихся членов списка (см. рис. 4).

Рис. 4.

Задание 2. На 2-ом листе рассчитать таблицу на основе таблицы задания 1.

А	В	С	D
Должность	Количество	Средний доход	Максимальный доход
Секретарь
Экономист
Юрист
Инженер
Лаборант

Порядок работы.

Для подсчета количества человек, занимающих определенную должность, нам понадобится функция СЧЕТЕСЛИ(). Эта функция подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию.

Синтаксис функции следующий:

СЧЕТЕСЛИ(диапазон; критерий)

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

=СЧЕТЕСЛИ(Лист1!В3:В15; "секретарь").

Обратите внимание на тот факт, что диапазон В3:В15 содержит явное указание на то, что он находится на первом листе.

Далее нам необходимо подсчитать "Средний доход" для каждой должности. Здесь нам понадобятся формулы массива.

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

Формула массива создается так же, как и простая формула, но с нюансами:

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

– ввести формулу;

– ввести комбинацию клавиш <CTRL + SHIFT + ENTER>. Excel заключит формулу массива в фигурные скобки.

Для значения Excel может понадобиться выполнить несколько действий. Например, следующая формула вычисляет среднее значение только тех ячеек, принадлежащих диапазону С3:С15 на 1-ом листе для которых в ячейках диапазона В3:В15 содержится слово "секретарь". Для этого используется функция ЕСЛИ, которая в диапазоне В3:В15 находит ячейки, содержащие слово "секретарь", и возвращает значения ячеек в диапазоне С3:С15 только из этих строк функции СРЗНАЧ, которая затем уже вычисляет их среднее значение (см. рис. 5). В данном задании наша формула для секретарей будет выглядеть следующим образом:

{=СРЗНАЧ(ЕСЛИ(Лист1!В3:В15="секретарь"; Лист1!С3:С15))}

Аналогично Среднему доходу производится расчет в колонке Максимальный доход., только вместо функции СРЗНАЧ надо воспользоваться функцией МАКС:

{=МАКС(ЕСЛИ(Лист1!В3:В15="секретарь"; Лист1!С3:С15))}

Здесь опять пришлось воспользоваться формулой массива.

Другой способ получения среднего дохода для каждой должности – использовать функции СУММЕСЛИ() и СЧЕТЕСЛИ(), поделив их результаты.

Обращение к функции СУММЕСЛИ имеет вид:

СУММЕСЛИ (диапазон1; критерий; диапазон_суммирования)

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

=СУММЕСЛИ(ЛИСТ1!В3:В15;"секретарь"; Лист1! С3:С15)/

СЧЕТЕСЛИ(ЛИСТ1!В3:В15;"секретарь").

В этом примере в качестве критерия мы указали константу: "секретарь". Решение будет более гибким, если в качестве критерия мы укажем адрес ячейки второго листа, содержащего соответствующую должность, а именно, В2. Тогда можно будет скопировать эту формулу в ячейки, соответствующие другим должностям (С3 –С6). Для того, чтобы копирование проходило корректно (т.е. не изменялись адреса диапазонов) нужно в адресации диапазонов указать абсолютные адреса. В ячейку С2 листа 2 надо записать формулу:

= СУММЕСЛИ(ЛИСТ1!$В$3:$В$15; В2; Лист1! $С$3:$С$15)/

СЧЕТЕСЛИ(ЛИСТ1!$В$3:$В$15; В2).

Копируем эту формулу в ячейки С3 – С6 и получаем средний доход по указанным должностям.

Задание 3. Определить количество инженеров, чей годовой доход превышает 20000.

Порядок работы.

Данное задание выполним в два этапа. Сначала на первом листе добавим столбец, в котором определим инженеров с годовым доходом превышающим 20000. Определим – это значит, поставим 1, если инженер удовлетворяет нашему условию, а всем остальным: инженерам с годовым доходом не превышающим 20000 или не инженерам с годовым доходом превышающим или не превышающим 20000 поставим 0. Делать это мы будем с помощью функции ЕСЛИ:

ЕСЛИ(И($В$3:$В$15="инженер";$С$3:$С$15>20000);1;0).

Рис. 5.

При записи условия функции ЕСЛИ нам понадобилась логическая функция И(логическое значение1; логическое значение2; …) – логическое умножение. Эта функция возвращает значение "истина", если все аргументы имеют значение "истина" и возвращает значение "ложь", если хотя бы один аргумент имеет значение "ложь".

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

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

Воспользуемся функцией СЧЕТЕСЛИ(), считая, что необходимая нам информация находится в ячейках Е3 – Е15:

СЧЕТЕСЛИ(Е3:Е15;"=1").

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

Этой цели отвечает функция ПОИСК(). Она осуществляет поиск одного текста внутри другого и возвращает номер позиции, где этот текст найден. Обращение к этой функции имеет вид:

ПОИСК(искомый текст; текст для поиска; начальная позиция)

Недостатком этой функции является то, что при отрицательном результате поиска она возвращает код ошибки #ЗНАЧ! Чтобы освободиться от кода ошибки, который не воспринимается другими функциями, надо использовать функцию ЕОШ или ЕОШИБКА.

Функция ЕОШИБКА(значение) проверяет, является ли значение кодом ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ИМЯ?, #ПУСТО!) и возвращает значение "истина", если значение является кодом ошибки и "ложь" – в противном случае.

Таким образом, функция

ЕОШИБКА (ПОИСК(искомый текст; текст для поиска; начальная позиция))

Будет давать значение ложь, если исходный текст найден, истину – если не найден.

Соответственно, функция отрицания "НЕ", взятая от функции ЕОШИБКА, будет возвращать истину и ложь на противоположных значениях аргументов, т.е.

НЕ( ЕОШИБКА (ПОИСК ("инженер"; В3;1)))

возвращает значение "истина", если в тексте значения ячейки В3 присутствует слово "инженер". Если в ячейке В3 слова "инженер" нет, то возвращается "ложь".

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

Таким образом, функция ЕСЛИ, помещаемая в ячейку Е3, примет вид:

ЕСЛИ(И(НЕ(ЕОШИБКА(ПОИСК "инженер"; В3; 1))); С3>20000); 1; 0).

Скопируем эту формулу в ячейки Е4:Е15. получим столбец, состоящий из 0 и 1. Теперь остается только подсчитать количество единиц или, что то же самое, подсчитать сумму содержимого ячеек этого диапазона:

СУММ(Е3:Е15).

Таким образом, получим количество специалистов, должность которых содержит слово "инженер", которые имеют годовой доход >20000.

Задание 4. Определить, насколько самый большой доход превышает самый маленький (по формуле).

Порядок работы.

В табличном процессоре Excel содержится большой выбор различных функций. Есть функции, которые позволяют определить наибольшее число из заданных (функция МАКС()) и наименьшее число (функция МИН()). Поэтому формула следующая:

МАКС(С3:С15)-МИН(С3:С15).

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

Каков синтаксис функции ЕСЛИ()?

Для чего нужны формулы массивов?

Как выглядит адрес ячейки, расположенной на другом листе?

В чем различие абсолютной и относительной адресации?

Что вычисляет функция СЧЕТЕСЛИ()?

Лабораторная работа №3

Цель работы: научиться работать с таблицами в Ехсеl , как с базами данных.

Задание 1. Создать базу данных с записями следующей структуры:

Отдел	ФИО	Должность	Возраст	Пол	З/П 1 кв	З/П 2 кв	З/П 3 кв	З/П 4 кв	Год.З/П

В этой базе данных заданы зарплаты по кварталам Годовая З/П – вычисляемое поле – сумма зарплат по кварталам. Заполнить базу данных записями (более 10 штук), в котором повторяются 2 отдела и 3-4 должности.

Порядок работы.

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

В нашей таблице все данные вводятся произвольно, кроме последнего столбца – "Годовая зарплата". Здесь мы используем функцию СУММ(), для того чтобы сложить зарплату данного человека за все четыре квартала.

Задание 2. Просмотреть список с помощью формы.

Порядок выполнения.

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

Рис. 6.

Рис. 7.

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

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

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

Открыть окно формы.

Нажать кнопку Добавить. Excel создаст новую пустую запись – очистит поля редактирования и выведет на месте индикатора номера записи Новая запись.

Заполнить поля новой записи.

По окончании нажать кнопку Закрыть.

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

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

Открыть окно формы данных.

Найти запись для удаления.

Нажать кнопку Удалить. Excel предупреждает, что запись будет удалена окончательно.

Подтвердить удаление. Excel удалит запись.

Для возврата в экран листа нажать кнопку Закрыть.

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

Задание 4. С помощью формы вывести данные о сотрудниках старше 35 лет, у которых зарплата за 1 квартал <40000.

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

Для поиска записей нужно:

Открыть окно формы.

Нажать кнопку Критерии. Excel выводит пустые поля записи и заменяет индикатор номера записи словом Критерии.

Зайти в поле, по которому будет выполняться поиск, и ввести критерий. В нашем задании в поле "Возраст" установим критерий <35, а в поле "З/п 1 кв": <40000 (см. рис 8).

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

Задание 5. Используя функции для работы с базой данных и задавая критерии, определить:

средний возраст женщин и средний возраст мужчин;

количество пенсионеров по возрасту ( у мужчин – начиная с 60 лет, у женщин – с 55 лет);

среднюю зарплату секретарей в отделе КПО (за год);

количество мужчин в возрасте от 30 до 50 лет.

Рис. 8.

Порядок работы. Для списков в Excel существует набор функций, который носит название "функции баз данных" или Д-функции. Эти функции отличаются тем, что:

– подводят итоги для столбца таблицы;

– в результат включаются только те строки, которые удовлетворяют заданному критерию.

Общий синтаксис функций баз данных:

Д-функция(список, поле, критерий).

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

Пол

ж

С помощью мастера функций выбираем функции, относящиеся к разделу "Работа с базой данных" и затем функцию ДСРЗНАЧ(). Как было указано выше Д-функция имеет три аргумента. В качестве первого аргумента выбираем диапазон ячеек, формирующих список.

Рис. 9.

Вторым аргументом Поле задается заголовок столбца в двойных кавычках или число, представляющее номер столбца в списке. Третий аргумент – диапазон, содержащий задаваемые условия. Таким образом, Д-функция будет иметь следующий вид: ДСРЗНАЧ(A1:J11; D1; G21:G22). Аналогично надо поступить при вычислении среднего возраста мужчин, создав нужный критерий. Подсчитаем количество пенсионеров среди мужчин (пункт b)). Для этого нам понадобится критерий следующего вида:

Возраст	Пол
>60	м

В качестве Д-функции выбираем функцию БСЧЕТ() со следующими параметрами: БСЧЕТ(A1:J11; D1; B13:C14) (см. рис. 10).

Рис.10.

В пункте c) требуется подсчитать зарплату секретарей в отделе КПО (за год). Здесь опять будет двойной критерий, но по полям Должность и Отдел. Чтобы подсчитать среднюю зарплату воспользуемся Д-функцией ДСРЗНАЧ(). В качестве второго параметра Д-функции выступает столбец J, в котором определена годовая зарплата сотрудника (см. рис.11).

Рис. 11.

Подсчитаем количество мужчин в возрасте от 30 до 50 лет (пункт d)). При создании критерия в данной задаче необходимо дважды использовать столбец Возраст, т.к. его значение должно быть >30 и <50. Поскольку определяется количество, то выбираем Д-функцию БСЧЕТ() (см. рис. 12).

Рис. 12.

Таким образом, мы привели несколько примеров работы с таблицей в Excel как с базой данных.

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

Что называют списком в Excel?

Как получить форму для таблицы?

Как называются функции для работы с базой данных?

Можно ли при задании критерия выбирать значения нескольких столбцов?

Какие Д-функции вы знаете?

Литература

Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: БХВ-Петербург, 2001. – 816 с.

Лавренов С.М. Сборник примеров и задач. – М.: Финансы и статистика, 2001. – 336 с.

Попов А.А. Excel: практическое руководство. – М.:ДЕСС КОМ, 2000. – 302 с.