Как посчитать дельту в excel

Обновлено: 07.07.2024

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

Поскольку отделов немного (три), для наглядности разместим таблицу с расчетом в диапазоне ячеек G1:H4 того же рабочего листа, на котором располагается спи-сок (рис. 5.2).

Рис. 5.2. Таблица для определения количества сотрудников в отделах и количества сотрудников, занимающих определенные должности

В ячейки Н2:Н4 мы должны поместить формулы, с помощью которых будут производиться вычисления. Чтобы определить общее количество сотрудников в отделе, необходимо подсчитать, сколько ячеек с названием данного отдела имеется в диапазоне А2:А11 (то есть сколько раз упоминается в списке название отдела, столько в нем и сотрудников).

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

Рис. 5.3. Панель функции "СЧЕТЕСЛИ"

Название отдела можно внести непосредственно во второй аргумент функции "СЧЕТЕСЛИ", как показано на рис. 5.3. Но значительно проще вместо текста поместить туда ссылку на ячейку G2, в которой таковой находится. Для заполнения ячеек Н2:Н4 формулами, выполните следующие действия:

1. Выделите диапазон ячеек Н2:Н4.
2. Вызовите посредством мастера функций панель функции "СЧЕТЕСЛИ".
3. Поместите курсор в поле Диапазон и мышью выделите на рабочем листе ячейки А2:А11.
4. Нажмите функциональную клавишу [F4] для создания абсолютной ссылки на диапазон ячеек.
5. Перейдите в поле Условие и щелкните мышью на ячейке G2.
6. Нажмите комбинацию клавиш [Ctrl+Enter].

В результате этих действий в ячейке Н2 будет создана формула

Подобные формулы появятся также в ячейках НЗ и Н4, но во втором их аргументе будут указаны соответственно адреса ячеек G3 и G4.

Excel. Функции СЧЕТЕСЛИ и СУММЕСЛИ

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

Упражнение 1. «Магазин»

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

Комментарии:

I. Создайте две таблицы: с исходными данными и результатами.

1. В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
2. Заполните таблицу исходными данными, например:

3. В таблице с результатами должны быть поля: «Наименование», «Количество поставок», «Суммарная стоимость», «Средняя цена».
4. Во второй таблице в столбце «Наименование» названия молочных продуктов из первой таблицы должны встречаться один раз, например:

II. Введите формулы в таблицы.

1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке

2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:

а) Для определения количества поставок нужно определить, сколько раз за день в магазин завозили, например, молоко. Для этого нужно использовать функцию СЧЁТЕСЛИ, которая определяет количество данных в диапазоне, равных критерию. Примерный вид формулы в ячейке С15:

где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:

где F2:F11 — диапазон стоимости продукта из первой таблицы.

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:

где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.

Замечания:

Данное упражнение может быть дополнено следующими заданиями (и не только ими):

1. Определить количество (в литрах) каждого продукта, завезенного в магазин.
2. Составить таблицу «Поставщики», в которой определить, на какую сумму каждый поставщик завез в магазин продукции, общий вес привезенной каждым поставщиком продукции, и сколько видов продуктов привез каждый из поставщиков.

Упражнение 2. «Студенческие стипендии» [1]

Составить таблицу экзаменационных оценок студентов и на ее основе определить средний балл и размер стипендии для каждого студента. Правила определения размера стипендии:

Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:

Упражнение 3. «Завод железобетонных изделий»

Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.

I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.

1. Первая таблица должна содержать поля: «Марка», «Длина», «Ширина», «Высота», «Удельный вес», «Вес блока».
a) Заполните таблицу, кроме столбца «Вес блока» (не менее 10 марок).
2. Заведите отдельную ячейку для значения грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка», «Количество блоков», «Количество вагонов».
a) В столбце «Марка» повторите названия нескольких марок блоков из первой таблицы (не менее 4).
b) Столбец «Количество блоков» заполните произвольными данными.

1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны.
2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:
a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.
b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ.
3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.

Упражнение 4. «Прайс-лист»

Компания, выпускающая косметику, выдает распространителям прайс-лист, в котором указано название продукта и его цена для распространителя и для клиента. Создать «электронный калькулятор» распространителя, с помощью которого он сможет определить, какая сумма ему потребуется, чтобы выкупить заказанную покупателем продукцию у компании, сколько ему должен заплатить клиент, и прибыль, которую он получит в результате продажи.

Функция ДЕЛЬТА в Excel используется для проверки двух числовых значений и возвращает два возможных варианта:

Числовое значение 1, если переданные в качестве первого и второго аргумента числа совпадают по значению.
0 (нуль), если переданные числа по значению не совпадают.

Примеры использования функции ДЕЛЬТА в Excel

Пример 1. В таблице Excel указаны значения независимой координаты X и значения зависимых координат Y1 и Y2 для двух разных функций. Определить количество пар совпадающих значений.

Вид исходной таблицы данных:

Для решения задачи сравним каждую пару значений зависимых координат Y1 и Y2 с помощью функции ДЕЛЬТА. В ячейке D3 запишем следующую формулу:

B3 – значение аргумента Y1 – сравниваемое число;
C3 – значение аргумента Y2 – число, с которым сравнивается первое значение.

Полученное значение (1) свидетельствует о равенстве сравниваемых чисел. Протянем записанную в ячейке D3 формулу вниз до ячейки D12, чтобы автоматически рассчитать результаты для остальных значений Y1 и Y2:

Для расчета числа пар совпадающих значений используем следующую функцию:

Всего 4 пары совпадений в значениях функций координат.

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

Вид формы для расчета:

Поскольку для расчета используется логарифм из частного ожидаемой и начальной суммы с основанием в виде суммы депозита, выраженного в процентах (то есть 100%), и процентной ставки, то каждая ячейка из диапазона B2:B4 не должна являться пустой или принимать значение 0, поскольку логарифм нуля или логарифм с основанием 1 (100%) не может быть вычислен. Поэтому для расчетов используем следующую формулу:

В качестве проверочного выражения функции ЕСЛИ выступает значение, являющееся суммой возвращаемых результатов функциями ДЕЛЬТА. Последние сравнивают значения, содержащиеся в ячейках B2, B3 и B4 соответственно с 0 (второй аргумент явно не указан). Данный вариант записи соответствует логическому И (общий результат равен ИСТИНА, если все выражения, проверяемые функцией И, возвращают результат ИСТИНА). Полученное значение (0 или любое число от 1 до +∞) будет интерпретировано как ЛОЖЬ или ИСТИНА соответственно.

При незаполненных полях получим следующий результат:

Напишем формулу для преобразования дробного числа в годы и месяцы:

В результате получим:

Расчет вероятности выпуска бракованной продукции на производстве в Excel

Пример 3. При изготовлении деталей на производственно-ремонтном заводе существует вероятность брака производимой продукции, которая равная коэффициенту 0,001. Определить, какая вероятность появления 10 выбракованных деталей (в этом случае вся партия считается браком) в партии из 35000 шт. Считается, что вероятностями ниже 10% можно пренебречь (то есть их приравнивают к нулю).

Вид исходной таблицы данных:

Для расчета используем следующую формулу:

Функция ОКРУГЛВНИЗ округляет до 0 все значения, которые меньше числа 0,01, то есть 10%-й вероятности. Функция ДЕЛЬТА принимает только один аргумент (выражение, полученное на основе формулы Пуассона для вычисления маловероятных случайных ситуаций), то есть сравнение выполняется с числом 0 (нуль).

Если вычислять формулу поэтапно, будет видно, что вероятность по формуле Пуассона равна примерно 0,00974 < 0,01, поэтому результат округления равен 0. Тогда функция ДЕЛЬТА возвращает значение 1 (0=0), которое интерпретируется как логическое ИСТИНА.

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

Как вставить дельта-символ в Excel?

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

Вы можете скачать этот шаблон Excel для Delta Symbol здесь - шаблон Excel для Delta Symbol

Дельта Символ из меню Вставка

Выберите ячейку, в которую вы хотите вставить дельта-меню. Перейти в меню вставки. Выберите символы .

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

Если в списке отображается символ дельты, выберите его и нажмите кнопку вставки. Или измените раскрывающийся список «Подмножество», выберите «Греческий и коптский».

Это будет список символов, где вы можете увидеть дельта-символ. Выберите символ и нажмите кнопку вставки.

Символ дельты, похожий на треугольник, будет вставлен. Вы можете изменить внешний вид, используя другой доступный шрифт. Попробуем вставить дельта-символ черного цвета. Чтобы получить заполненную центром дельту, измените «Шрифт» на «Arial». И выберите Подмножество «Геометрическая форма».

Сочетание клавиш для вставки символа дельты в Excel

Если дельта-символ постоянно используется в листе Excel, выполнение вышеуказанного процесса занимает много времени. Здесь вы можете использовать сочетание клавиш для символа дельты. Удерживая клавишу Alt, нажмите клавишу 30 на цифровой клавиатуре. Это вставит дельта-символ черного цвета в выбранную область.

Это еще один способ вставить дельта-символ в лист. Чтобы вставить пустую дельту, выберите целевой тип ячейки «D».

Выберите главное меню, затем измените тип шрифта на «Символ». Буква будет изменена на дельта-символ.

Вставка дельты с помощью формулы

Можно вставить дельта-символ, используя функцию CHAR. Специальные символы и символы вставляются с использованием этой функции.

Откройте функцию char в ячейке, в которую вы хотите вставить дельта-символ.

Введите номер символа в функцию char и нажмите enter.

Вы получите соответствующий символ «r» для номера символа, предоставленного функции char.

Чтобы получить заполненный дельта-символ, измените номер символа с 112 на 112.

Вставка символа Delta с помощью автозамены

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

Скопируйте символ дельты из электронной таблицы, затем нажмите Файл >>>> Параметры

Вы получите новое окно для установки слова автозамены. Появится список ранее установленных автокоррекций. Будут показаны два текстовых поля, в текстовом поле «С:» вставьте символ дельты.

Откройте лист и введите слово, которое вы установили для автокоррекции. Здесь мы устанавливаем слово как «Del». Поэтому введите слово в ячейку, в которую вы хотите вставить символ дельты.

Нажмите пробел после ввода «Del». Символ дельты будет вставлен в ячейку, где введено слово. Точно так же можно установить пустой символ дельты.

Дельта-вставка с использованием пользовательского форматирования

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

Таблица дана с другим названием продукта и его ППМ. Сумма после продажи со скидкой также упоминается.

Найдите сумму проданной разницы и ППМ, найдя разницу между двумя числами

Это вставит дельта-символ с суммами.

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

Это изменит символ дельты двумя способами.

Чтобы получить это в двух разных цветах, используйте формат, чтобы упомянуть цвета для двух дельта-символов.

В таблице будут представлены цифры с разными цветами

То, что нужно запомнить

Используются два типа дельта-символов. Заполненный дельта-символ и пустой дельта-символ.
Функция Char может вставлять символы на лист, используя его номера символов.
Автокоррекция является лучшим вариантом, если дельта-символ используется постоянно.
При использовании Alt + 30 в качестве ярлыка используйте числовую клавиатуру, чтобы получить число 30.

Подсчет ЧИСЕЛ попадающих в интервал в EXCEL

history 19 апреля 2013 г.

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

В качестве примера подсчета чисел возьмем список с числовыми значениями от 4 до 30 (См. файл примера ).

Будем подсчитывать значения, попадающие в интервал, например, (4;15]. Причем, границы интервала «включает [ ]» и «не включает ( )» будем выбирать из Выпадающего (раскрывающегося) списка .

Примечание : решение без выбора интервалов = СЧЁТЕСЛИМН(A2:A12;">"&D2;A2:A12;" Предполагается, что границы интервала введены в ячейки D2 и F2 . Эти ячейки не должны быть пустыми, даже если одна из границ =0. Если в диапазоне A2:A12 содержатся числовые значения в текстовом формате , то они будут проигнорированы.

Для настройки границ интервала используем Проверку данных с типом данных Список . В качестве источника укажем для левой границы >;>= и для правой СЧЁТЕСЛИМН(A2:A12;C2&D2;A2:A12;E2&F2)

Читайте также: