Разбить число на слагаемые эксель

Обновлено: 07.07.2024

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

Рассмотрим несколько способов автоматизации этой процедуры в среде электронных таблиц. Будем использовать VBA (Visual Basic for Applications – язык программирования, встроенный в линейку продуктов Microsoft Office).

1. Кнопка-макрос «Разложение на простые множители»

Файл с макросом на VBA, который выводит все простые множители данного натурального числа можно скачать

Опишем макрос. Исходим из следующих предположений. Анализируемое число вводится в ячейку А1. Результат разложения выводится в два столбика (стандартным для школьной математики способом). Первый столбик (столбец А, начиная со строки №3) – частное от деления данного числа на простые множители, начиная с самого числа до 1 включительно. Второй столбик (столбец В, начиная со строки №3) – простые множители по возрастанию. Программный код процедуры:

Макрос назвали ProstMnog. Для его вызова можно назначить «горячие клавиши», если открыть окно диалога «Макросы», нажать в нем кнопку «Параметры…» и в открывшемся окне диалога «Параметры макроса» указать комбинацию клавиш. Подразумевается, что лист книги, на котором происходят вычисления, имеет имя L1.

2. Пользовательская функция «Минимальный делитель»

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

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

Программный код пользовательской функции «Минимальный делитель»:

Function MinDel(n As Integer) As Integer
If n < 2 Then
MinDel = 1
Else
i = 1
Do
i = i + 1
MinDel = i
Loop Until n Mod i = 0
End If
End Function

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

При использовании файла необходимо включить макросы – нажать кнопку «Параметры», выбрать пункт «Включить это содержимое» и нажать кнопку «Ок»

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

Чтобы разложить конкретное натуральное число на простые множители необходимо:
- ввести это число в какую-нибудь ячейку, например, в ячейку А1
- в ячейке В1 ввести формулу =MinDel(A1)
- в ячейке А2 ввести формулу =А1/В1
- в ячейке В2 ввести формулу =MinDel(A2)

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

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

Для разработки пользовательской функции в MS Excel следует сохранить книгу в формате с поддержкой макросов (*.xlsm), открыть редактор Microsoft Visual Basic (Alt+F11), в меню Insert выбрать пункт Module и ввести программный код.

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

Пример . «Назовем наибольшим делителем составного натурального числа его самый большой, не равный ему делитель. Наименьшим делителем назовем его самый маленький, не равный единице, делитель. Например, у числа 150 наибольший делитель равен 75, а наименьший – 2. Сколько существует различных составных натуральных чисел, у которых наибольший делитель ровно в 341 раз больше наименьшего?»

Расчет для решения задачи про минимальный и максимальный делитель Расчет для решения задачи про минимальный и максимальный делитель Режим отображения формул для задачи про минимальный и максимальный делитель Режим отображения формул для задачи про минимальный и максимальный делитель

В столбце А задаем минимальный делитель, на основе которого рассчитывается натуральное число удовлетворяющее условию задачи. Для этого в столбце С рассчитан максимальный делитель, превышающий минимальный в 341 раз. В столбце D рассчитываем число как произведение минимального делителя в столбце А и максимального делителя в столбце С. У полученного числа настоящий минимальный делитель может отличаться от заданного в первом столбце, поэтому в столбце Е с помощью пользовательской функции рассчитывается действительно минимальный делитель. В столбце F проводится проверка совпадения заданного минимального делителя в столбце А и рассчитанного минимального делителя в столбце Е.

Решение представляет собой численный эксперимент, проверено всего 20 чисел. Строго говоря пока нельзя утверждать, что ответ правильный, кроме того не предъявлено строгое аналитическое решение. Но результаты расчетов помогают понять, что после 11 строки в столбце Е повторяются простые числа, не превышающие 11. Возникает предположение, что это связано с простыми делителями числа 341 = 11 × 31. Поскольку каждое число, которое делится на 341, делится и на 11, то его минимальный делитель не может превышать 11. Это рассуждение и позволяет строго обосновать ответ.

Не очень частый, но и не экзотический случай. На моих тренингах такой вопрос задавали не один и не два раза :) Суть в том, что мы имеем конечный набор каких-то чисел, из которых надо выбрать те, что дадут в сумме заданное значение.

В реальной жизни эта задача может выглядеть по-разному.

Например, мы выгрузили из интернет-банка все платежи, которые поступили на наш счет за последний месяц. Один из клиентов разбивает сумму своего платежа на несколько отдельных счетов и платит частями. Мы знаем общую сумму оплаты и количество счетов, но не знаем их сумм. Надо подобрать те суммы в истории платежей, которые дадут в общем заданное значение.
У нас есть несколько рулонов стали (линолеума, бумаги. ), из которых надо подобрать под заказ те, что дадут заданную длину.
Блэкджек или в народе "очко". Надо набрать карты суммарной стоимостью максимально близкой к 21 баллу, но не превысить этот порог.

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

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

Способ 1. Надстройка Поиск решения (Solver)

Эта надстройка входит в стандартный набор пакета Microsoft Office вместе с Excel и предназначена, в общем случае, для решения линейных и нелинейных задач оптимизации при наличии списка ограничений. Чтобы ее подключить, необходимо:

в Excel 2007 и новее зайти Файл - Параметры Excel - Надстройки - Перейти (File - Excel Options - Add-ins - Go)
в Excel 2003 и старше - открыть меню Сервис - Надстройки (Tools - Add-ins)

и установить соответствующий флажок. Тогда на вкладке или в меню Данные (Data) появится нужная нам команда.

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

Диапазон A1:A20 содержит наши числа, из которых мы будем выбирать нужные, чтобы "вписаться" в заданную сумму.
Диапазон В1:B20 будет своего рода набором переключателей, т.е. будет содержать нули или единички, показывая, отбираем мы данное число в выборку или нет.
В ячейке E2 стоит обычная автосумма всех единичек по столбцу B, подсчитывающая кол-во выбранных чисел.
В ячейке E3 с помощью функции СУММПРОИЗВ (SUMPRODUCT) считается сумма попарных произведений ячеек из столбцов А и B (то есть A1*B1+A2*B2+A3*B3+. ). Фактически, здесь подсчитывается сумма чисел из столбца А, отобранных единичками из столбца В.
В розовую ячейку E4 пользователь вводит желаемую сумму для подбора.
В ячейке E5 вычисляется абсолютное по модулю значение погрешности подбора с целью ее будущей минимизации.
Все желтых ячейках Е8:E17 хотелось бы получить список отобранных чисел, т.е. тех чисел из столбца А, напротив которых в столбце В есть единички. Для этого необходимо выделить сразу все (!) желтые ячейки и в них ввести вот такую формулу массива:

=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$20;НАИМЕНЬШИЙ(ЕСЛИ(B1:B20=1;СТРОКА(B1:B20);"");СТРОКА()-СТРОКА($E$8)+1));"")

=IFERROR(INDEX($A$1:$A$20;SMALL(IF(B1:B20=1;ROW(B1:B20);"");ROW()-ROW($E$8)+1));"")

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

Теперь перейдем на вкладку (или в меню) Данные и запустим инструмент Поиск решения (Data - Solver):

В открывшемся окне необходимо:

Задать как целевую функцию (Target Cell) - ячейку вычисления погрешности подбора E5. Чуть ниже выбрать опцию - Минимум, т.к. мы хотим подобрать числа под заданную сумму с минимальной (а лучше даже нулевой) погрешностью.
В качестве изменяемых ячеек переменных (Changing cells) задать диапазон столбца переключателей B1:B20.
С помощью кнопки Добавить (Add) создать дополнительное условие на то, что ячейки диапазона B1:B20 должны быть бинарными (т.е. содержать только 0 или 1):

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

Теперь можно либо оставить найденное решение подбора (Сохранить найденное решение), либо откатиться к прежним значениям (Восстановить исходные значения).

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

Найденные комбинации можно сохранять виде сценариев (кнопка Сохранить сценарий), чтобы вернуться к нем позднее с помощью команды Данные - Анализ "что-если" - Диспетчер сценариев (Data - What-If Analysis - Scenario Manager):

И весьма удобно будет вывести все найденные решения, сохраненные в виде сценариев, в одной сравнительной таблице с помощью кнопки Отчет (Summary):

Способ 2. Макрос подбора

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

Для использования макроса нажмите сочетание Alt+F11, в открывшемся окне редактора Visual Basic вставьте новый модуль через меню Insert - Module и скопируйте туда этот код:

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

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

Для начал включим надстройку или проверим, что она включена (в Excel 2013): Файл / Параметры, раздел Надстройки, выбрать Управление: Надстройки Excel, нажать Перейти. Отметить флагом Поиск решения, нажать ОК

На ленте на вкладке Данные появился Поиск решения:

Теперь запускаем Поиск решения. И заполняем:

Не очень частый, но и не экзотический случай. На моих тренингах такой вопрос задавали не один и не два раза 🙂 Суть в том, что мы имеем конечный набор каких-то чисел, из которых надо выбрать те, что дадут в сумме заданное значение.

В реальной жизни эта задача может выглядеть по-разному.

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

Способ 1. Надстройка Поиск решения (Solver)

и установить соответствующий флажок. Тогда на вкладке или в меню Данные (Data) появится нужная нам команда.

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

В открывшемся окне необходимо:

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

Способ 2. Макрос подбора

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

и установить соответствующий флажок. Тогда на вкладке или в меню Данные появится нужная команда.
Способ 2. Макрос подбора

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

Разбейте или разделите номер на отдельные цифры с помощью формулы

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

1. Выберите пустую ячейку (говорит ячейка C1), чтобы найти первую разделенную цифру числа в ячейке A1, затем введите формулу = MID ($ A1; COLUMN () - (COLUMN ($ C1) - 1); 1) в строку формул, а затем нажмите Enter ключ.

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

2. Продолжайте выбирать ячейку C1, затем перетащите маркер заполнения в правые ячейки, пока все цифры ячейки A1 не будут разделены.

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

Разбейте или разделите номер на отдельные цифры с помощью Kutools for Excel

Освободи Себя Разделить клетки полезности Kutools for Excel помогает легко разделить все выбранные числовые ячейки на отдельные цифры сразу. Пожалуйста, сделайте следующее.

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

1. Выделите ячейки с номерами, которые нужно разбить на цифры, затем нажмите Kutools > Слияние и разделение > Разделить клетки. Смотрите скриншот:

2. в Разделить клетки диалоговое окно, выберите Разделить на столбцы вариант в Тип и в разделе Указать разделитель выберите Указать ширину и введите число 1 в текстовое поле. Щелкните значок OK кнопку.

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

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

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

Разделить слово или число на отдельные ячейки с помощью формулы

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

1. В пустой ячейке рядом с вашими данными, например C1, введите эту формулу = MID ($ A1; COLUMNS ($ A $ 1: A $ 1); 1) , см. снимок экрана:

2. Затем перетащите маркер заполнения в правые ячейки, пока не отобразятся пустые ячейки, а содержимое ячейки A1 не будет разделено на отдельные ячейки.

3. Затем, продолжая перетаскивать дескриптор заполнения вниз, в диапазон, в котором вы хотите применить эту формулу, и все данные в столбце A будут разделены по разным ячейкам по вашему желанию. Смотрите скриншот:

Разделить слово или число на отдельные ячейки с кодом VBA

Если вас интересует код VBA, следующий код также может оказать вам услугу.

1. Удерживайте ALT + F11 ключи в Excel, чтобы открыть Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: разделить слово на отдельные ячейки

3, Затем нажмите F5 ключ для запуска этого кода, и окно подсказки напомнит вам выбрать диапазон данных, который вы хотите использовать, см. снимок экрана:

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

5. Затем нажмите OK, а слова или числа в выбранных ячейках были разделены на отдельные символы. Смотрите скриншот:

Разделите слово или число на отдельные ячейки с помощью Kutools for Excel

Kutools for ExcelАвтора Разделить клетки feature - мощный инструмент, который может помочь вам разделить содержимое ячейки на отдельные столбцы или строки с определенными разделителями, в то же время он также может разделить текст и число на два столбца.

После установки kutools для Excel, пожалуйста, сделайте следующее:

1. Выделите ячейки, которые хотите разделить.

2. Затем нажмите Kutools > Слияние и разделение > Разделить клетки, см. снимок экрана:

3. В Разделить клетки диалоговое окно, выберите Разделить на столбцы под Тип, затем проверьте Укажите ширину под Разделить на раздел и введите номер 1 в текстовое поле, что означает разделение слова на отдельные ячейки по каждому символу. Смотрите скриншот:

4. Затем нажмите Ok При нажатии кнопки появляется другое окно подсказки, напоминающее о выборе ячейки для вывода результата, см. снимок экрана:

5, Затем нажмите OK, содержимое выделенной ячейки разделено на отдельные символы. Смотрите скриншот:

Разделите слово или число на отдельные ячейки с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Объединяйте отдельные буквы или цифры в одно слово или цифру

Если вы хотите объединить эти отдельные буквы ячеек в одно слово в отличие от вышеуказанных методов, Kutools for Excel's Сочетать утилита может помочь вам объединить их в одну ячейку так быстро, как вы можете.

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