Ранг(число; ссылка; порядок)
- число – число, для которого определяется ранг;
- ссылка – массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются;
- порядок – число, определяющее способ упорядочения. Если значение параметра порядок равно 0 или он опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если значение параметра порядок является любым ненулевым числом, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.
Функция РАНГ присваивает одинаковым числам одинаковый ранг.
Функция ЧАСТОТА возвращает распределение частот в виде вертикального массива. Для данного множества значений и данного множества карманов (т.е. интервалов в математическом смысле) частотное распределение подсчитывает, сколько исходных значений попадает в каждый интервал.
Синтаксис:
ЧАСТОТА (массив_данных; двоичный_массив)
- массив_данных – массив или ссылка на множество данных, для которых вычисляются частоты; если массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей;
- двоичный_массив – массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных. Если двоичный_массив не содержит значений, то функция ЧАСТОТА возвращает количество элементов в аргументе двоичный_массив.
Вычисление значений с использованием условия
При составлении отчетной ведомости часто приходится пользоваться функциями СЧЁТЕСЛИ, СУММЕСЛИ и ПРОСМОТР, которые позволяют анализировать данные с применением условий.
Функция СЧЁТЕСЛИ возвращает количество непустых ячеек внутри интервала, удовлетворяющих заданному критерию.
Синтаксис:
СЧЁТЕСЛИ (диапазон; условие)
- диапазон – интервал, в котором нужно подсчитать ячейки;
- условие – условие в форме числа, выражения или текста, которое определяет, какие ячейки надо подсчитывать; например, критерий может быть выражен следующим образом: 17, “17”, “>17”, “Компьютер”.
Рассмотрим пример. Пусть ячейки диапазона А1:А4 содержат следующие значения: яблоки, апельсины, персики, яблоки. Тогда =СЧЁТЕСЛИ(А1:А4;”яблоки”) возвращает 2.
Пусть ячейки В1:В4 содержат 32, 54, 75 и 86 соответственно. Тогда =СЧЁТЕСЛИ(В1:В4; ”>50”) возвращает 3.
Функция СУММЕСЛИ суммирует ячейки, заданные критерием.
Синтаксис:
СУММЕСЛИ(диапазон;условие; диапазон_суммирования)
- диапазон – это интервал вычисляемых ячеек;
- условие – это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Например, критерий может быть выражен как 32, “32”, “>32”, “яблоки”;
- диапазон_суммирования – это фактические ячейки для суммирования.
Ячейки в аргументе диапазон_суммирования складываются, если соответствующие им ячейки в аргументе диапазон удовлетворяют условию. Если диапазон_суммирования опущен, то в расчетах участвуют ячейки в аргументе диапазон.
ФункцияПРОСМОТР возвращает значение из строки, из столбца или из массива. Функция ПРОСМОТР имеет две синтаксические формы: вектор и массив. Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор) в поисках определенного значения и возвращает значение из другого столбца или строки. Форма массива функции ПРОСМОТР просматривает первую строку или первый столбец массива, находит указанное значение и возвращает значение из аналогичной позиции последней строки или столбца массива.
Векторная форма
ПРОСМОТР(искомое_значение; просматриваемый_вектор; вектор_результатов)
Искомое_значение — это значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.
Просматриваемый_вектор — интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями.
Вектор_результатов — интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор.
Замечания.
· Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение.
· Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.
Примеры использования функции ПРОСМОТР.
| A | B | |
| Периодичность | Цвет | |
| 4,14 | красный | |
| 4,19 | оранжевый | |
| 5,00 | желтый | |
| 5,77 | зеленый | |
| 6,39 | синий | |
| Формула | Описание (результат) | |
| =ПРОСМОТР(4,19;A2:A6;B2:B6) | Поиск 4,19 в столбце A и возвращение значения из столбца B, находящегося в той же строке (оранжевый) | |
| =ПРОСМОТР(5,00;A2:A6;B2:B6) | Поиск 5,00 в столбце A и возвращение значения из столбца B, находящегося в той же строке (желтый) | |
| =ПРОСМОТР(7,66;A2:A6;B2:B6) | Поиск 7,66 в столбце A, соотнесение со следующим наименьшим значением (6,39) и возвращение значения из столбца B, находящегося в той же строке (синий) | |
| =ПРОСМОТР(0;A2:A6;B2:B6) | Поиск 0 в столбце A и возвращение сообщения об ошибке, т. к. ноль меньше наименьшего значения в просматриваемом векторе A2:A7 (#Н/Д) |
Пусть, например, на рабочем листе имеются данные фирмы по работе с недвижимостью о стоимости квартир и о комиссионных при их продаже (табл. 9.3).
Таблица 9.3 – Стоимость квартир и комиссионных
| Ячейка | Значение (стоимость квартиры) | Ячейка | Значение (комиссионные от продажи) |
| А1 | В1 | ||
| А2 | В2 | ||
| А3 | В3 | ||
| А4 | В4 |
Тогда следующая формула возвращает сумму комиссионных от продажи квартир, стоимость которых больше чем 150 000:
=СУММЕСЛИ (А1:А4;”>150000”;B1:B4) (возвращает 63000).
Продемонстрируем использование функций СЧЁТЕСЛИ и
СУММЕСЛИ на примере создания отчетной ведомости, в которой необходимо проследить все заказы, размещенные коммивояжерами фирмы, торгующей самоварами, и подсчитать объемы продаж по каждому из них.
Итак, в вашем распоряжении имеется рабочая книга, состоящая из двух рабочих листов (рис. 9.9).
- лист Заказы, где приведен список осуществленных продаж за текущий день каждым из коммивояжеров вашей фирмы;
- лист Коммивояжер со списком всех коммивояжеров вашей фирмы.
Рис. 9.9. Стоимость и число заказов, выполненных каждым из коммивояжеров
Для проведения расчетов введите на рабочем листе Коммивояжеры представленные ниже формулы.
| Ячейка | Формула | Описание |
| С2 | =СЧЁТЕСЛИ(Заказы!$A$2:$A$10;A2) | Возвращает число заказов, выполненных Ивановым. Выберите ячейку С2 и протяните маркер заполнения вниз на диапазон С3:С4, для определения числа заказов, выполненных каждым из коммивояжеров |
| D2 | =СУММЕСЛИ(Заказы!$A$2:$A$10;A2; Заказы!$B$2:$B$10) | Возвращает стоимость заказов, выполненных Ивановым. Выберите ячейку D2 и протяните заполнения на диапазон D3:D4 для определения стоимости заказов, выполненных каждым из коммивояжеров |
Пример отчетной ведомости по расчету затрат на производство
Предположим, что вы являетесь бухгалтером фирмы, производящей компакт-диски. Составим отчетную ведомость по расчету затрат на производство компакт-дисков (рис. 9.10). Упаковка диска фирме обходится в 1 руб./шт., стоимость материалов – 4 руб./шт. Фирма продает готовые диски по цене 10 руб./шт. Технические возможности позволяют выпускать до 5000 дисков в день. Оплата труда рабочих является сдельной и зависит от количества выпущенных дисков по правилу, представленному в табл. 9.4.
Таблица 9.4 – Правила оплаты труда
| Выпущено дисков, шт. | Оплата труда, руб./шт. |
| За первую тысячу | 0,3 |
| За вторую тысячу | 0,4 |
| За третью тысячу | 0,5 |
| За четвертую тысячу | 0,6 |
| За превышение над четвертой тысячей | 0,7 |
Предположим, что поступил заказ на изготовление 4500 компакт-дисков. Необходимо посчитать суммарные издержки и прибыль после выполнения данного заказа.
Рис. 9.10. Расчет затрат на производство товара
Для упрощения чтения формул при помощи команды Вставка – Имя – Присвоить можно установить имена ячейкам и диапазонам ячеек, перечисленные ниже:
| Диапазон | Имя |
| В1 | ЗаказШт |
| В2 | ПродажнаяЦена |
| В3 | СтоимостьУп |
| В4 | СтоимостьМат |
| В9 | ОбщиеИздержки |
| D2:D6 | ДискиШт |
| E2:E6 | ОплатаРубШт |
| F2:F6 | ОплатаРуб |
Зарплата рабочих в зависимости от объема выпущенных дисков находится в диапазоне F2:F7 по формуле массива (не забудьте завершить ее ввод нажатием комбинации клавиш <Ctrl>+<Shift>+<Enter>).
=ЕСЛИ(ЗаказШт-1000>ДискиШт;1000*ОплатаРубШт;
ЕСЛИ(ЗаказШт>ДискиШт;(ЗаказШт-ДискиШт)*ОплатаРубШт;0))
Кроме того, для завершения расчетов введите в ячейки формулы:
| Ячейка | Формула | Описание |
| В6 | =ЗаказШт*СтоимостьМат | Стоимость упаковки |
| В7 | =ЗаказШт*СтоимостьМат | Стоимость материалов |
| В8 | =СУММ(ОплатаРуб) | Суммарная зарплата |
| В9 | =СУММ(В6:В8) | Суммарные издержки |
| В10 | =ЗаказШт*ПродажнаяЦена-ОбщиеИздержки | Суммарная прибыль |
СОРТИРОВКА ДАННЫХ
Сортировкой называется упорядочение элементов таблицы по возрастанию или убыванию. Например, последовательности (а)-(г) упорядочены по возрастанию, а последовательности (д)-(з) – по убыванию:
а) 1, 2, 3, 4;
б) 25.45, 31.33, 44.55, 44.56, 44.57;
в) А, Б, В, Г, Д;
г) Арбузов, Борисов, Владимиров, Глебов, Данченко;
д) 4, 3, 2, 1;
е) 44.57, 44.56, 44.55, 31.33, 25.45;
ж) Д, Г, В, Б, А;
з) Данченко, Глебов, Владимиров, Борисов, Арбузов.
Excel позволяет выполнять сортировку данных, хранящихся в таблицах. Можно выделить два основных режима сортировки – одноуровневый и многоуровневый. В первом случае данные упорядочиваются по указанному пользователем признаку, например, по номеру зачетной книжки студентов. Во втором случае, упорядочение идет сразу по нескольким ключам, например, сначала по первому, затем по второму.
Например, таблица 10.1 содержит варианты упорядочения списка по трем ключам: по фамилиям, именам, отчествам.
Таблица 10.1– Пример сортировки по трем ключам
| Фамилия | Имя | Отчество |
| Борисов | Александр | Алексеевич |
| Борисов | Александр | Сергеевич |
| Толстой | Алексей | Константинович |
| Толстой | Алексей | Николаевич |
| Толстой | Алексей | Федорович |
| Толстой | Лев | Львович |
| Толстой | Лев | Николаевич |
| Толстой | Федор | Константинович |
| Толстой | Федор | Степанович |
| Уфимцев | Вячеслав | Иванович |
| Яковлев | Андрей | Петрович |
Общая схема сортировки
1. Выделить таблицу.
2. Выполнить команду Данные – Сортировка.
На экране появится окно (рис. 10.1).
Рис. 10.1.Пример окна сортировки
3. Указать:
Ø в области 1 – как идентифицировать поля:
(а) используя подписи, то есть первая строка таблицы рассматривается как ее "шапка";
(б) по номерам (обозначениям) столбца, то есть "Столбец А", "Столбец В" и т.п.;
Ø в области 2 – первый ключ сортировки (например, "Столбец А" (см. рис. 8.1) или "Фамилия" (см. табл. 8.1);
Ø в области 3 – правило упорядочения информации в первом столбце (по возрастанию или по убыванию);
если необходимо, то:
Ø в области 4 – второй ключ сортировки (например, "Столбец В" (см. рис. 1.1) или "Имя" (см. табл. 8.1);
Ø в области 5 – правило упорядочения информации во втором столбце (по возрастанию или по убыванию);
Ø в области 6 – третий ключ сортировки (например, "Столбец С" (см. рис. 8.1) или "Отчество" (см. табл. 8.1);
Ø в области 7 – правило упорядочения информации в третьем столбце (по возрастанию или по убыванию)
и щелкнуть по кнопке ОК.
Замечание.Имейте в виду, что при сортировке взаимосвязанных строк, необходимо выделять всю таблицу. Иначе информация после сортировки не будет соответствовать истинной.