Совершенствование торгово-технологического процесса (стр. 3 из 4)
Формула массива выполняет одновременно несколько вычислений и возвращает либо несколько значений, либо одно значение. Обычно формулы массива обрабатывают два и более наборов аргументов, называемых аргументами массива. Каждый аргумент массива должен иметь соответствующий номер строки и столбца. При этом все аргументы массива должны иметь одинаковое количество строк и столбцов. Чтобы формула возвращала несколько значений, она должна быть введена в несколько ячеек.
Формула массива создается так же, как и простая формула, но с нюансами:
– выделить ячейку или группу ячеек, в которых необходимо создать формулу массива;
– ввести формулу;
– ввести комбинацию клавиш <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 начинает с имен полей и добавляет текстовое поле для каждого значения, доступного для редактирования.