Эксель объем продаж с указанной даты
Статья представляет собой подборку заданий для проведения итоговых уроков по изучению 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,0 < средний балл < 3,5 — 1000р.
- 3,5 < средний балл < 4,0 — 1200р.
- 4,0 < средний балл < 4,5 — 1500р.
- 4,5 < средний балл < 5,0 — 1800р.
- средний балл = 5,0 — 2000р.
Определить общую сумму назначенных стипендий.
Замечания:
Формулировка данной задачи, с некоторыми изменениями, взята из учебника «Информатика: Практикум по технологии работы на компьютере» под ред. Н.В. Макаровой.
- Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:
- Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:
Упражнение 3. «Завод железобетонных изделий»
Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.
I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.
1. Первая таблица должна содержать поля: «Марка», «Длина», «Ширина», «Высота», «Удельный вес», «Вес блока».
a) Заполните таблицу, кроме столбца «Вес блока» (не менее 10 марок).
2. Заведите отдельную ячейку для значения грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка», «Количество блоков», «Количество вагонов».
a) В столбце «Марка» повторите названия нескольких марок блоков из первой таблицы (не менее 4).
b) Столбец «Количество блоков» заполните произвольными данными.
1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны.
2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:
a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.
b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ.
3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.
Упражнение 4. «Прайс-лист»
Компания, выпускающая косметику, выдает распространителям прайс-лист, в котором указано название продукта и его цена для распространителя и для клиента. Создать «электронный калькулятор» распространителя, с помощью которого он сможет определить, какая сумма ему потребуется, чтобы выкупить заказанную покупателем продукцию у компании, сколько ему должен заплатить клиент, и прибыль, которую он получит в результате продажи.
Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
При создании формул массива, операторы массива могут значительно увеличить время расчета. В этой короткой главе рассматриваются два примера, в которых обычная формула справляется с задачей значительно быстрее формулы массива.
Подсчет дат, когда критерий сформулирован в виде текста. На рис. 12.1 показан набор данных с датами в стандартном формате Excel, то есть в виде порядковых чисел. В тоже время, критерии заданы как число (год) и текст (месяц). Цель – подсчитать, сколько дат соответствуют критерию. Проблема в том, что у нас несоответствие формата данных: в столбце A даты как порядковые номера, а критерий – смесь чисел и текста. На рис. 12.1 приведено пять различных формул, которые можно использовать для достижения цели.
Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)
Давайте подробнее изучим работу этих пяти формул.
- Если вы можете позволить себе вспомогательный столбец, функция СЧЁТЕСЛИ будет самым простым решением.
- Функция МЕСЯЦ возвращает число между 1 и 12, а функция ГОД – число (год).
- Хотя Excel требует, чтобы аргумент функции МЕСЯЦ был представлен датой в числовом формате, этот аргумент может распознать и текст. Однако МЕСЯЦ(Окт) вернет ощибку, а вот если добавить к названию месяца любое число, например, 1, то Excel справится. Используйте, как в формуле выражение Окт1, заданное фрагментом F8&1, или 1Окт, заданное фрагментом 1&F8.
- Формулы с вспомогательными столбцами как правило работают быстрее.
- Если у вас Excel 2007 или более поздний, вы можете использовать функции СЧЁТЕСЛИМН и КОНМЕСЯЦА.
- Вам даны год (в виде числа) и месяц (как текст). Это означает, что вы можете вычислить дату начала и конца месяца, а затем определить даты, попажающие между ними.
- Месяц всегда начинается с первого числа, так что вы можете создать нижнюю границу диапазона конкатенацией: " >=1 " &F8&E8. Операции конкатенации возвращают текст, но это не страшно, т.к. функция СУММЕСЛИМН понимает даты в виде текста.
- Вы используете функцию КОНМЕСЯЦА с аргументом число_месяцев равным нулю; это позволяет получить последнюю дату текущего месяца. Функция КОНМЕСЯЦА является динамической: она возвращает 28 или 29 для февраля и 30 или 31 для любого другого месяца.
- Эта формула является самой быстрой, если вам нужно получить решение в одной ячейке.
- Если у вас Excel версии младше 2007 г., вы можете использовать две функции СЧЁТЕСЛИ, одну – для верхнего диапазона, вторую – для нижнего. Фокус в том, чтобы сначала сосчитать все значения, которые равны или меньше верхней границы, а затем вычесть все значения, которые меньше нижней границы.
- В Excel 2003 или более ранней, чтобы добавить функцию КОНМЕСЯЦА, вам нужно выбрать Инструменты → Надстройки → Анализ Данных.
- Эта формула работает быстрее, чем формулы [4] и [5].
- Функции МЕСЯЦ и ГОД возвращают числа, извлекая их из порядкового номера даты.
- Далее сравниваются два фрагмента, каждый полкченный конкатенацией.
- Функция ТЕКСТ используется для представления чисел в виде текста. Второй аргумент этой функции – формат – определяет, как будет представлено число. Вы может конвертировать весь столбец А в текст, состоящий из 7 символов: 3 буквы месяца и 4 цифры года.
Нахождение объема продаж за год. На рис. 12.4 показан пример несоответствие формата года в критерии Е6 (число) и формата дат в диапазоне А2:А6 (порядковый номер). Цель – найти сумму продаж за год. На рисунке представлены шесть вариантов формул, которые могут решить задачу. Обратите внимание, что в формулах [1] и [2] критерии начала и конца года жестко зашиты в коде, т.к. они не могут изменяться. Это 1/1 и 31/12). Формулы размещены на рисунке в порядка увеличения скорости работы.
Рис. 12.4. Формата года в критерии Е6 (число) не соответствует формату дат в диапазоне А2:А6 (порядковый номер)
Например, у нас есть список участников с их днями рождения, и сейчас мы готовимся сделать поздравительные открытки для этих участников. Перед тем, как сделать поздравительные открытки, мы должны посчитать, сколько дней рождения приходится на конкретный месяц / год / число. Здесь я проведу вас к Countif по дате / месяцу / году и диапазону дат с формулами в Excel следующими методами:
Счетчик по конкретному месяцу / году и диапазону дат с формулами в Excel
В этом разделе я представлю некоторые формулы для подсчета дней рождения по определенному месяцу, году или диапазону дат в Excel.
Countif к определенному месяцу
= СУММПРОИЗВ (1 * (МЕСЯЦ (C3: C16) = G2))
Ноты:
В приведенной выше формуле C3: C16 - это указанный столбец даты рождения, в котором вы будете считать дни рождения, а G2 - это ячейка с конкретным номером месяца.
Вы также можете применить формулы этого массива = СУММ (ЕСЛИ (МЕСЯЦ (B2: B15) = 8,1)) (нажмите клавиши Ctrl + Shift + Enter), чтобы считать дни рождения по конкретному месяцу.
Countif к определенному году
Если вам нужно подсчитать дни рождения к определенному году, говорит 1988, вы можете использовать одну из следующих формул по мере необходимости.
Примечание. Вторая формула - это формула массива. Не забудьте нажать кнопку Ctrl + Shift + Enter ключи вместе после ввода формул;
Countif к определенной дате
Countif по определенному диапазону дат
Если вам нужно посчитать, если позже / раньше определенной даты (например, 1990-1-1), вы можете применить следующие формулы:
 | Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем! Подробнее . Бесплатная пробная версия |
Легко считать по Финансовый год, полугодие, номер недели или день недели в Excel
Функция особой группировки по времени сводной таблицы, предоставляемая Kutools for Excel, может добавить вспомогательный столбец для расчета финансового года, полугодия, номера недели или дня недели на основе указанного столбца даты и позволяет легко подсчитывать, суммировать , или средние столбцы на основе вычисленных результатов в новой сводной таблице. Полнофункциональная бесплатная 30-дневная пробная версия!
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Счетчик по указанной дате, году или диапазону дат в Excel
Если у вас установлен Kutools for Excel, вы можете применить его Выбрать определенные ячейки утилита для простого подсчета количества вхождений по указанной дате, году или диапазону дат в Excel.
Kutools for Excel- Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 дней, кредитная карта не требуется! Get It Now
1. Выберите столбец дня рождения, в котором вы будете считать, и нажмите Kutools > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. В открывшемся диалоговом окне Select Specific Cells сделайте, как показано на скриншоте выше:
(1) В Тип выбора раздел, пожалуйста, отметьте нужный вариант. В нашем случае мы проверяем Ячейка вариант;
(2) В Конкретный тип выберите Больше или равно из первого раскрывающегося списка, а затем введите первую дату указанного диапазона дат в правом поле; затем выберите Меньше или равно из второго раскрывающегося списка и введите последнюю дату указанного диапазона дат в правом поле, а затем установите флажок Разумеется, вариант;
(3) Щелкните значок Ok кнопку.
3. Теперь появляется диалоговое окно, в котором показано, сколько строк было выбрано, как показано на скриншоте ниже. Пожалуйста, нажмите на OK кнопку, чтобы закрыть это диалоговое окно.
Ноты:
(1) Для подсчета количества вхождений в указанный год просто укажите диапазон дат от первой даты этого года до последней даты этого года, например, от 1/1/1990 в 12/31/1990.
(2) Для подсчета количества вхождений к указанной дате, например, 9 сентября 14 г., просто укажите настройки в диалоговом окне «Выбор конкретных ячеек», как показано ниже:
Демонстрация: счетчик по дате, дню недели, месяцу, году или диапазону дат в Excel
Статьи по теме:
Подсчитать, если ячейка не содержит текста в Excel
Например, у вас есть длинный список данных в Excel, знаете ли вы, как подсчитать количество ячеек, не содержащих определенной текстовой строки? В этой статье я представлю формулу для подсчета, если ячейки не содержат определенной текстовой строки в Excel.
Подсчитайте, если в ячейках есть дата / данные в Excel
Были ли у вас проблемы с подсчетом количества ячеек, содержащих любую дату или данные в определенном диапазоне в Excel? В этой статье я расскажу о способах подсчета того, содержат ли ячейки какую-либо дату / данные в Excel.
Подсчет ячеек, если они содержат X или Y в Excel
Функция СЧЁТЕСЛИ поможет нам подсчитать ячейки, содержащие один критерий, а функция СЧЁТЕСЛИ может помочь подсчитать ячейки, содержащие набор условий или критериев в Excel. Что делать, если подсчет ячеек содержит один из нескольких критериев? Здесь я поделюсь способами подсчета ячеек, если они содержат X, Y или Z… и т. Д. В Excel.
Отфильтрованные данные / список Countif в Excel
Вы можете заметить, что независимо от того, отфильтровали вы свою таблицу или нет, функция СЧЁТЕСЛИ игнорирует фильтрацию и возвращает фиксированное значение. В некоторых случаях вам необходимо подсчитывать отфильтрованные данные по определенным критериям, так как же это сделать? В этой статье я быстро расскажу о нескольких способах работы с отфильтрованными данными / списками Countif в Excel.
Довольно часто при работе датами в таблице Эксель возникает необходимость определения временного промежутка между ними, который измеряется в днях. В Excel существуют различные способы для выполнения данной задачи. Давайте рассмотрим их.
Расчет разности дат
Для корректной работы с датами сперва нужно задать ячейкам соответствующий формат. В противном случае возможны проблемы с корректным отображением дат, что сделает невозможным их дальнейшую обработку.
На самом деле Эксель способен самостоятельно определять формат ячейки после ввода в нее информации. Но этот механизм не всегда срабатывает как надо, поэтому стоит заранее самостоятельно настроить формат ячеек или проверить, правильно ли его определила программа.
- Для начала выделяем целевой диапазон ячеек, воспользовавшись зажатой левой кнопкой мыши.
- по умолчанию мы должны оказаться во вкладке “Число” (если это не так, переключаемся в нее);
- в списке “Числовые форматы” находим позицию “Дата” и кликаем на нее;
- в поле “Тип” выбираем нужный образец представления данных, в соответствии с которым они будут отображаться на листе;
- нажимаем OK.
Метод 1: использование формулы вычитания
Начнем, пожалуй, с самого простого способа – вычитания двух дат, представленного в виде обычной формулы.
- Вносим две даты, между которыми будем определять разницу в днях, в две разные ячейки отформатированного ранее диапазона.
- Теперь выделим ячейку, в которой планируем отобразить результат нашего вычисления.
ВАЖНО: Количество дней обозначается простым целым числом, следовательно, ячейка для его вывода должна иметь отличный от “Дата” формат, а именно – “Общий“. В противном случае полученный результат будет отображаться в формате даты, что не позволит оценить корректность проведенных вычислений. Для определения текущего формата ячейки достаточно взглянуть на соответствующее поле, расположенное в разделе инструментов “Число” на ленте вкладки “Главная“.
Метод 2: функция РАЗНДАТ
Следующий способ – это определение разницы между двумя датами при помощи функции РАЗНДАТ.
Функция имеет следующий синтаксис: =РАЗНДАТ(начальная_дата;конечная_дата;единица).
Если с двумя первыми аргументами все понятно – их суть соответствует названию, то на третьем (“единица“) стоит остановиться подробнее, так как именно он позволяет варьировать результат использования функции, выбирая единицу расчета разницы между датами:
- “d” — количество дней между датами;
- “m” — количество полных месяцев между датами;
- “y” — количество полных лет между датами;
- “YM” — разница между датами в месяцах;
- “MD” — разница между датами в днях, без учета месяцев и лет;
- “YD” — разница между датами в днях, без учета лет.
В нашем случае покажем пример использование аргумента “YD”, так как целью расчета является определение количества дней между датами в пределах года.
Основное отличие от простого вычитания одной даты из другой – в порядке следования аргументов функции: в качестве первого аргумента выбирается начальная (более ранняя дата), а в качестве второго – конечная (более поздняя). Опишем ниже алгоритм вычислений.
Метод 3: функция ЧИСТРАБДНИ для определения количества рабочих дней
Третий описываемый метод позволяет вычислить количество дней с дополнительным условием – исключением из него нерабочих (выходных и праздничных) дней.
В данном методе используется функция ЧИСТРАБДНИ со следующим синтаксисом: =ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники]).
Первые два аргумента аналогичны функции РАЗНДАТ. Последний аргумент является опциональным, то есть функция будет работать и без его указания. Однако именно он позволяет учесть в критериях вычисления нерабочие праздничные дни, так как по умолчанию из общего количества уберутся только субботы и воскресенья.
Приступаем к вычислениям:
Заключение
С помощью описанных выше методов можно достаточно легко посчитать количество дней между двумя датами, как с наличием дополнительных критериев, так и без них. Формула вычитания и функция РАЗНДАТ прекрасно подойдут для простого подсчета дней, а функция ЧИСТРАБДНИ поможет с расчетом количества именно рабочих дней между датами. Окончательный выбор зависит от поставленной перед пользователем задачи.
Читайте также: