|
Поиск ячеек по сумме |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home/users/d/dm9/domains/mokshin.su/includes/unicode.inc on line 311.
|
Опубликовано d.mokshin в чтв, 11/06/2015 – 22:59. |
||
|
Хороший инструмент – надстройка Поиск решения в MS Excel!
Например, можно использовать в ситуации, когда вам нужно найти вариант из каких различных чисел могла сложиться определнная сумма (может вы ищете из каких счетов могла сложиться сумма оплаты). Допустим, нужно найти по приведенным числам сумму 10:
Для начал включим надстройку или проверим, что она включена (в Excel 2013): Файл / Параметры, раздел Надстройки, выбрать Управление: Надстройки Excel, нажать Перейти… Отметить флагом Поиск решения, нажать ОК
На ленте на вкладке Данные появился Поиск решения:
Теперь нужно придумать, как мы можем параметрами в разных строках “подбирать” сумму. Я сделал вариант, когда в столбце мы указываем множитель 0 или 1, в соседнем столбце считаем произведение, и потом значения складываем в итоговую сумму:
- в ячейках столбца B указываем 0 или 1 (сейчас неважно, что конкретно)
- в ячейке C4 формула =A4*B4
- в ячейках C5:C14 – аналогично, с учетом номера строки
- в ячейке C3 формула =СУММ(C4:C14)
Теперь запускаем Поиск решения. И заполняем:
- Оптимизировать целевую функцию: $C$3
- До: Значения: 10
- Изменяя ячейки переменных: $B$4:$B$14
- В соответствии с ограничениями: – добавляем (кнопка Добавить) три условия: 1) значения переменных должны быть целые, 2) значения должны быть >= 0, 3) 2) значения должны быть <= 1
- Выберите метод решения: Эволюционный поиск решения (выбираем это значение, т.к. у нас негладкая задача)
И нажимаем Найти решение.
Excel начинает что-то активно считать. В строке состояния меняются значения. При этом надо понимать, что чем больше самих переменных используется, и чем больше их значений возможно – тем дольше будет выполняться поиск решения. Поэтому критерии поиска, в том числе и ограничение по времени, можно задавать нажав на кнопку Параметры в окне Параметры поиска решений.
Через какое-то время отобразится окно Результаты поиска решений. В данном примере всё срослось удачно и решение было найдено:
Снимаем флаг Вернулься в диалоговое окно параметров (если установлено), нажимаем ОК и любуемся результатом.
Если немного изменить начальные данные, то можно получить другие результаты:
Если точный результат не может быть найден, то выдается такое сообщение:
А иногда случается так, что этот функционал вообще не может найти подходящее решение (скорее всего из-за очень большого количества вариантов, которые он просто не успевает перебрать), и сообщает следуещее:
В таком случае иногда помогает изменить немного подход к поиску результата, и искать не конкретное значение, а Минимум отклонения. В нашем примере делаем так:
- в ячейке C3 формула =ABS(СУММ(C4:C14)-B1)
А в Параметры поиска решений
- До: Минимум
И мы получаем прекрасный результат!
- Блог пользователя d.mokshin
Например, у меня есть следующий список чисел, и теперь я хочу знать, какая комбинация чисел в списке дает в сумме 480, на следующем скриншоте вы можете увидеть, что есть пять групп возможных комбинаций, которые в сумме равны до 480, например, 300 + 60 + 120, 300 + 60 + 40 + 80 и т. д. В этой статье я расскажу о некоторых методах определения суммирования ячеек до определенного значения в Excel.
- Найдите комбинацию ячеек, равную заданной сумме, с помощью формул
- Найдите комбинацию ячеек, которая равна заданной сумме, с помощью надстройки Solver
- Найдите комбинацию ячеек, которая равна заданной сумме, с помощью функции, определяемой пользователем
- Найдите все комбинации, которые равны заданной сумме, с удивительной функцией
Найдите комбинацию ячеек, равную заданной сумме, с помощью формул
Во-первых, вам нужно создать несколько имен диапазонов, а затем применить формулу массива, чтобы найти ячейки, которые суммируются с целевым значением, выполните следующие действия шаг за шагом:
1. Выберите список номеров и определите для этого списка имя диапазона – Range1 в Имя Box, и нажмите Enter ключ для завершения определения имени диапазона, см. снимок экрана:
2. После определения имени диапазона для списка номеров вам необходимо создать еще два имени диапазона в Менеджер имен коробка, пожалуйста, нажмите Формулы > Менеджер имен, В Менеджер имен диалоговое окно, нажмите Новинки кнопку см. скриншоты:
3. В выскочившем Новое имя диалоговое окно введите имя List1 в Имя и фамилия поле и введите эту формулу = СТРОКА (КОСВЕННАЯ (“1:” & СТРОКА (Диапазон1))) (Range1 это имя диапазона, которое вы создали на шаге 1) в Относится к поле, см. снимок экрана:
4. Нажмите OK вернуться к Менеджер имен диалоговое окно, затем продолжайте щелкать Новинки кнопку, чтобы создать другое имя диапазона, в Новое имя диалоговое окно введите имя List2 в Имя и фамилия поле и введите эту формулу = СТРОКА (КОСВЕННАЯ (“1:” & 2 ^ СТРОКА (Диапазон1))) (Range1 это имя диапазона, которое вы создали на шаге 1) в Относится к поле, см. снимок экрана:
5. После создания имен диапазонов примените следующую формулу массива к ячейке B2:
=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),”X”,””), и нажмите Shift + Ctrl + Enter вместе, затем перетащите дескриптор заполнения вниз в ячейку B8, последнее число в списке, и вы увидите, что числа, общая сумма которых составляет 480, помечены как X в столбце B, см. снимок экрана:
- Ноты:
- В приведенной выше длинной формуле: List1, List2 и Range1 имена диапазонов, которые вы создали на предыдущих этапах, C2 – это конкретное значение, к которому вы хотите добавить числа.
- Если несколько комбинаций значений имеют сумму, равную определенному значению, отображается только одна комбинация.
Быстрый и простой поиск и перечисление всех комбинаций, равных заданной сумме, в Excel
Kutools for Excel‘s Составить номер Утилита может помочь вам быстро и легко найти и перечислить все комбинации и конкретные комбинации, которые равны заданной сумме. Нажмите, чтобы скачать Kutools for Excel!
Найдите комбинацию ячеек, которая равна заданной сумме, с помощью надстройки Solver
Если вы не уверены в приведенном выше методе, Excel содержит Надстройка Solver функция, используя эту надстройку, вы также можете определить числа, общая сумма которых равна заданному значению.
1. Во-первых, вам нужно активировать это решающее устройство добавить в, перейдите к Файл > Опции, В Параметры Excel диалоговое окно, нажмите Надстройки на левой панели, а затем щелкните Надстройка Solver из Неактивные надстройки приложений раздел, см. снимок экрана:
2. Затем нажмите Go для входа в Надстройки диалог, проверьте Надстройка Solver и нажмите OK для успешной установки этой надстройки.
3. После активации надстройки Solver вам необходимо ввести эту формулу в ячейку B9: = СУММПРОИЗВ (B2: B9; A2: A9)(B2: B9 – это пустые ячейки столбца рядом со списком номеров, и A2: A9 это список номеров, который вы используете. ) и нажмите Enter ключ, см. снимок экрана:
4. Затем нажмите Данные > решающее устройство , чтобы перейти к Параметр решателя диалоговое окно, в диалоговом окне выполните следующие операции:
(1.) Щелкните 
кнопка для выбора ячейки B10 где ваша формула из Установить цель раздел;
(2.) Тогда в к раздел, выберите Значение, и введите целевое значение 480 как вам нужно;
(3.) Под Изменяя ячейки переменных раздел, нажмите кнопка для выбора диапазона ячеек B2: B9 где будут отмечены ваши соответствующие числа.
5. Затем нажмите Добавить кнопку, чтобы перейти к Добавить ограничение диалоговое окно, нажмите кнопка для выбора диапазона ячеек B2: B9И выберите бункер из выпадающего списка см. снимок экрана:
6. Нажмите OK вернуться в Параметр решателя диалоговое окно, затем щелкните Решить кнопку, через несколько минут Результаты решателя появится диалоговое окно, и вы увидите, что комбинация ячеек, равная заданной сумме 480, помечена как 1. В поле Результаты решателя диалог, пожалуйста, выберите Сохранить решение для решения и нажмите OK для выхода из диалога. Смотрите скриншот:
Внимание: Этот метод также позволяет получить только одну комбинацию ячеек, если существует более одной комбинации значений, сумма которой равна определенному значению.
Найдите комбинацию ячеек, которая равна заданной сумме, с помощью функции, определяемой пользователем
Первые два метода являются сложными для большинства наших пользователей Excel, здесь я могу создать код VBA, чтобы быстро и легко решить эту задачу.
Чтобы получить правильный результат, вы должны сначала отсортировать список номеров в порядке убывания. А затем проделайте следующие шаги:
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: найти комбинацию ячеек, равную заданной сумме:
Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice
Dim xStr As String
Dim xSum As Double
Dim xCell As Range
xSum = SumCellId
For Each xCell In CoinsRange
If Not (xSum / xCell < 1) Then
xStr = xStr & Int(xSum / xCell) & " of " & xCell & " "
xSum = xSum - (Int(xSum / xCell)) * xCell
End If
Next
GetCombination = xStr
End Function
3. Затем сохраните и закройте это окно кода, а затем вернитесь на рабочий лист и введите эту формулу = getcombination (A2: A9, C2) в пустую ячейку и нажмите Enter key, вы получите следующий результат, который отображает комбинационные числа, равные заданной сумме, см. снимок экрана:
- Ноты:
- В приведенной выше формуле A2: A9 – диапазон номеров, а C2 содержит целевое значение, которому вы хотите равняться.
- Если несколько комбинаций значений имеют сумму, равную определенному значению, отображается только одна комбинация.
Найдите все комбинации, которые равны заданной сумме, с удивительной функцией
Возможно, все вышеперечисленные методы несколько сложны для вас, здесь я представлю мощный инструмент, Kutools for Excel, С его Составить номер функция, вы можете быстро получить все комбинации, равные заданной сумме.
Советы:Чтобы применить это Составить номер функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Кутулс > Содержание > Составить номер, см. снимок экрана:
2, Затем в Составьте число диалоговое окно, щелкните 
кнопку, чтобы выбрать список номеров, который вы хотите использовать, из Источник данных, а затем введите общее число в Сумма текстовое поле, см. снимок экрана:
3. А затем нажмите OK Кнопка, появится окно с подсказкой, чтобы напомнить вам выбрать ячейку, чтобы найти результат, см. снимок экрана:
4. Затем щелкните OK, и теперь все комбинации, которые равны данному номеру, были отображены, как показано на скриншоте ниже:
Нажмите, чтобы скачать Kutools for Excel и бесплатная пробная версия прямо сейчас!
Демонстрация: найдите комбинацию ячеек, которая равна заданной сумме в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Онлайн калькулятор находит число из которого был вычтен заданный процент.
Зачастую используется для решения задач финансового и бухгалтерского характера, для нахождения суммы до вычета НДС или НДФЛ.
Суть расчетов, в которых используется процент от суммы, заключается в том, что этот процент заложен в 100% изначального числа, которое состоит из суммы различных его частей.
По заданной части и проценту необходимо найти сам процент в единичном выражении. Для этого от 100% нужно отнять заданный процент, затем разделить заданное число на полученный процент и найти значение 1%. Умножив его на первоначальный процент, найдем искомую величину.
Пример: Сотрудником была получена зарплата в размере 40000 рублей. Эта сумма является остатком заработной платы по договору после вычета 13% подоходного налога. Необходимо найти размер подоходного налога в денежном выражении, вычтенного из зарплаты.
Решение: Заработная плата, выданная сотруднику, составляет 100%-13%=87%. Один процент от 40000 рублей составляет 40000/87=459.77 рубля. Тогда 13% будут равны 459.77×13=5977.01 рублей.
×
Пожалуйста напишите с чем связна такая низкая оценка:
×
Для установки калькулятора на iPhone – просто добавьте страницу
«На главный экран»
Для установки калькулятора на Android – просто добавьте страницу
«На главный экран»
- сосчитать необходимые дополнения до X для каждого элемента. Этой операциии не избежать. Получится убывающий ряд.
- двигаемся по обоим рядам от меньших к большим, пока не находится равенство. Тогда пара найдена – значение и X минус значение.
Т.о. один раз пройти, вычисляя дополнения до X, и макс. 2 раза, разыскивая совпадения. И по результатам, исключить дубли зеркальных пар, как (-3,15) в примере ниже.
Недостаток – нужно хранилище для 2 x размер данных. Это оптимизируется, но не будем усложнять.
Пример:
X = 12, исходный ряд: -3 1 3 4 7 9 12 15
-3 1 3 4 7 9 12 15
дополнения до X:
15 11 9 8 5 3 0 -3
двигаем пошагово указатель на текущий элемент в каждом ряду
от меньших к большим,
если бОльшее значение в одном ряду, в другом ряду берём следующее,
если равны, то для след. шага сравнения берём по след. значению в обоих
-3 1 3 4 7 8 12 15
-3 0 3 5 8 9 11 15
Находим -3, значит, пара (-3, 12 - -3 = 15);
второе совпадение (3, 12-3=9);
третье совпадение (15, 12-15=-3);
Надо ещё пройтись по результатам и убрать зеркальные дубли пар, если есть (тут один случай).
Upd не рассмотрел случай, если дополнение = значению и повтор одинаковых значений в исходном массиве, напр. [1,3,3,4,4].
