Реферат: Совершенствование торгово-технологического процесса
Содержание
Введение
Лабораторная работа №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.
Если расщеплениеполучилось не там, где нужно, разбивку панелей можно отрегулировать мышью,перемещая разделительные линии. Чтобы двигать одновременно обе линии, нужноспозиционировать курсор на их пересечении.
С помощью мыши можноразделить экран на две или четыре панели. Для этого нужно нажать одну из вешекразбивки. Вешка горизонтальной разбивки расположена выше стрелки «вверх»вертикальной полосы прокрутки. Вешка вертикального расщепления находится справаот стрелки «вправо» горизонтальной полосы прокрутки.
Чтобы разделить лист спомощью вешек разбивки, нужно:
1. Спозиционироватькурсор мыши на требуемой вешке. Курсор примет вид двунаправленной стрелки.
2. Нажать иудерживать левую кнопку мыши. Появится серая полоска, указывающая текущуюпозицию разделения.
3. Протянутьуказатель в требуемую позицию разделения.
4. Отпустить кнопкумыши. 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 не делает различий между литерамиверхнего и нижнего регистров;
– не рекомендуетсяприменять в именах символы операторов(+,-,*,/,<,>,&), т.к. это можетвнести путаницу в формулах;
– для облегчения работы сименами следует делать их как можно короче (конечно, без ущерба для смысловойнагрузки).
Процедура создания именидиапазона включает следующие шаги:
1. Выделить диапазон для именования.
2. В меню Вставка в подменю Имя выбратькоманду Присвоить. Откроется окно диалога Присвоение имени .
3. ввести имя диапазона в поле Имя.
4. Нажать кнопку Добавить. Excel добавит имя в список имен.
5. Нажать кнопку Закрыть для возврата вэкран листа.
Назначенное имя диапазонабудет доступно в любом листе книги.
Присвоим имя курс_доллараячейке С15. Введем значение курса и отформатируем его Формат – ячейки –денежный – английский (США). Рядом со столбцом Суммарная зарплата за полгода(АВ) в ячейку АС3 введем формулу =АВ3/курс_доллара.
Распространим эту формулудля всех членов списка. Таким образом, мы подсчитали
в столбце АС итоговуюзарплату каждого человека в долларах, используя поименованную ячейку или можноуказать абсолютный адрес ячейки.
Задание 10. Изменитьназвание листа.
Порядок работы. Чтобыпереименовать лист, нужно щелкнуть на его ярлычке правой кнопкой мыши и выбратьв контекстном меню Переименовать. После этого в открывшемся окне можно ввестиновое имя листа.
Контрольные вопросы.
1. Как объединить ячейки?
2. Как ввести формулу в ячейку?
3. Как разделить таблицу на 4-ре части?
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).
Контрольные вопросы.
1. Каков синтаксис функции ЕСЛИ()?
2. Для чего нужны формулы массивов?
3. Как выглядит адрес ячейки,расположенной на другом листе?
4. В чем различие абсолютной иотносительной адресации?
5. Что вычисляет функция СЧЕТЕСЛИ()?
Лабораторная работа №3
Цель работы: научитьсяработать с таблицами в Ехсеl,как с базами данных.
Задание 1. Создать базуданных с записями следующей структуры:
Отдел ФИО Должность Возраст Пол З/П 1 кв З/П 2 кв З/П 3 кв З/П 4 кв Год.З/ПВ этой базе данных заданызарплаты по кварталам Годовая З/П – вычисляемое поле – сумма зарплат покварталам. Заполнить базу данных записями (более 10 штук), в которомповторяются 2 отдела и 3-4 должности.
Порядок работы.
Excel – более чем подходящий инструментдля работы с плоскими (табличного вида) файлами баз данных или списками. Список– набор строк, содержащий связанные данные. Список может использоваться какнебольшая база данных, в которой строки выступают в качестве записей, столбцыявляются полями, а ячейки – отдельные элементы данных. Первую строку списка приэтом Excel воспринимает в качестве заголовковстолбцов. Поэтому список начинаем создавать как обычную таблицу в Excel, учитывая, что между заголовкамистолбцов и конкретными данными не должно быть пробелов (см. рис. 6).
В нашей таблице вседанные вводятся произвольно, кроме последнего столбца – «Годовая зарплата».Здесь мы используем функцию СУММ(), для того чтобы сложить зарплату данногочеловека за все четыре квартала.
Задание 2. Просмотретьсписок с помощью формы.
Порядок выполнения.
Форма – это окно диалога,предназначенное для управления списком. Excel создает форму автоматически, анализируя структурусписка. Чтобы получить форму, надо выделить список как диапазон и выбрать вменю Данные команду Форма (см. рис. 7). В окне формы отображается только одназапись. С помощью кнопки Далее можно поочередно просмотреть все записи списка.С помощью формы можно редактировать все поля списка, за исключением вычисляемыхи защищенных полей. При создании формы данных Excel начинает с имен полей и добавляет текстовое поле длякаждого значения, доступного для редактирования.
/>
Рис. 6.
/>
Рис. 7.
Далее со статусом толькодля чтения включаются поля, которые содержат результаты расчетов по формулам –эти поля недоступны для редактирования. Полоса прокрутки и кнопки Назад и Далеепозволяют быстро перемещаться по списку. Индикатор номера записи в правомверхнем углу отражает номер текущей записи и общее число записей в списке.
Задание 3. Добавить,удалить несколько записей с помощью формы.
Порядок работы.Добавление и удаление записей с помощью формы – процедуры очень простые ибыстрые. Чтобы добавить запись надо выполнить следующую последовательностьдействий:
1. Открыть окно формы.
2. Нажать кнопку Добавить. Excel создаст новую пустую запись –очистит поля редактирования и выведет на месте индикатора номера записи Новаязапись.
3. Заполнить поля новой записи.
4. По окончании нажать кнопку Закрыть.
При создании новой записичерез форму Excel добавляет ее в конец списка.
Для удаления записи спомощью формы нужно выполнить следующие шаги:
1. Открыть окно формы данных.
2. Найти запись для удаления.
3. Нажать кнопку Удалить. Excel предупреждает, что запись будетудалена окончательно.
4. Подтвердить удаление. Excel удалит запись.
5. Для возврата в экран листа нажатькнопку Закрыть.
При удалении записи такимобразом Excel удаляет данные из строки и длязаполнения образовавшегося промежутка смещает нижние записи вверх
Задание 4. С помощьюформы вывести данные о сотрудниках старше 35 лет, у которых зарплата за 1квартал <40000.
Порядок работы. Найтизапись в списке можно, указав критерий, которому должна отвечать запись. Excel сравнивает каждую запись скритериями и показывает первую запись, которая отвечает этому критерию. В формеможно установить только простые критерии поиска. Критерий поиска составляется сиспользованием текста, чисел и операторов сравнения «равно» или «больше»(«меньше»).
Для поиска записей нужно:
1. Открыть окно формы.
2. Нажать кнопку Критерии. Excel выводит пустые поля записи изаменяет индикатор номера записи словом Критерии.
3. Зайти в поле, по которому будет выполнятьсяпоиск, и ввести критерий. В нашем задании в поле «Возраст» установимкритерий <35, а в поле «З/п 1 кв»: <40000 (см. рис 8).
Кнопки Далее и Назадиспользуются для перемещения по записям, отвечающим критерию, если этих записейнайдено несколько.
Задание 5. Используяфункции для работы с базой данных и задавая критерии, определить:
a) средний возраст женщин и среднийвозраст мужчин;
b) количествопенсионеров по возрасту ( у мужчин – начиная с 60 лет, у женщин – с 55 лет);
c) среднюю зарплату секретарей в отделеКПО (за год);
d) количество мужчин в возрасте от 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 как с базой данных.
Контрольные вопросы.
1. Что называют списком в Excel?
2. Как получить форму для таблицы?
3. Как называются функции для работы сбазой данных?
4. Можно ли при задании критериявыбирать значения нескольких столбцов?
5. Какие Д-функции вы знаете?
Литература
1. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: БХВ-Петербург, 2001. – 816 с.
2. Лавренов С.М. Сборникпримеров и задач. – М.: Финансы и статистика, 2001. – 336 с.
3. Попов А.А. Excel: практическое руководство. – М.: ДЕССКОМ, 2000. – 302 с.