Oc-windows.ru

IT Новости из мира ПК
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Формулы количества и суммы в Excel

Формулы количества и суммы в Excel

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

Формулы количества Excel

СЧЁТ

Подсчитывает количество ячеек, содержащих числа.
Учитывает аргументы, являющиеся числами, датами или текстовым представлением чисел (например, число, заключенное в кавычки, такое как «1»).

Синтаксис: СЧЁТ(значение1;[значение2];…)
Пример формулы: =СЧЁТ(A1:A20)

СЧЁТЗ

Учитывает данные любого типа, включая значения ошибок и пустой текст («»). Например, если в диапазоне есть формула, которая возвращает пустую строку, функция СЧЁТЗ учитывает это значение. Функция СЧЁТЗ не учитывает пустые ячейки.

Синтаксис: СЧЁТЗ(значение1;[значение2];…)
Пример формулы: =СЧЁТЗ(A2:A7)

СЧИТАТЬПУСТОТЫ

Подсчитывает пустые ячейки в указанном выше диапазоне.

Синтаксис: СЧИТАТЬПУСТОТЫ (значение1;[значение2];…)
Пример формулы: =СЧИТАТЬПУСТОТЫ(A2:A7)

СЧЁТЕСЛИ

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

Синтаксис: =СЧЁТЕСЛИ(где нужно искать; что нужно найти)
Пример формулы: =СЧЁТЕСЛИ(A2:A5;»Лондон»); =СЧЁТЕСЛИ(A2:A5;A4)

СЧЁТЕСЛИМН

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

Синтаксис: =СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
Пример формулы: =СЧЁТЕСЛИМН(B2:B5,»=Да»,F2:F5,»>1″)

Формулы суммы Excel

СУММ

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

Пример формулы: =СУММ(A2:A10), =СУММ(A2:A10;C2:C10)

СУММЕСЛИ

Суммирует значения, которые соответствуют указанному условию. Можно задать условие для текущего диапазона, а просуммировать соответствующие значения из другого диапазона.
Например, формула =СУММЕСЛИ(B3:B9; «Яблоки»; C3:C9) суммирует только те значения из диапазона C3:C9, для которых соответствующие значения из диапазона B2:B5 равны «Яблоки».

Синтаксис: = СУММЕСЛИ(диапазон; условие;[диапазон_суммирования])
Пример формулы: =СУММЕСЛИ(B2:B25;»> 5″), =СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7)

СУММЕСЛИМН

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

Синтаксис: СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
Пример формулы: =СУММЕСЛИМН(B2:B9; C2:C9; «Рязань»; E2:E9; «Бананы»)

Программа для раскроя в Excel и Calc

Значок Честный распил!Достаточно функциональную программу для решения задач экономного линейного раскроя погонажных материалов можно создать самостоятельно и абсолютно бесплатно, используя исключительно встроенные функции и штатную надстройку «Решатель» программы Calc.

. из свободно распространяемого офисного пакета LibreOffice. При этом ни к созданию макросов, ни к написанию пользовательских функций, ни к программированию вообще обращаться нет необходимости!

«Решатель» Calc — это NLPSolver (wiki.openoffice.org/wiki/NLPSolver).

Предложенное далее решение прекрасно работает и в Calc, и в Excel. Причем даже переписывать формулы не нужно! Достаточно просто скопировать ячейки с формулами и данными и вставить на лист Excel, а вместо «Решателя» использовать надстройку «Поиск решения» — Excel Solver (solver.com/excel-solver-online-help) или лучше – надстройку OpenSolver (opensolver.org).

И все-таки – почему в данном случае стоит попробовать использовать не Excel, а Calc?

  • Во-первых (и это важно), в «Решателе» Calc нет ограничения по количеству переменных. В Excel «Поиск решения» может принять для поиска оптимального раскройного плана только 200 схем раскроев. (Но, если скачать и в Excel подключить OpenSolver, то получите еще более широкие возможности, чем в LibreOffice Calc!)
  • Во-вторых, интерфейс «Решателя» — на русском языке. Интерфейс OpenSolver — на английском языке.
  • В-третьих, в «Решателе» Calc представлены 5 различных алгоритмов поиска решения. В Excel без надстройки OpenSolver набор алгоритмов скромнее.
  • В-четвертых, LibreOffice Calc, в отличие от MS Excel, бесплатен для коммерческого использования.
  • В-пятых, LibreOffice Calc не обязательно устанавливать на компьютер. На официальном сайте можно скачать полнофункциональную версию — LibreOffice Portable (libreoffice.org/download/portable-versions/).

Структура задачи и терминология.

Данная тема уже была подробно рассмотрена на блоге в статье «Линейный раскрой в Excel» 4 года назад. Вновь обратиться к ней побудило желание автоматизировать генерацию схем раскроев и сравнить результаты работы «Решателя» и OpenSolver.

Полную «задачу о распиле» можно разделить на две значительные подзадачи:

  1. Генерация всех возможных схем раскроев.
  2. Поиск на основе сгенерированных схем оптимального раскройного плана.

Первая подзадача в статье «Линейный раскрой в Excel» решалась методом составления схем раскроев «вручную» по определенному алгоритму.

Вторая подзадача решалась автоматически с использованием надстройки Excel «Поиск решения» (Excel Solver).

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

Поиск плана раскроя осуществим тремя способами:

  1. В LibreOffice Calc с помощью «Решателя» (NLPSolver).
  2. В MS Office Excel посредством штатного «Поиска решения» (Excel Solver).
  3. В MS Office Excel при помощи надстройки OpenSolver.
Читать еще:  Как обновить Оперу в Windows XP

Определимся с терминологией и ограничениями:

  1. Заготовки – это исходный материал в виде рулонов, прутков, полос, стержней и т.д. одинаковой длины.
  2. Детали – это элементы, которые необходимо получить, разрезав исходные заготовки на части.
  3. Схема раскроя – один из вариантов раскроя заготовки, при котором длина отхода всегда меньше длины самой короткой детали.
  4. План раскроя – это перечень схем раскроя с количеством их повторений.
  5. *Ширина реза равна нулю.

Пример.

Таблица Рулоны - деталиИсходные данные для примера я и в этот раз придумывать не стал, а взял из статьи «Задача раскроя» в Википедии (ru.wikipedia.org/wiki/Задача_раскроя).

Условие задачи:

Бумагоделательная машина производит рулоны (заготовки) шириной 5600 мм.

Нужно найти план раскроя для нарезки 13 типоразмеров конечных рулонов (деталей), используя минимальное количество исходных рулонов (заготовок).

Ширины конечных рулонов (размеры деталей) и их необходимое количество — в таблице слева.

Скриншот программы:

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

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

В ячейках с желтой заливкой – не защищенные от изменений формулы. Будьте внимательны! Изменять значения можно только в ячейках со светло-бирюзовой заливкой.

Скриншот Программа для раскроя в Calc

Прошу уважающих труд автора скачать файлы с программой после подписки на анонсы статей. Подписные окна расположены в конце статьи и наверху страницы.

Ссылки на скачивание файлов с программой:

    (ods 106,4KB); (xlsx 60,3KB).

Правила ввода исходных данных:

В светло-бирюзовые ячейки записываем исходные данные из условия задачи:

  • длину исходных рулонов – заготовок — ;
  • длины конечных рулонов – деталей — i ;
  • количество конечных рулонов – деталей — i .

Длины деталей i следует вписывать в порядке уменьшения размеров, слева – направо:

Внимание! Если типоразмеров деталей в другой вашей задаче будет меньше 13, например 10, то, чтобы не переделывать каждый раз поле схем раскроев, в первые 3 ячейки для длин деталей следует записать значения больше размера заготовки, а их количество указать равным нулю:

А > Lз

Решение:

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

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

Единственное, что следует дополнительно настроить:

  • Из окна «Решателя» нужно перейти в окно «Параметры» и выбрать в выпадающем списке «Механизма решателя»: LibreOffice CoinMP линейный решатель.
  • Там же в окне «Настройки» рекомендую проставить все галочки, так как переменные у нас в примере неотрицательные и целочисленные.

Ответ:

Минимум из 73 заготовок (исходных рулонов) можно изготовить все детали (конечные рулоны) с долей отходов всего 0,401%! В плане раскроя используется 12 схем раскроев. Время поиска решения – менее 10 секунд! (План раскроя показан выше на скриншоте программы.)

Ответ в Википедии: 73 заготовки; 0,401% отходов; в плане – 10 схем раскроев.

Итоги.

Существует несколько решений — планов раскроя с 13, 12, 11, 10 схемами раскроев, состоящих из 73 заготовок.

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

Таблица Excel Количество заготовок по планам и раскроям

Все программы справились с поставленной задачей – нашли раскройные планы, обеспечивающие минимальное количество отходов.

Для обеспечения работоспособности стандартного «Поиска решения» (Excel Solver) в Excel пришлось удалить из поиска 13 схем раскроев с большими длинами остатков.

Если для этой задачи применить прямолинейный «жадный» алгоритм, традиционно используемый на многих производствах (сначала режем широкие рулоны, а узкие – в конце, используя по возможности отходы), то потребуется 82 исходных рулона-заготовки. Более 11,3% материала уйдет в отходы. При этом в плане будет 15 схем раскроев.

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

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

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

Читать еще:  Как создать образ диска в Daemon Tools

Статьи с близкой тематикой

Отзывы

12 комментариев на «Программа для раскроя в Excel и Calc»

    Андрей 12 Июн 2019 09:59

Здравствуйте. Работаю инженером-конструктором. Расчеты зубчатых и звездочек очень пригодились. Остальное осваиваю в своей практике. Большое Вам спасибо за труд.

Огромное Вам спасибо за то что делитесь своими знаниями. Благодаря вашим статьям многому научился.

Больше спасибо за труд и доходчивые объяснения. Я изучал линейное программирование, но мы пользовались программой POM fow Windows для решения задач на минимум и максимум. Тогда еще не было такого пакета в Excel. Применение расчета позволило мне съэкономить более 150 тыс. рублей при расчете бруса на дом, так как фирма сознательно завышала объем.

Спасибо Андрей, Юрий и Антон за теплые отзывы.

Антон, я рад за Вас. Не очень понял применение какой именно программы — POM fow Windows или описанных в статье помогло Вам сэкономить более 150 тыс. рублей,но это и не важно. Важно то, что Вы задумались, вспомнили линейное программирование и не дали Вас провести. Может быть в этом и заключается одно из главных предназначений высшего образования — не поверить, что-то вспомнить и разобраться. Вполне возможно, что фирма Вас и не обманывала, а просто по-привычке «пилила по „жадному алгоритму“», выбрасывая отходы.

Благодарю вас! Отличную работу проделали!

Александр, большое спасибо за очень грамотные расчеты, которые мне очень помогают. Проектирую оборудование для инвалидов: подъемники по лестничным маршам, эвакуаторы и т.п. С уважением, Николай.

Здравствуйте. В вашем примере при изменении длины первой детали с 2200 на 2250 длина остатка становится отрицательной (ячейка Q12=-30). При этом после «решения» этот вариант разбивки предлагается к исполнению. Что я делаю не так?

Здравствуйте. С файлом xlsx бывает возникают вопросы.

Скачайте файл ods и откройте в LibreOffice — там всё работает правильно.

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

Теперь возникла другая проблема. В LibreOffice в вашем примере, если поставить длину исходной заготовки 12000 (вместо 5600), то в столбце R количество заготовок в решении есть и отрицательные, и дробные.

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

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

Чем более громоздкая задача, тем сложнее «Решателю» с ней справляться, бывает выдает ошибки или вовсе не находит решение. Задачи линейного программирования не просты.

Буква Е в калькуляторе или как убрать Е в Excel

Не только школьники, но и взрослые недоумевают что за буква Е появляется в результатах вычислений в калькуляторах или таблицах. Это экспонента? Но тогда почему Е, а не е ? Или это ошибка от слова ERROR? Как убрать E в таблице Excel?

Попробую всё объяснить наглядно про стандартный вид числа, экспоненту и экспоненциальную запись. Я не математик и не учитель, в комментах можете поправить. Ну и читайте крайне полезный пост — КАК ПОЛУЧИТЬ КУРСЫ ВАЛЮТ ИЗ ИНТЕРНЕТА В EXCEL ИЛИ ТАБЛИЦЫ ГУГЛ.

Для чего буква Е в записи числа в Excel

Е — экспоненциальный формат записи числа. Служит для записи очень больших и очень малых чисел. Удобен для действий над такими числами. Используется, например, в калькуляторах, в которых нет возможности отобразить большое число. Например:

2134123412341230000000000000000000 = 2,13412341234123E+33

Стандартный вид числа

Стандартный вид числа 37973,797*10 3 .

Число 3797 в стандартном виде записывается как 3,797*10 3 , где 3,797 — мантисса, а 10 3 — порядок. Так можно записать любое действительное число.

Ещё число 3797 можно записать как 3,797*10 3 , как 37,97*10 2 или как 379,7*10.

В нормализованной научной записи для удобства действий записывается 1 знак до запятой в мантиссе. Или, другими словами, записывается 1 знак в целой части: 2,75*10 12 , 9,175*10 7 и т.п.

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

Надеюсь, что хоть что-то стало понятнее. Ниже форматы представления чисел в Excel.

Формат представления чисел в таблице с E

Действия над числами в стандартном виде

При сложении или вычитании нужно привести к одному порядку переведя число в нестандартный вид

1,7*10 13 + 1,7*10 12 =1,7*10 13 + 0,17*10 13 = (1,7+ 0,17)*10 13 =1,87*10 13 =1,87E+13

При умножении показатели степеней складываем, а при делении вычитаем.

1,7*10 3 * 3,2*10 2 =(1,7×3,2)*(10 3+2 )= 5,44*10 5 =5,44E+05

Ещё примеры можете посмотреть тут .

Как привести число с Е к привычному виду в Excel, OpenOffice или LibreOffice

Как на скриншоте выше — выбрать соответствующий формат в блоке Число на вкладке Главное для Excel 2019. Выделяем нужные ячейки и меняем формат. Для других версий или таблиц других производителей будет как на скриншотах ниже.

Читать еще:  Скачать драйвера для ASUS F5RL

Убираем Е в Excel

Правым кликом по выделенной ячейке или группе вызываем меню, в котором выбираем Формат ячеек…

Как убрать Е в Excel

В окне Формат ячеек выбираем необходимое Число десятичных знаков.

Как убрать Е в Excel

Убираем Е в OpenOffice

Тем же правым кликом на выделенных ячейках вызываем меню где выбираем Формат ячеек и устанавливаем дробную часть. Видите, в ячейке D4 число с E, а в D7 уже привычное нам число.

Как убрать Е в OpenOffice

Убираем Е в LibreOffice

Всё точно так же как в OpenOffice. Формат сам перестроится после того как вы укажете дробную часть. Формат Standart (или Общий для Excel) отобразит данные в стандартном виде, то есть с E.

Как убрать Е в LibreOffice

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

Как получить больше знаков в целой части числа c E

Теперь выясним всё про константу e и экспоненту.

Что такое число e или EXP (функция EXP)

Константа e , иногда называемая числом Эйлера , приблизительно равна 2,718 и является основанием натурального логарифма. Это не E.

Экспонента — показательная функция y=e x , где е — число Эйлера.

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

Описание функции EXP (функция EXP) в Excel для Microsoft 365 Excel для Microsoft 365 для Mac

Минутный пост какой-то длинный получился. Надеюсь, стало понятно, что E — форма записи «длинных» чисел и не более.

Кредитный калькулятор в Excel

Кредитный калькулятор в Excel

Когда для реализации планов или определенных целей не хватает собственных средств, одним из наиболее распространенных вариантов пополнить бюджет остается кредитование. Какой кредит брать? В каком банке? На какой срок? Чтобы ответить на эти вопросы и принять правильное решение, необходимо просчитывать множество вариантов. В этом деле поможет кредитный калькулятор в Microsoft Excel. Калькулятор, о котором сейчас пойдет речь, будет рассчитывать суммы помесячных выплат по кредиту, при условии погашения его равными частями.

Расчет суммы ежемесячных выплат

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

  1. Откройте программу Microsoft Excel и введите в столбик А описание исходных данных: сумма кредита, годовая ставка, срок кредита (в месяцах), а также строку результата – сумма ежемесячных выплат. В столбик В мы будем заносить соответствующие значения.Кредитный калькулятор в Excel
  2. Ставим курсор на ячейку результата (в нашем примере это ячейка В5) и в меню «Вставка» находим пункт «функция». Откроется окно Мастера функций Кредитный калькулятор в Excel
  3. Выберите категорию «финансовые» и в окне функций выделите функцию ПЛТ Кредитный калькулятор в Excel
  4. Далее необходимо указать ячейки, которые будут служить аргументами функции. Первый аргумент Ставка. Чтобы не вводить название ячейки вручную, окно мастера можно свернуть кнопкой в конце строки.Кредитный калькулятор в Excel
  5. Сверните окно и выделите ячейку, в которой будете указывать ставку (в нашем примере это ячейка В2).Кредитный калькулятор в Excel
  6. Нажмите на кнопку в конце строки аргумента, чтобы вернуться в окно мастера.Кредитный калькулятор в Excel
  7. Так как ставку вы буде указывать годовую, а результат выплат нужно получить помесячный, то в строке аргумента заданное значение нужно разделить на 12. Дополните строку аргумента «/12» Кредитный калькулятор в Excel
  8. Следующий аргумент – количество периодов. Таким же образом сверните окно и укажите ячейку значения «срок кредита» (у нас это ячейка В3) Кредитный калькулятор в Excel
  9. Аргумент ПС означает сумму кредита, сверните окно мастера и укажите соответствующую ячейку (в нашем примере В1) Кредитный калькулятор в Excel
  10. Аргумент БС выражает конечный баланс. Очевидно, что наша задача погасить кредит полностью, поэтому введите в строку значение аргумента «0». Аргумент Тип указывает на способ учета зачисления выплат – в начале периода (месяца) или в конце. Большинство кредитов выдается по второму типу учета, поэтому определите значение аргумента равным «0» Кредитный калькулятор в Excel
  11. Нажмите кнопку ОК и формула готова. Введите исходные значения, и в результативной ячейке вы найдете сумму ежемесячных выплат. Она будет со знаком минус. Это означает, что деньги вам нужно отдавать… Кредитный калькулятор в Excel

Дополнительные расчеты

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

  1. Всего выплат по кредиту (за весь период) – значение выражается формулой, умножьте сумму ежемесячных выплат на количество месяцев. В нашем примере В5*В3. Кредитный калькулятор в Excel
  2. Сумма переплаты – сколько денег вы заплатите за пользование кредитными средствами. Для этого нужно сложить сумму всех выплат (эта величина у нас со знаком минус!) и сумму кредита. Формула в нашем случае: В6+В1 Кредитный калькулятор в Excel
  3. Просчитывать разные варианты и сравнивать предложения по кредитованию с таким калькулятором очень легко.Кредитный калькулятор в Excel

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

Звёзд: 1Звёзд: 2Звёзд: 3Звёзд: 4 Звёзд: 5
(1 оценок, среднее: 5,00 из 5)

Ссылка на основную публикацию
Adblock
detector
×
×