Стол стул в excel

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

Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».

Примеры использования функции СЧЁТЕСЛИ в Excel

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

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

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

=СЧЁТЕСЛИ(C3:C17;»Samsung»)/A17

Описание аргументов:

• C3:C17 – диапазон ячеек, содержащих названия фирм проданной техники;
• «Samsung» – критерий поиска (точное совпадение);
• A17 – ячейка, хранящая номер последней продажи, соответствующий общему числу продаж.

Результат расчета:

Доля проданной продукции техники фирмы Samsung в процентах составляет – 40%.



Подсчет количества определенного значения ячейки в Excel при условии

Пример 2. По итогам сдачи экзаменов необходимо составить таблицу, в которой содержатся данные о количестве студентов, сдавших предмет на 5, 4, 3 балла соответственно, а также тех, кто не сдал предмет.

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

Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:

=СЧЁТЕСЛИ(B3:B19;D2:D5)

Описание аргументов:

• B3:B19 – диапазон ячеек с оценками за экзамен;
• D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.

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

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

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

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

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

Особенности использования функции СЧЁТЕСЛИ в Excel

Функция имеет следующую синтаксическую запись:

=СЧЕТЕСЛИ(диапазон; критерий)

Описание аргументов:

• диапазон – обязательный аргумент, принимающий ссылку на одну либо несколько ячеек, в которых требуется определить число совпадений с указанным критерием.
• критерий – условие, согласно которому выполняется расчет количества совпадений в рассматриваемом диапазоне. Условием могут являться логическое выражение, числовое значение, текстовая строка, значение типа Дата, ссылка на ячейку.

Примечания:

1. При подсчете числа вхождений в диапазон в соответствии с двумя различными условиями, диапазон ячеек можно рассматривать как множество, содержащее два и более непересекающихся подмножеств. Например, в таблице «Мебель» необходимо найти количество столов и стульев. Для вычислений используем выражение =СЧЁТЕСЛИ(B3:B200;»*стол*»)+СЧЁТЕСЛИ(B3:B200;»*стул*»).
2. Если в качестве критерия указана текстовая строка, следует учитывать, что регистр символов не имеет значения. Например, функция СЧЁТЕСЛИ(A1:A2;»Петров») вернет значение 2, если в ячейках A1 и A2 записаны строки «петров» и «Петров» соответственно.
3. Если в качестве аргумента критерий передана ссылка на пустую ячейку или пустая строка «», результат вычисления для любого диапазона ячеек будет числовое значение 0 (нуль).
4. Функция может быть использована в качестве формулы массива, если требуется выполнить расчет числа ячеек с данными, удовлетворяющим сразу нескольким критериям. Данная особенность будет рассмотрена в одном из примеров.
5. Рассматриваемая функция может быть использована для определения количества совпадений как по одному, так и сразу по нескольким критериям поиска. В последнем случае используют две и более функции СЧЁТЕСЛИ, возвращаемые результаты которых складывают или вычитают. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Для расчета количества ячеек с числами больше 3 и менее 8 необходимо выполнить следующие действия:

Скачать примеры функции СЧЁТЕСЛИ для подсчета ячеек в Excel

• записать первую функцию СЧЁТЕСЛИ с критерием «>3»;
• записать вторую функцию с критерием «>=8»;
• определить разницу между возвращаемыми значениями =СЧЁТЕСЛИ(A1:10;»>3″)-СЧЁТЕСЛИ(A1:A10;»>=8″). То есть, вычесть из множества (3;+∞) подмножество [8;+∞).

Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

В зависимости от расположения элементов различают массивы:

• одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
• двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

• горизонтальными (данные – в строке);
• вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

С помощью формул массива реально:

• подсчитать количество знаков в определенном диапазоне;
• суммировать только те числа, которые соответствуют заданному условию;
• суммировать все n-ные значения в определенном диапазоне.

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

Классика жанра – товарный чек

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

1. выделяем ячейку С7
2. вводим с клавиатуры =СУММ(
3. выделяем диапазон B2:B5
4. вводим знак умножения (звездочка)
5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:
   
6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле – отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно – они автоматически появляются при нажатии Ctrl + Shift + Enter.

Разрешите Вас… транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

• Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
• вводим функцию транспонирования =ТРАНСП(
• в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

Пример использования:

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

Пример использования:

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

• Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
• Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
• тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
  • 1 – значение по умолчанию, когда закреплены все индексы;
  • 2 – закрепление индекса строки;
  • 3 – закрепление индекса столбца;
  • 4 – адрес без закреплений.
• стиль_ссылки – необязательный аргумент. Логическое значение:
  • ИСТИНА – формат ссылок «A1»;
  • ЛОЖЬ – формат ссылок «R1C1».
• имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

Пример использования:

=АДРЕС=АДРЕС=АДРЕС=АДРЕС

Функция ДВССЫЛ

Контрольный пример

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

Определить,
сколько столов и шкафов фабрике следует
изготовлять, чтобы прибыль от их
реализации была максимальной.

Для
решения этой задачи необходимо построить
математическую модель. Процесс построения
модели можно
начать с ответа на следующие три вопроса:

1. Для
   определения каких величин строится
   модель?

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

3. Каким
   ограничениям должны удовлетворять
   неизвестные?

В
данном случае мебельной фабрике
необходимо спланировать объем
производства столов и шкафов так, чтобы
максимизировать прибыль. Поэтому
переменными являются: х1 — количество
столов, х2 — количество шкафов

Суммарная
прибыль от производства столов и шкафов
равна z=6*x1+8*x2.
Целью фабрики является определение
среди всех допустимых значений х1 и х2
таких, которые максимизируют суммарную
прибыль, т.е. целевую функцию z

Ограничения,
которые налагаются на х1 и х2:

• объем
  производства шкафов и столов не может
  быть
  отрицательным, следовательно: х1, х2 
  0.

• нормы
  затрат древесины на столы и шкафы не
  может превосходить максимально
  возможный запас данного исходного
  продукта, следовательно:

0.2×1+
0.1×2 40

0.1×1
+0.3×2 60

Кроме
того, ограничение на трудоемкость не
превышает количества затрачиваемых
ресурсов

1.2×1+
1.5х2 
371.4

Таким
образом, математическая модель данной
задачи имеет следующий вид:

Максимизировать

z
= 6х1 +
8х2

при
следующих ограничениях:

0.2×1+
0.1×2 40

0.1×1
+0.3×2 60

1.2×1+
1.5х2 
371.4

Данная
модель является линейной, т.к. целевая
функция и ограничения линейно зависят
от переменных.

Соседние файлы в папке зачет

• #
• #

Помогите пожалуйста

Мебельная фабрика выпускает столы, стулья, бюро и шкафы. При изготовлении этих товаров используется два типа досок. Нормы затрат досок каждого вида и трудовых ресурсов на единицу изделия, а также запасы ресурсов приведены в таблице.

Прибыль от реализации одного стола составляет 12 грн., стула – 5 грн., бюро – 15 грн., шкафа – 10 грн.
В ассортимент выпускаемых товаров должно входить не менее 40 столов, не менее 130 стульев, не менее 30 бюро, не более 10 шкафов.
Определить какого товара и сколько нужно выпускать, чтобы прибыль была максимальной.

Заранее большое спасибо

Количество в эксель

Как выполнить подсчёт ячеек со значением

Здравствуйте, дорогие читатели.

Перед началом данной темы я бы хотел вам посоветовать отличный обучающий продукт по теме экселя, по названием « Неизвестный Excel » , там всё качественно и понятно изложено. Рекомендую.

Ну а теперь вернёмся к теме.

Вы наверняка являетесь представителем одной из профессий, в которой не обойтись без программы эксель. Ведь она позволяет вести подсчеты, составлять списки, таблицы и диаграммы, заполнять ежедневники и решать многие другие задачи, связанные с цифрами.

Однако не все, кто работает с этим приложением, знает его полную функциональность и умеет ее применять на практике. Вы один из них? Тогда вы обратились по адресу. В частности, сегодня мы разберем, как в excel подсчитать количество ячеек со значением. Есть несколько способов, как это сделать. Они зависят от того, какое именно содержимое вам нужно посчитать. Разберем самые популярные из них.

Самый быстрый способ

Наиболее простым, но в то же время поверхностным, является способ подсчета элементов в строке состояния. Их численность выводится в самой нижней панели открытого окна.

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

Выберите нужный параметр и узнайте больше о том, что содержит ваша таблица.

Подсчет ячеек в строках и столбцах

Существует два способа, позволяющие узнать количество секций. Первый — дает возможность посчитать их по строкам в выделенном диапазоне. Для этого необходимо ввести формулу =ЧСТРОК(массив) в соответствующее поле. В данном случае будут подсчитаны все клетки, а не только те, в которых содержатся цифры или текст.

Второй вариант — =ЧИСЛСТОЛБ(массив) — работает по аналогии с предыдущей, но считает сумму секций в столбце.

Считаем числа и значения

Я расскажу вам о трех полезных вещах, помогающих в работе с программой.

1. Сколько чисел находится в массиве, можно рассчитать с помощью формулы СЧЁТ(значение1;значение2;…). Она учитывает только те элементы, которые включают в себя цифры.То есть если в некоторых из них будет прописан текст, они будут пропущены, в то время как даты и время берутся во внимание. В данной ситуации не обязательно задавать параметры по порядку: можно написать, к примеру, =СЧЁТ(А1:С3;В4:С7;…).
2. Другая статистическая функция — СЧЕТЗ — подсчитает вам непустые клетки в диапазоне, то есть те, которые содержат буквы, числа, даты, время и даже логические значения ЛОЖЬ и ИСТИНА.
3. Обратное действие выполняет формула, показывающая численность незаполненных секций — СЧИТАТЬПУСТОТЫ(массив). Она применяется только к непрерывным выделенным областям.

Ставим экселю условия

Когда нужно подсчитать элементы с определённым значением, то есть соответствующие какому-то формату, применяется функция СЧЁТЕСЛИ(массив;критерий). Чтобы вам было понятнее, следует разобраться в терминах.

Массивом называется диапазон элементов, среди которых ведется учет. Это может быть только прямоугольная непрерывная совокупность смежных клеток. Критерием считается как раз таки то условие, согласно которому выполняется отбор. Если оно содержит текст или цифры со знаками сравнения, мы его берем в кавычки. Когда условие приравнивается просто к числу, кавычки не нужны.

Разбираемся в критериях

• «>0» — считаются ячейки с числами от нуля и выше;
• «Товар» — подсчитываются секции, содержащие это слово;
• 15 — вы получаете сумму элементов с данной цифрой.

Для большей ясности приведу развернутый пример.

Чтобы посчитать ячейки в зоне от А1 до С2, величина которых больше прописанной в А5, в строке формул необходимо написать =СЧЕТЕСЛИ(А1:С2;«>»&А5).

Задачи на логику

Хотите задать экселю логические параметры? Воспользуйтесь групповыми символами * и ?. Первый будет обозначать любое количество произвольных символов, а второй — только один.

К примеру, вам нужно знать, сколько имеет электронная таблица клеток с буквой Т без учета регистра. Задаем комбинацию =СЧЕТЕСЛИ(А1:D6;«Т*»). Другой пример: хотите знать численность ячеек, содержащих только 3 символа (любых) в том же диапазоне. Тогда пишем =СЧЕТЕСЛИ(А1:D6;«. »).

Средние значения и множественные формулы

В качестве условия может быть задана даже формула. Желаете узнать, сколько у вас секций, содержимое которых превышают среднее в определенном диапазоне? Тогда вам следует записать в строке формул следующую комбинацию =СЧЕТЕСЛИ(А1:Е4;«>»&СРЗНАЧ(А1:Е4)).

Если вам нужно сосчитать количество заполненных ячеек по двум и более параметрам, воспользуйтесь функцией СЧЕТЕСЛИМН. К примеру, вы ищите секций с данными больше 10, но меньше 70. Вы пишете =СЧЕТЕСЛИМН(А1:Е4;«>10»;А1:Е4;«

© 2016 — 2019 “Блог Дмитрия Вассиярова”

Перепечатка, использование материалов с данного сайта, разрешена только по согласию с автором.

Как в Экселе посчитать количество ячеек

Посчитать количество ячеек в Эксель может потребоваться в различных случаях. В данной статье, мы и рассмотрим, как посчитать блоки с определенными значениями, пустые и если они попадают под заданные условия. Использовать для этого будем следующие функции: СЧЁТ, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СЧИТАТЬПУСТОТЫ.

Заполненных

Для начала, рассмотрим самый простой способ. Если Вам нужно посчитать количество заполненных блоков в Excel, выделите диапазон ячеек в документе – существуют различные способы, прочтите про них, перейдя по ссылке – и в «Строке состояния» обратите внимание на пункт «Количество» . Там будет написано количество непустых блоков: учитываются все с текстом и цифрами, а пустые – нет.

Если Вам нужно посчитать блоки в таблице, заполненные определенными значениями, и использовать это число в формулах для расчетов, то данный способ не подойдет, так как, данные в таблице могут периодически меняться. Поэтому перейдем к рассмотрению функций.

Где введены числа

Функция СЧЁТ – подсчитывает блоки, заполненные только числовыми значениями. Выделяем Н1 , ставим «=» , пишем функцию «СЧЁТ» . В качестве аргумента функции укажите нужный диапазон (F1:G10) . Если диапазонов несколько, разделите их «;» – (F1:G10;B3:C8) .

Всего заполнено 20 блоков. Тот, в котором записан текст, был не учтен, но посчитаны те, которые заполнены датой и временем.

С определенным текстом или значением

Функция СЧЁТЕСЛИ – позволяет рассчитать количество блоков, которые соответствуют заданному критерию. В качестве аргумента прописывается диапазон – В2:В13 , и через «;» указывается критерий – «>5» .

Например, есть таблица, в которой указано, сколько килограмм определенного товара было продано за день. Посчитаем, сколько товаров было продано весом больше 5 килограмм. Для этого нужно посчитать сколько блоков в столбце Вес, где значение больше пяти. Функция будет выглядеть следующим образом: =СЧЁТЕСЛИ(В2:В13;»>5″) . Она рассчитает количество блоков, содержимое в которых больше пяти.

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

Функция также может рассчитать:

– количество ячеек с отрицательными значениями: =СЧЁТЕСЛИ(В2:В13;» »&A10) ;
– ячейки, значение в которых больше 0: =СЧЁТЕСЛИ(В2:В13;»>0″) ;
– непустые блоки из выделенного диапазона: =СЧЁТЕСЛИ(В2:В13;»<>») .

Применять функцию СЧЁТЕСЛИ можно и для расчета ячеек в Excel, содержащих текст. Например, рассчитаем, сколько в таблице фруктов. Выделим область и в качестве критерия укажем «фрукт». Будут посчитаны все блоки, с данным словом. Можно не писать текст, а просто выделить прямоугольник, который его содержит, например С2 .

Для формулы СЧЁТЕСЛИ регистр не имеет значения, будут подсчитаны ячейки содержащие текст «Фрукт» и «фрукт».

В качестве критерия также можно использовать специальные символы: «*» и «?» . Они применяются только к тексту.

Посчитаем сколько товаров начинается на букву А: «А*» . Если указать «абрикос*» , то учтутся все товары, которые начинаются с «абрикос»: абрикосовый сок, абрикосовое варенье, абрикосовый пирог.

Символом «?» можно заменить любую букву в слове. Написав в критерии «ф?укт» – учтутся слова фрукт, фуукт, фыукт.

Чтобы посчитать слова в ячейках, которые состоят из определенного количества букв, поставьте знаки вопросов подряд. Для подсчета товаров, в названии которых 5 букв, поставим в качестве критерия «. » .

Если в качестве критерия поставить звездочку, из выбранного диапазона будут посчитаны все блоки, содержащие текст.

С несколькими критериями

Функция СЧЁТЕСЛИМН используется в том случае, когда требуется указать несколько условий, максимальное их количество в Excel – 126. В качестве аргумента: задаем первый диапазон значений, и указываем условие, через «;» задаем второй диапазон, и пишем условие для него – =СЧЁТЕСЛИМН(B2:B13;»>5″;C2:C13;»фрукт») .

В первом диапазоне мы указали, чтобы вес был больше пяти килограмм, во втором выбрали, чтоб это были фрукты.

Пустых блоков

Функция СЧИТАТЬПУСТОТЫ рассчитает количество пустых ячеек в заданном диапазоне.

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

Способы подсчета количества ячеек в диапазоне с данными

В Excel есть несколько функций, позволяющих подсчитать число пустых ячеек или ячеек с данными определенного типа в диапазоне.

Щелкните ячейку, в которой должен выводиться результат.

На вкладке Формулы щелкните Другие функции, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:

СЧЁТЗ: подсчитывает количество непустых ячеек.

СЧЁТ: подсчитывает количество ячеек, содержащих числа.

СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.

СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

Выделите диапазон ячеек и нажмите клавишу RETURN.

Щелкните ячейку, в которой должен выводиться результат.

На вкладке Формулы щелкните Вставить, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:

СЧЁТЗ: подсчитывает количество непустых ячеек.

СЧЁТ: подсчитывает количество ячеек, содержащих числа.

СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.

СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

Выделите диапазон ячеек и нажмите клавишу RETURN.

Подсчет количества значений в столбце в Microsoft Excel

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

Процедура подсчета значений в столбце

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

Способ 1: индикатор в строке состояния

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

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

В некоторых случаях индикатор количества значений может не высвечиваться в строке состояния. Это означает то, что он, скорее всего, отключен. Для его включения следует кликнуть правой кнопкой мыши по строке состояния. Появляется меню. В нем нужно установить галочку около пункта «Количество». После этого количество заполненных данными ячеек будет отображаться в строке состояния.

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

Способ 2: оператор СЧЁТЗ

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

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

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

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

Тем самым мы вызвали Мастер функций. Переходим в категорию «Статистические» и выделяем наименование «СЧЁТЗ». После этого производим щелчок по кнопке «OK» внизу данного окошка.

Мы переходим к окну аргументов функции СЧЁТЗ. В нём располагаются поля ввода аргументов. Как и количество аргументов, они могут достигать численности 255 единиц. Но для решения поставленной перед нами задачи хватит и одного поля «Значение1». Устанавливаем в него курсор и после этого с зажатой левой кнопкой мыши выделяем на листе тот столбец, значения в котором нужно подсчитать. После того, как координаты столбца отобразились в поле, жмем на кнопку «OK» в нижней части окна аргументов.

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

Способ 3: оператор СЧЁТ

С помощью оператора СЧЁТ можно произвести подсчет только числовых значений в выбранной колонке. Он игнорирует текстовые значения и не включает их в общий итог. Данная функция также относится к категории статистических операторов, как и предыдущая. Её задачей является подсчет ячеек в выделенном диапазоне, а в нашем случае в столбце, который содержит числовые значения. Синтаксис этой функции практически идентичен предыдущему оператору:

Как видим, аргументы у СЧЁТ и СЧЁТЗ абсолютно одинаковые и представляют собой ссылки на ячейки или диапазоны. Различие в синтаксисе заключается лишь в наименовании самого оператора.

Выделяем элемент на листе, куда будет выводиться результат. Нажимаем уже знакомую нам иконку «Вставить функцию».

После запуска Мастера функций опять перемещаемся в категорию «Статистические». Затем выделяем наименование «СЧЁТ» и щелкаем по кнопке «OK».

После того, как было запущено окно аргументов оператора СЧЁТ, следует в его поле внести запись. В этом окне, как и в окне предыдущей функции, тоже может быть представлено до 255 полей, но, как и в прошлый раз, нам понадобится всего одно из них под названием «Значение1». Вводим в это поле координаты столбца, над которым нам нужно выполнить операцию. Делаем это все тем же образом, каким выполняли данную процедуру для функции СЧЁТЗ: устанавливаем курсор в поле и выделяем колонку таблицы. После того, как адрес столбца был занесен в поле, жмем на кнопку «OK».

Способ 4: оператор СЧЁТЕСЛИ

В отличие от предыдущих способов, использование оператора СЧЁТЕСЛИ позволяет задавать условия, отвечающие значения, которые будут принимать участие в подсчете. Все остальные ячейки будут игнорироваться.

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

Аргумент «Диапазон» представляется в виде ссылки на конкретный массив ячеек, а в нашем случае на колонку.

Аргумент «Критерий» содержит заданное условие. Это может быть как точное числовое или текстовое значение, так и значение, заданное знаками «больше» (>), «меньше» ( ) и т.д.

Посчитаем, сколько ячеек с наименованием «Мясо» располагаются в первой колонке таблицы.

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

В Мастере функций совершаем переход в категорию «Статистические», выделяем название СЧЁТЕСЛИ и щелкаем по кнопке «OK».

Производится активация окошка аргументов функции СЧЁТЕСЛИ. Как видим, окно имеет два поля, которые соответствуют аргументам функции.

В поле «Диапазон» тем же способом, который мы уже не раз описывали выше, вводим координаты первого столбца таблицы.

В поле «Критерий» нам нужно задать условие подсчета. Вписываем туда слово «Мясо».

После того, как вышеуказанные настройки выполнены, жмем на кнопку «OK».

Давайте немного изменим задачу. Теперь посчитаем количество ячеек в этой же колонке, которые не содержат слово «Мясо».

Выделяем ячейку, куда будем выводить результат, и уже описанным ранее способом вызываем окно аргументов оператора СЧЁТЕСЛИ.

В поле «Диапазон» вводим координаты все того же первого столбца таблицы, который обрабатывали ранее.

В поле «Критерий» вводим следующее выражение:

То есть, данный критерий задает условие, что мы подсчитываем все заполненные данными элементы, которые не содержат слово «Мясо». Знак «<>» означает в Экселе «не равно».

После введения этих настроек в окне аргументов жмем на кнопку «OK».

Теперь давайте произведем в третьей колонке данной таблицы подсчет всех значений, которые больше числа 150.

Выделяем ячейку для вывода результата и производим переход в окно аргументов функции СЧЁТЕСЛИ.

В поле «Диапазон» вводим координаты третьего столбца нашей таблицы.

В поле «Критерий» записываем следующее условие:

Это означает, что программа будет подсчитывать только те элементы столбца, которые содержат числа, превышающие 150.

Далее, как всегда, жмем на кнопку «OK».

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel

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

Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».

Примеры использования функции СЧЁТЕСЛИ в Excel

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

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

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

• C3:C17 – диапазон ячеек, содержащих названия фирм проданной техники;
• «Samsung» – критерий поиска (точное совпадение);
• A17 – ячейка, хранящая номер последней продажи, соответствующий общему числу продаж.

Доля проданной продукции техники фирмы Samsung в процентах составляет – 40%.

Подсчет количества определенного значения ячейки в Excel при условии

Пример 2. По итогам сдачи экзаменов необходимо составить таблицу, в которой содержатся данные о количестве студентов, сдавших предмет на 5, 4, 3 балла соответственно, а также тех, кто не сдал предмет.

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

Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:

• B3:B19 – диапазон ячеек с оценками за экзамен;
• D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.

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

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

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

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

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

Особенности использования функции СЧЁТЕСЛИ в Excel

Функция имеет следующую синтаксическую запись:

=СЧЕТЕСЛИ( диапазон; критерий )

• диапазон – обязательный аргумент, принимающий ссылку на одну либо несколько ячеек, в которых требуется определить число совпадений с указанным критерием.
• критерий – условие, согласно которому выполняется расчет количества совпадений в рассматриваемом диапазоне. Условием могут являться логическое выражение, числовое значение, текстовая строка, значение типа Дата, ссылка на ячейку.

1. При подсчете числа вхождений в диапазон в соответствии с двумя различными условиями, диапазон ячеек можно рассматривать как множество, содержащее два и более непересекающихся подмножеств. Например, в таблице «Мебель» необходимо найти количество столов и стульев. Для вычислений используем выражение =СЧЁТЕСЛИ(B3:B200;»*стол*»)+СЧЁТЕСЛИ(B3:B200;»*стул*»).
2. Если в качестве критерия указана текстовая строка, следует учитывать, что регистр символов не имеет значения. Например, функция СЧЁТЕСЛИ(A1:A2;»Петров») вернет значение 2, если в ячейках A1 и A2 записаны строки «петров» и «Петров» соответственно.
3. Если в качестве аргумента критерий передана ссылка на пустую ячейку или пустая строка «», результат вычисления для любого диапазона ячеек будет числовое значение 0 (нуль).
4. Функция может быть использована в качестве формулы массива, если требуется выполнить расчет числа ячеек с данными, удовлетворяющим сразу нескольким критериям. Данная особенность будет рассмотрена в одном из примеров.
5. Рассматриваемая функция может быть использована для определения количества совпадений как по одному, так и сразу по нескольким критериям поиска. В последнем случае используют две и более функции СЧЁТЕСЛИ, возвращаемые результаты которых складывают или вычитают. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Для расчета количества ячеек с числами больше 3 и менее 8 необходимо выполнить следующие действия:

• записать первую функцию СЧЁТЕСЛИ с критерием «>3»;
• записать вторую функцию с критерием «>=8»;
• определить разницу между возвращаемыми значениями =СЧЁТЕСЛИ(A1:10;»>3″)-СЧЁТЕСЛИ(A1:A10;»>=8″). То есть, вычесть из множества (3;+∞) подмножество [8;+∞).

Разработка проекта «Решение оптимизационных задач средствами Excel»

Место темы в учебном курсе: уроки в 10-м классе

План работы над проектом

Урок 1. Знакомство с программой «Поиск решения» в Excel

Построить математическую модель задачи и решить её средствами Excel.

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

Определить, сколько столов и шкафов фабрике следует выпускать, чтобы прибыль от реализации была максимальной.

РЕШЕНИЕ.

Составим математическую модель задачи. Пусть фабрика изготавливает x₁ столов и x₂ шкафов. По смыслу задачи эти переменные неотрицательны, x x₁, ₂ ≥ 0 . Прибыль от реализации такого количества шкафов и столов составит F = 6x₁ +9x₂ рублей, ее нужно максимизировать:

F = 6x₁ +9x₂ → max .

Теперь составим ограничения задачи.

Для изготовления x₁ столов и x₂ шкафов потребуется 0,2x₁ +0,1x₂ древесины первого вида, запасы которой составляют 40 куб.м., поэтому 0,2x₁ +0,1x₂ ≤ 40 , или 2x₁ + x₂ ≤ 400. Для изготовления x₁ столов и x₂ шкафов потребуется 0,1x₁ +0,3x₂ древесины второго вида, запасы которой составляют 60 куб.м., поэтому 0,1x₁ +0,3x₂ ≤ 60 , x₁ +3x₂ ≤ 600.

Для изготовления x₁ столов и x₂ шкафов потребуется 1,2x₁ +1,5x₂ древесины третьего вида, запасы которой составляют 371,1 куб.м., поэтому 1,2x₁ +1,5x₂ ≤ 371,1, 12x₁ +15x₂ ≤ 3711,

4x₁ +5x₂ ≤ 1237

Получаем задачу линейного программирования:

F = 6x₁ +9x₂ → max,

2x₁ + x₂ ≤ 400,

x₁ +3x₂ ≤ 600,

4x₁ +5x₂ ≤1237,

x₁, ₂ ≥ 0.

Решим задачу средствами Excel. Заполним ячейки исходными данными (в виде таблицы) и формулами математической модели. Вычисляемые ячейки пометим цветом.

Таблице в режиме чисел:

Таблица в режиме формул: (для отображения формул можно перед знаком равно поставить пробел)

Вызываем надстройку «Поиск решения» и заполняем параметры: (для активирования данной функции необходимо выполнить команду СЕРВИС, НАДСТРОЙКА и установить галочку против программы Поиск решения)

Вносим целевую функцию и ограничения.

Запускаем решение:

Получаем решение:

Получили нецелочисленное решение – 101,571 столов и 166,143 стульев. Чтобы получить более «реальное» в экономическом смысле решение, добавим ограничение целочисленности переменных, тогда получим:

Искомое решение:

Таким образом, следует производить 103 стула и 165 шкафов, при этом прибыль от реализации будет максимальна и составит 2103 рубля. В процессе производства будут остатки древесины первого и второго типа: 2,9 и 0,2 кубометра соответственно. Трудоемкость будет «использована» в полном размере.

Урок 3. Индивидуальные задания для учащихся

Задача 1. Для изготовления одного пирожка требуется 0,8 ед. начинки и 4 ед. теста, одного пирожного 4 ед. начинки и 0,5 ед. теста, одного рулета 2 ед. начинки и 2,5 ед. теста. Сколько пирожков, пирожных и рулетов нужно сделать кондитерской, если в наличии имеется 120 ед. теста и 300 ед. начинки? Определите доход от реализации кондитерских изделий, если доход от продажи одного пирожка составляет 3 рубля, одного пирожного 2 рубля, одного рулета 1,5?

Задача 2. Состав еды рядовых регламентируется верховной ставкой главнокомандующего, которая устанавливает нижние нормы питания в сутки по основным компонентам: 1500 килокалорий, 100 г белков, 280 г углеводов, 90 г жиров, 1 кг воды. На складах есть 4 вида продуктов, которые выдают защитникам Родины сухим пайком: лимонад, тушенка в маленьких банках, унифицированные наборы горбушек и пирожки с ежевикой. Стоимость этих четырех продуктов соответственно 12 руб., 34 руб., 3 руб. и 20 руб. Какова минимальная сумма, которую должен затратить прапорщик на питание одного солдата?

Задача 3. Предприятие выпускает два вида продукции: Изделие 1 и Изделие 2. На изготовление единицы Изделия 1 требуется затратить 11 кг сырья первого типа, 21 кг сырья второго типа, 31 кг сырья третьего типа. На изготовление единицы Изделия 2 требуется затратить 12 кг сырья первого типа, 22 кг сырья второго типа, 32 кг сырья третьего типа. Производство обеспечено сырьем каждого типа в количестве 100 кг, 150 кг, 180 кг соответственно. Рыночная цена единицы Изделия 1 составляет 3 тыс. руб., а единицы Изделия 2 — 4 тыс.руб. Требуется составить план производства изделий, обеспечивающий максимальную выручку от их реализации.

Задача 4. Для производства двух видов продукции А и В предприятие использует четыре группы оборудования 1, 2, 3, 4. На производство одной штуки продукции А требуется занять в течение одной единицы времени 1, 0, 5, 2 единиц соответственно 1, 2, 3, 4 оборудования. На производство одной штуки продукции В требуется 1, 1, 0, 2 единиц оборудования 1, 2, 3, 4. Имеется оборудования по группам 1-10, 2-12, 3-24, 4-20 единиц. Предприятие получает с одной штуки продукции А доход 4 тыс. рублей, а продукции – 6 тыс. руб.

Сколько штук продукции каждого вида должно производить предприятие, чтобы получить максимальную прибыль?

Задача 5. В мастерской промартели освоили производство столов и тумбочек для торговой сети. Для их изготовления имеется два вида древесины 1- 72м³ и 2-56 м³.

На каждое изделие требуется древесина м³ каждого вида

От производства одного стола промартель получает чистого дохода 1,1 тыс. руб. и от производства тумбочки 700 руб. Определить, сколько столов и тумбочек должна производить промартель, чтобы получить максимальную прибыль?

Задача 6. Цех выпускает трансформаторы двух видов. На один трансформатор первого вида расходуется 3 кг. Проволоки и 5 кг. Железа, а на один трансформатор второго вида требуется 3 кг железа и 2 кг. Проволоки. От реализации одного трансформатора первого вида цех получает прибыль в 1,2 тыс. руб., а от реализации одного трансформатора второго вида 1 тыс. руб. Сколько трансформаторов каждого вида должен производить цех, чтобы получить максимальную прибыль, если цех располагает 480 кг. Железа и 300 кг. Проволоки?

Задача 7. Для откорма животных на ферме в их ежедневный рацион необходимо включить не менее 33 единиц вещества А, 23 единицы питательного вещества В. Для откорма используется два вида кормов. Данные о содержании питательных веществ и стоимости весовой единицы представлены в таблице.

Составьте наиболее дешевый рацион, при котором каждое животное получило бы необходимое количество питательных веществ А и В?

Задача 8. Имеется три вида сырья А, В, С, которые используются для производства двух видов продуктов 1 и 2. В распоряжении находится 500 единиц сырья А, 750 единиц сырья В и 200 единиц сырья С. Продукт 1 состоит из 1 единицы сырья А и двух единиц сырья В. Продукт 2 состоит из двух единиц сырья А, одной единицы сырья В и одной единицы сырья С. Доход от производства одной единицы продукта 1 составляет 4 тыс. руб, а от одной единицы продукта 2 – 3 тыс. руб. Сколько единиц каждого вида продукции производить, чтобы получить максимальную прибыль?

Задача 9. Имеется два вида сырья для производства двух видов продуктов 1 и 2. Продукт 1 состоит из двух единиц сырья А и пяти единиц сырья В. Продукт 2 состоит из трех единиц сырья А и четырех единиц сырья В. Доход от производства одной единицы продукта 1 составляет 4 тыс. руб., а от одной единицы продукта 2 – 5 тыс. руб. Сколько единиц каждого вида продукта нужно производить, чтобы максимизировать прибыль, если в распоряжении имеется 500 единиц сырья А и 750 единиц сырья В?

Задача 10. Фирма производит два вида продукции А и В. Для выпуска каждого вида продукции требуется определенное время обработки на всех устройствах I, II, III:

Пусть время работы на устройствах соответственно 40, 36, 36 часов в неделю. Прибыль от изделий А и В соответственно составляет 5$ и 3$. Определите недельные нормы выпуска изделий для максимизации прибыли. (Рынок сбыта для каждого продукта неограничен.)

Задача 11. В рационе животных используется два вида кормов. Животные должны получать три вида веществ. Составить рацион кормления, обеспечивающий минимальные затраты. Исходные данные сведены в таблицу.

Задача 12. В рационе лошадей используется два вида кормов (сено и рожь). Лошади должны получать три вида веществ. Составить рацион кормления, обеспечивающий минимальные затраты. Исходные данные сведены в таблицу.

Задача 13. Фирма производит три вида продукции: А, В и С. Для выпуска каждого вида продукции требуется определенное время обработки на всех устройствах I, II, III, IV:

Пусть время работы на устройствах соответственно 84, 42, 21, 42 часа. Определите, какую продукцию и в каких количествах стоит производить для максимизации прибыли. Рынок сбыта для каждого продукта неограничен.

Задача 14. Фирма занимается составлением диеты, содержащей, по крайней мере, 20 ед. белков, 30 ед. углеводов, 10 ед. жиров и 40 ед. витаминов. Как дешевле всего достичь этого при указанных ценах в рублях на 1 кг (1 литр) пяти имеющихся продуктов?

Задача 15. Фирма производит два набора удобрений для газонов: обычный и улучшенный. В обычный входят 3 фунта азотных, 4 фунта фосфорных и один фунт калийных удобрений, а в улучшенный — 2 фунта азотных, 6 фунтов фосфорных и 2 фунта калийных удобрений. Известно, что для некоторых газонов требуется, по меньшей мере, 10 фунтов азотных, 20 фунтов фосфорных и 7 фунтов калийных удобрений. Обычный набор стоит 3$, а улучшенный — 4$. Сколько (целое число) и каких наборов удобрений надо купить, чтобы обеспечить эффективное питание почвы и минимизировать стоимость?

Задача 16. Производятся изделия А и В, при изготовлении которых используется два типа технологического оборудования 1 и 2. На производство единицы изделия А оборудование 1 используется 2 часа, а оборудование 2 1 час. На производство единицы изделия В оборудование 1 используется 1 час, а оборудование 2 – 2 часа. Администрация на изготовление изделий может выделить оборудование 1 – на 10 часов, а оборудование 2 – на 8 часов. Спланировать производство изделий А и В так, чтобы общая прибыль была наибольшей, если от реализации единицы изделия А прибыль 5 тыс руб, В – 2 тыс. руб

Задача 17. Цех двойного суперфосфата выпускает два вида минеральных удобрений А и В. На производство 1 кг удобрений А используют 5 кг питательных веществ 1 и 4 кг. Питательного вещества 2. На производство 1 кг удобрений В используют 3 кг питательных веществ 1 и 2 кг. Питательного вещества 2. От реализации 1 кг А цех получает прибыль 40 руб., а от реализации 1 кг В – 35 руб.

Какое количество удобрений должен производить цех, чтобы получить максимальную прибыль, если цех имеет всего 450 кг питательного вещества 1 и 400 кг питательного вещества 2?

Урок 4. План работы над проектом

1. Создать папку «Проект учащегося ФИО», в которую перенести файл с решенной задачей и создать текстовый документ.

2. Содержание работы:

1. Титульный лист

2. Оглавление

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

4. Практическая часть состоит из технологии решения вашей задачи.

5. Используемые источники

1. Параметры оформления документа

   1. Лист книжный

   2. Отступы 1,5 см со всех сторон

   3. Шрифт Times New Roman для основного текста, размер 12

   4. Шрифт Arial для заголовков, размер 14, начертание Ж

   5. Номер в правом нижнем углу и без номера на титульном листе

   6. Каждый раздел документа располагается с нового листа.

Оформление проекта

1. Набрать текст

2. Разделить документ на страницы: титульный лист, оглавление, введение, теоретическая часть, практическая часть, список литературы. Установить курсор в начало новой страницы, выполнить ВСТАВКА/РАЗРЫВ/НОВАЯ СТРАНИЦА

Обратите внимание, что страница ОГЛАВЛЕНИЕ пока пустая.

3. Заполните титульный лист, предмет, свою фамилию. Выровняйте текст, кроме таблицы, по центру.

4. Выделите все заголовки и присвойте им стиль ЗАГОЛОВОК 1.

5. Вставить нумерацию в правом нижнем углу. ВСТАВКА/НОМЕРА СТРАНИЦ/ справа внизу и без номера на первой странице.

6. Перейти на страницу ОГЛАВЛЕНИЕ, вставить оглавление. ВСТАВКА/ССЫЛКА/ОГЛАВЛЕНИЕ И УКАЗАТЕЛИ/ Выбрать выкладку ОГЛАВЛЕНИЕ, задать УРОВНИ -1, убрать отметку о гиперссылках, но задать отметки

• ПОКАЗАТЬ НОМЕРА СТРАНИЦ

• НОМЕРА СТРАНИЦ ПО ПРАВОМУ КРАЮ.

Урок 5. Образец выполненного проекта

Урок 6. Вопросы итогового теста

1. Какую опцию главного меню необходимо открыть для выбора функции ПОИСК РЕШЕНИЯ?

2. Задача. Комплект мебели (парта и два стула) стоит 8500 рублей. Известно, что производитель М продает мебель упаковками по 6 комплектов в упаковке, а производитель N — по 4 комплекта в упаковке. Какое максимальное количество можно купить на 150 тыс. рублей? В ответе запишите два целых числа через пробел: количество комплектов и потраченную сумму в рублях. Решение

Какая ячейка оптимизируется?

1. Как оптимизируется ячейка? (варианты минимум, максимум, значение и т.д.)

2. Какие ячейки изменяются при оптимизации?

3. На какие ячейки накладываются ограничения при оптимизации?

4. Необходимое условие для автоматического собирания оглавления

5. Для нумерации страниц в работе необходимо выполнить

6. Расставьте по порядку основные разделы документа «Проект»

7. Выберите советского ученого, лауреата Нобелевской премии в области экономики (дан список имен)

8. Выберите верное утверждение (Предлагаются варианты размещения нумерации)

Источники информации

1. Практикум по вычислительной математике: Метод. Разработка/ Сост. Н.Л.Грохульская, С.Г.Ершова, А.Н.Новогрудская; Свердловский педагогический институт. Свердловск, 1990, 215  с, с.111-122

2. Культура информационной деятельности: Учебное пособие для основной школы (9 класс)./ В.В.Мочульский, А.Г.Гейн, В.И.Жильцова, В.И.Кадочникова, Т.В.Шпота, Е.А.Гвоздева, В.Г.Мещеряков, А.Г.Мачульская.- Екатеринбург: Центр «Учебная книга»; Смоленск; Издательство «Ассоциация XXIвек», 2006. – 432 с, с.176-180

3. https://www.matburo.ru/Examples/Files/LP_Excel1.pdf

4. https://inecon.org/docs/Kantorovich_1.pdf

Приложение. Образец выполненного проекта

Министерство образования и молодежной политики Свердловской области

Управление образования администрации Чкаловского района

Образовательная область: технология

Предмет: технология

Проект

Решение оптимизационных задач средствами Excel

Екатеринбург

2020 г.

Оглавление

Введение 3

Линейное программирование, оптимизационные задачи 4

Практическая часть 5

Источники информации 8

Введение

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

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

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

Старые методики обучения требуют переосмысления и развития по мере появления новых информационных технологий.

Разрешение приведенных противоречий обуславливает актуальность настоящей работы и определяет ее проблему: какие средства могут быть использованы для формирования ИКТ-компетентности у учащихся средней школы?

Этот анализ определил следующий выбор темы проекта: «Методика решения оптимизационных задач средствами Excel».

Объект исследования: формирование ИКТ-компетентности.

Предмет исследования: использование разработки методики решения оптимизационной задачи средствами Excel для формирования ИКТ-компетентности.

Цель исследования: формирование ИКТ-компетентности средствами решения оптимизационной задачи в Excel.

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

На основании цели исследования и рабочей гипотезы была сформулирована следующая задача исследования: разработать методику решения оптимизационной задачи средствами Excel для формирования ИКТ-компетентности.

Теоретическую основу исследования составили работы по линейному программированию:

1. https://www.matburo.ru/Examples/Files/LP_Excel1.pdf

2. https://inecon.org/docs/Kantorovich_1.pdf

Теоретическая значимость исследования заключается в следующем: описана последовательность решения оптимизационных задач средствами Excel.

Практическая значимость исследования состоит в том, что теоретические результаты доведены до уровня практического применения: решена конкретная задача.

Линейное программирование, оптимизационные задачи

Математические исследования отдельных экономических проблем, математическая формализация числового материала проводилась ещё в XIX веке.

Развитие экономики потребовало количественных показателей, и в начале XX века были созданы и исследованы математические модели, которые повлияли на работы экономиста и статистика Василия Васильевича Леонтьева. Он разработал межотраслевую модель производства и распределения продукции.

В 1938 году Леонид Витальевич Канторович в порядке научной консультации приступил к изучению чисто практической задачи по составлению наилучшего плана загрузки лущильных станков (фанерный трест). Эта задача не поддавалась обычным методам. Стало ясно, что задача не случайная. В 1939 году Леонид Канторович опубликовал работу «Математические методы организации и планирования производства», в которой сформулировал новый класс экстремальных задач с ограничениями и разработал эффективный метод их решения, таким образом были заложены основы линейного программирования. Изучение подобных задач привело к созданию новой научной дисциплины линейного программирования и открыло новый этап в развитии экономико-математических методов. В 1975 году он стал лауреатом Нобелевской премии по экономике совместно с Тьяллингом Купмансом за вклад в теорию оптимального распределения ресурсов»).

Общей (стандартной) задачей линейного программирования называется задача нахождения минимума (максимума) линейной целевой функции (линейной формы). Задача, в которой фигурируют ограничения в форме неравенств, называется основной задачей линейного программирования (ОЗЛП).

Примеры задач:

• транспортная задача;

• максимальный поток;

• задача экономного использования сырья.

Наиболее известным и широко применяемым на практике для решения общей задачи линейного программирования (ЛП) является симплекс-метод.

Практическая часть

Задание. Фирма производит два вида продукции A и В. Для выпуска каждого вида продукции требуется определенное время обработки на всех устройствах I, II, III:

Пусть время работы на устройствах соответственно 40, 36, 36 часов в неделю. Прибыль от изделий А и В соответственно составляет 5$ и 3$. Определите недельные нормы выпуска изделий для максимизации прибыли. (Рынок сбыта для каждого продукта неограничен.)

Решение.

Составим математическую модель задачи. Пусть фабрика изготовит х изделий А и у изделий В. По смыслу задачи эти переменные неотрицательные, х,у=0.

Прибыль от реализации такого количества продукции составит F=5*x+3*y.

Эту функцию надо максимизировать

Теперь составим ограничения задачи.

Для изготовления х изделий А и у изделий В потребуется:

0,5*х+0,25*у времени работы I устройства;

0,4*х+0,3*у времени работы II устройства;

0,2*х+0,4*у времени работы III устройства;

Но время работы I устройства ограничено 40 часами, т.е.

0,5*х+0,25*у

время работы II устройства ограничено 36 часами, т.е.

0,4*х+0,3*у

время работы III устройства ограничено 36 часами, т.е.

0,2*х+0,4*у

Получаем задачу линейного программирования:

Решаем задачу средствами Excel. Заполняем ячейки исходными данными (в виде таблицы) и формулами математической модели.

Таблица в режиме чисел

Таблица в режиме формул:

Вызываем надстройку «Данные / Поиск решения» и заполняем параметры:

Вносим целевую функцию и ограничения.

Получаем решение

Получили следующий результат:

При заданных ограничениях предприятие может получить максимальную прибыль

420 единиц, изготовляя 60 единиц изделия А и 40 единиц изделия В.

Источники информации

1. Симоненко В.Д. Технология: базовый уровень: 10-11 классы: учебник для учащихся общеобразовательных учреждений / В.Д. Симоненко, О.П.Очинин, Н.В.Матяш: под ред. В.Д.Симоненко. – М.: Вентана-Граф., 2010. – 224 с, : ил.

2. http://www.apmath.spbu.ru/ru/staff/kuzyutin.d/files/vved_v_lp_lekciya.pdf

3. https://math.semestr.ru/simplex/simplex_lectures.php