Андрей Смирнов
Время чтения: ~23 мин.
Просмотров: 1

Как преобразовать текст в число в excel

Генератор случайных чисел в Excel в диапазоне с помощью функции СЛУЧМЕЖДУ

СЛУЧМЕЖДУ – это еще одна функция в Excel для создания генератора случайных чисел.. Она возвращает случайные целые числа в указанном диапазоне:

СЛУЧМЕЖДУ (нижняя граница; верхняя граница)

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

Подобно СЛЧИС, СЛУЧМЕЖДУ в Excel является изменчивой функцией, и она также возвращает новое случайное целое число каждый раз, когда ваша таблица пересчитывается или изменяется.

Например, того чтобы сделать генератор случайных целых чисел от 10 до 50 (включая 10 и 50) используйте следующую формулу СЛУЧМЕЖДУ:

= СЛУЧМЕЖДУ(10; 50)

Генератор случайных чисел в Excel – Генерация случайных чисел в заданном диапазоне

Функция СЛУЧМЕЖДУ в Excel может создавать как положительные, так и отрицательные числа случайные числа. Например, чтобы получить список случайных чисел от -10 до 10, введите следующую формулу на листе:

=СЛУЧМЕЖДУ(-10;10)

Функция СЛУЧМЕЖДУ доступна в следующих версиях: Excel 2016, Excel 2013, Excel 2010 и Excel 2007.

В более ранней версии Excel 2003, вы можете использовать формулу СЛЧИС, рассмотренную выше.

Далее в этой статье вы найдете еще несколько примеров формул, демонстрирующих, как использовать функцию СЛУЧМЕЖДУ для создания генератора случайных чисел, отличных от целых.

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

Хотя функция СЛУЧМЕЖДУ в Excel была предназначена для генерации случайных целых чисел, вы можете использовать ее для генерации случайных десятичных чисел с таким количеством десятичных знаков, сколько хотите.

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

= СЛУЧМЕЖДУ(нижняя граница*10; верхняя граница*10)/10

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

Следующая формула СЛУЧМЕЖДУ возвращает случайные десятичные числа от 1 до 50:

= СЛУЧМЕЖДУ(1*10;50*10)/10

Генератор случайных чисел в Excel – Генерация случайных чисел с одним знаком после запятой

Аналогичным образом, чтобы сделать генератор случайных чисел от 1 до 50 с двумя знаками после запятой, вы умножаете аргументы функции СЛУЧМЕЖДУ на 100, а затем делите результат на 100:

=СЛУЧМЕЖДУ(1*100; 50*100)/100

Генератор случайных чисел в Excel – Генерация случайных чисел с двумя знаками после запятой

Как извлечь дату из сложного текста при помощи формул?

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

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

Давайте попробуем вытащить дату из пояснения к бухгалтерским проводкам. Как видите, варианты написания могут быть самые разнообразные. Единственное условие – день должен быть записан в виде двузначного числа: 07 в нашем случае.

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

если у вас двумя цифрами обозначены день и месяц, и четырьмя – год:

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

А если сделать так, чтобы и внешний вид извлеченной даты также устанавливался не вручную, а при помощи формулы? Здесь нам поможет функция ТЕКСТ.

Самый базовый вариант представления выглядит так:

Как видите, вся хитрость заключается в правильном описании формата во втором аргументе. «DD MMMM YYYY» означает число, месяц в именительном падеже и год из четырёх цифр. А вот «DD MMMM YYYY» сделает то же самое, но месяц будет в родительном падеже (7 ноября). Остальные варианты вы видите на рисунке выше. Можете сами выбрать тот, который больше подходит для ваших задач.

Ну а если день у вас может быть записан и одной цифрой, и двузначным числом? И здесь есть универсальный вариант. Правда, более сложный.

Здесь применяем формулу массива.

Естественно, скобки вводить не нужно: они появятся автоматически после того, как вы в конце ввода формулы нажмете комбинацию клавиш CRTR+SHIFT+ENTER.

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

К примеру, чтобы сразу получить «07 ноября 2019 г.», можно изменить наше выражение следующим образом:

И не забывайте про формулу массива!

Как преобразовать число в текст?

Вводить числа как текст в Excel приходится достаточно часто. Например:

  1. Номер фактуры или договора состоит из более 12-ти чисел. Допустим номер: «1234567891012» Excel округляет большие числа в такой формат «1,23457E+12».
  2. Некоторые номера телефонов автоматически конвертируются в дату. Номер телефона: 2012-10-17 отображается как «17.10.2012».
  3. Иногда нужно записать формулу обычным текстом.

Поэтому важно научиться управлять форматами ячеек. Попробуйте заполнить табличку, так как показано на рисунке:

Попробуйте заполнить табличку, так как показано на рисунке:

Как видно на примере проблема возникает при введении текста «10/12». После нажатия клавиши «Enter» получаем дату:

Необходимо заставить Excel распознавать числа как текст.

Решение 1. В ячейке D2 введите перед текстовым значением символ апострофа «’» (апостроф следует ввести с английской раскладки клавиатуры). Теперь число преобразовано в текстовое значение.

Решение 2. Задайте в ячейке D2 текстовый формат. Для этого откройте окно «Формат ячеек» CTRL+1. На вкладке «Число» выберите из списка «Числовые формат» опцию «Текстовый» и нажмите ОК. Теперь можно вводить любые числа как текст без апострофа.

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

Примечание. Второе решение закрепляет за ячейкой ее формат, и любые другие типы данных будут преобразованы, пока пользователь не изменит для нее другие форматы (например, «Общий» — по умолчанию).

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

Если в исходном столбце содержаться значения в ячейках с разным форматом валют тогда нам потребуется распознать все форматы в каждой ячейке. По умолчанию в Excel нет стандартной функции, которая умеет распознавать и возвращать форматы ячеек. Поэтому напишем свою пользовательскую макрофункцию и добавим ее в нашу формулу. Макрофункция будет называться ВЗЯТЬФОРМАТ (или можете назвать ее по-своему). Исходный VBA-код макрофункции выглядит так:

Скопируйте его в модуль («Insert»-«Module») VBA-редактора (ALT+F11). При необходимости прочитайте:

Теперь изменяем нашу формулу и получаем максимально эффективный результат:

Благодаря функции ВЗЯТЬФОРМАТ написанной на VBA-макросе мы просто берем значение и формат из исходной ячейки и подставляем его как текстовую строку. Так наша формула с пользовательской макрофункцией ВЗЯТЬФОРМАТ автоматически определяет валюту для каждой суммы в исходных значениях ячеек.

Как преобразовать дату в текст в Excel

В Excel каждой дате соответствует определенное число дней, прошедших с принятой точки отсчета – 1 января 1900 года. Функция ДАТАЗНАЧ возвращает число, соответствующее числовому представлению даты, которая указана в виде текста, с учетом указанной выше особенности хранения дат в Excel. Формат возвращаемого значения зависит от настроек формата ячейки, в которой будет выведен результат вычислений.

Зачастую даты в Excel записывают без использования функции ДАТА. Табличный редактор определяет такие значения как обычные текстовые строки. Поэтому процедуры форматирования, сортировки по дате, а также различные вычисления (например, разница дат) приводят к некорректным результатам или появлению ошибок. Поэтому функция ДАТАЗНАЧ полезна для преобразования текстовых значений к данным формата Дата.

Пример 1. В таблице Excel находится столбец, в котором хранятся даты как текстовые строки, при этом записи имеют вид: «28 сентября 2018 года». Преобразовать эти значения в данные формата Дата.

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

Для получения даты в формате, поддерживаемом Excel, используем следующую функцию:

Единственный аргумент состоит из подстрок, склеенных амперсандами (&):

Функция ЛЕВСИМВ возвращает номер дня (первые два символа строки, содержащейся в ячейке A2)

Очень важно, чтобы однозначные номера дней (например, 8 апреля) записывались как 08 апреля (имели нуль в начале), иначе будет возникать ошибка.
Комбинация функций ПСТР и ЛЕВСИМВ выделяет из строки три первых символа названия месяца и возвращает их.
Комбинация функций ПСТР и ПРАВСИМВ выделяет 4 символа, соответствующие числовому представлению года.. Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:

Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:

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

Функция разделения текста в Excel

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

  • ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков с начала ячейки;
  • ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество знаков с конца ячейки;
  • ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо

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

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

В первой строке есть только имя и фамилия, разделенные пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества знаков – используется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.

Формула для извлечения фамилии:

С помощью функции ПОИСК Excel определяет количество знаков для функции ПРАВСИМВ. Функция ДЛСТР «считает» общую длину текста. Затем отнимается количество знаков до первого пробела (найденное ПОИСКом).

Вторая строка содержит имя, отчество и фамилию. Для имени используем такую же формулу:

Формула для извлечения фамилии несколько иная: Это пять знаков справа. Вложенные функции ПОИСК ищут второй и третий пробелы в строке. ПОИСК(» «;A3;1) находит первый пробел слева (перед отчеством). К найденному результату добавляем единицу (+1). Получаем ту позицию, с которой будем искать второй пробел.

Часть формулы – ПОИСК(» «;A3;ПОИСК(» «;A3;1)+1) – находит второй пробел. Это будет конечная позиция отчества.

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

Формула «для отчества» строится по тем же принципам:

Как преобразовать текст в дату при помощи формул.

Разберем еще один способ как превратить сложную формулировку в дату. В колонке А записаны датировки как текстовые выражения вида «08 марта 2020г.». Сразу отметим, что записи должны быть единообразны, то есть все числа сохранены в двузначном виде: 01, 07, 08, 10, 12 и т.д.

Используем формулы работы с символьными значениями — ЛЕВСИМВ, ПРАВСИМВ, ПСТР. Они позволяют извлечь определенное количество символов  с начала текстовой строки, с конца или с середины.

Наша задача – разобрать текст на части, из которых затем «склеить» дату.

Используем формулу:

Давайте разбираться, как это работает.

ЛЕВСИМВ(A2;2) — извлекаем 2 первых символа, то есть число.

ПСТР(ЛЕВСИМВ(A2;6);4;3) — получаем 3 первых буквы названия месяца.

ПСТР(ПРАВСИМВ(A2;6);1;4) — выводим 4 цифры года.

И объединяем все это при помощи оператора &, вставляя между этими частями тире.

Можно записать и чуть иначе:

В английской версии:

Мастер преобразования текста в столбцы — не требующий формул способ преобразования текста в дату.

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

Пример 1. Преобразование простых текстовых строк в даты.

В этом примере мы будем превращать текстовые строки, например 01 01 2015 (день, месяц и год разделены пробелами), в даты.

  1. На рабочем листе выберите столбец, содержимое которого вы хотите конвертировать.
  2. Перейдите на вкладку «Данные», и нажмите «Текст по столбцам».
  1. На шаге 1 мастера преобразования текста в столбцы выберите «С разделителями» и нажмите «Далее».
  2. На шаге 2 мастера снимите флажки со всех разделителей и нажмите Далее.
  3. На последнем шаге выберите «Дата» в разделе «Формат данных столбца», укажите в выпадающем списке вид, соответствующий вашим данным.  Нажмите «Готово».

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

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

Заметка. Для правильной работы мастера «Текст по столбцам» все записи должны быть единообразными. Например, если некоторые из них выглядят как день / месяц / год , а другие — месяц / день / год , вы получите неверные результаты.

Пример 2. Преобразование сложных текстовых строк в даты.

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

  • 8 март 2020 г., воскресенье
  • 8 март 2020 г. 15:00

Вам нужно будет приложить немного больше усилий и использовать рассматриваемый нами инструмент в комбинации с функцией ДАТА.

Важно! Название месяца при использовании этого метода должно быть в именительном падеже!

  1. Выберите нужную область данных.
  2. Вызовите мастер преобразования.
  3. На шаге 1 укажите «С разделителями» и нажмите « Далее» .
  4. На шаге 2 мастера выберите разделители, которые у вас используются.

Например, если вы преобразуете строки, разделенные запятыми и пробелами, например «8 март 2020г., воскресенье», то вам следует выбрать оба разделителя — запятую и пробел.

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

Я немного усложнил наш пример, добавив после порядкового номера года еще букву «г» с точкой. Поэтому эта буква и добавлена в список разделителей.

И, наконец, взгляните на окно Образец разбора данных и проверьте, правильно ли поделены ваши слова на столбцы. Затем нажмите «Далее».

  1. На шаге 3 мастера убедитесь, что все колонки в разделе предварительного просмотра данных имеют формат «Общий». Если это не так, щелкните столбец и выберите Общий в разделе Формат данных.

Заметка. Не выбирайте формат «Дата», поскольку каждый столбец содержит только ее часть. Поэтому Excel не сможет понять, что вы на самом деле хотите сделать.

Если вам не нужен какой-то из столбцов, щелкните по нему и выберите «Пропустить

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

Если вы не хотите перезаписывать исходные данные, укажите, где именно должны быть вставлены результаты — введите адрес верхней левой ячейки в поле «Поместить в…». Выбираем ячейку B2.

Когда закончите, нажмите кнопку Готово.Как вы видите на скриншоте выше, мы пропускаем четвертую и пятую колонки с точкой и днями недели, разделяем остальные данные на 3 столбца (в общем формате) и вставляем их, начиная с ячейки В2.

На следующем рисунке показан результат с исходными данными в A и разделенными на части данными в В, C и D.

  1. Наконец, вы должны объединить эти кусочки вместе, используя функцию ДАТА (DATE). Синтаксис её не требует особых пояснений:

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

Но не так просто с месяцем, потому что это слово, а нужен его номер. К счастью, Microsoft Excel предоставляет специальную функцию МЕСЯЦ (MONTH), которая может изменить название месяца на его порядковый номер:

Чтобы функция МЕСЯЦ понимала, что она имеет дело с датой, мы выражаем это так:

Здесь C2 содержит название месяца, март в нашем случае. «1 &» добавляется для получения даты (1 март), чтобы формула могла превратить ее в соответствующий номер месяца. Повторюсь еще раз, что месяц должен быть здесь в именительном падеже, что не всегда удобно.

А теперь давайте встроим это выражение в качестве второго аргумента:

И вуаля, наши сложные комбинации слов и цифр успешно становятся датами:

Как преобразовать текст в число в Эксель?

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

Зеленый уголок-индикатор

О том, что числовой формат был преобразован в текстовый, говорит появление своеобразного уголка-индикатора. Его появление – это своеобразная удача, поскольку достаточно выделить все, кликнуть по всплывающему значку и нажать на «Преобразовать в число».

1

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

Повторный ввод

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

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

Формула преобразования текста в число

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

2

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

Логично, что можно использовать любую подобную операцию, после которых изменения значения нет. Например, добавить и вычесть единицу или разделить на 1. Результат будет аналогичным.

Специальная вставка

Это очень старый метод, который использовался в первых версиях Excel (поскольку зеленый индикатор добавили лишь в 2003-й версии). Наши действия следующие:

  1. Ввести единицу в любую ячейку, не содержащую никаких значений.
  2. Скопировать ее. 
  3. Выделить ячейки с записанными в текстовом формате числами и изменить его на числовой. На этом этапе ничего не изменится, поэтому выполняем дальнейший этап.
  4. Вызвать меню и воспользоваться «Специальной вставкой» или Ctrl + Alt + V.
  5. Откроется окно, в котором нас интересует радиокнопка «значения», «умножить».

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

Инструмент «Текст по столбцам»

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

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

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

4

Осталось только кликнуть на «Готово», как текстовое значение немедленно превратится в полноценное число.

Макрос «Текст – число»

Если часто нужно совершать такие операции, то рекомендуется этот процесс сделать автоматическим. Для этого существуют специальные исполняемые модули – макросы. Чтобы открыть редактор, существует комбинация Alt+F11. Также к нему можно получить доступ через вкладку «Разработчик». Там вы найдете кнопку «Visual Basic», которую и нужно нажать.

Наша следующая задача – вставить новый модуль. Чтобы это сделать, нужно открыть меню Insert – Module. Далее нужно скопировать этот фрагмент кода и вставить в редактор стандартным способом (Ctrl + C и Ctrl + V).

Sub Convert_Text_to_Numbers()

    Selection.NumberFormat = “General”

    Selection.Value = Selection.Value

End Sub

Для выполнения каких-либо действий с диапазоном, его следует предварительно выделить. После этого надо запустить макрос. Делается это через вкладку «Разработчик – Макросы». Появится перечень подпрограмм, которые можно выполнять в документе. Нужно выбрать ту, которая надо нам и нажать на «Выполнить». Далее программа все сделает за вас. 

Отформатировать число как текст в Excel с денежным форматом ячейки

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

Обратите внимание, что каждое число после объединения с текстом (в столбце D) не сохраняет свой денежный формат, который определен в исходной ячейке (столбца B). Чтобы решить данную задачу, необходимо поместить ссылку к ячейкам с числами денежных сумм в функцию ТЕКСТ

Благодаря ей можно форматировать числовые значения прямо в текстовой строке. Формула изображена на рисунке:

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

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

Функция ТЕКСТ требует 2 обязательных для заполнения аргумента:

  1. Значение – ссылка на исходное число (в данном случае).
  2. Формат – текстовый код формата ячеек Excel (может быть пользовательский).

Число можно форматировать любым способом, важно лишь соблюдать правила оформления форматов, который должен распознаваться в Excel. Например, ниже заполненная аргументами функция ТЕКСТ возвращает число в денежном формате пересчитанному по курсу 68 руб./1$:

Например, ниже заполненная аргументами функция ТЕКСТ возвращает число в денежном формате пересчитанному по курсу 68 руб./1$:

Измененная ниже формула возвращает долю в процентах от общей выручки:

Простой способ проверки синтаксиса кода формата, который распознает Excel – это использование окна «Формат ячеек». Для этого:

  1. Щелкните правой кнопкой мышки по любой ячейке с числом и выберите из появившегося контекстного меню опцию «Формат ячейки». Или нажмите комбинацию горячих клавиш CTRL+1.
  2. Перейдите на вкладку «Число».
  3. В секции «Числовые форматы:» выберите категорию «(все форматы)».
  4. Введите в поле «Тип:» свой пользовательский код формата и в секции «Образец» наблюдайте как он будет распознан в Excel и отображен в ячейке.

Как определить наиболее часто встречающееся число

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

Если таких чисел несколько – будет выведено то, которое раньше других встречается в списке. Функция работает только с числовыми данными.

Часто встречающееся число

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

Макрос «Текст – число»

Преобразовать числа, сохраненные как текст, в числа можно с помощью макроса.

Есть набор значений, сохраненных в текстовом формате:

Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:

Sub Conv()
    With ActiveSheet.UsedRange
        arr = .Value
        .NumberFormat = "General"
        .Value = arr
    End With
End Sub

Чтобы он «заработал», нужно сохранить. Но книга Excel должна быть сохранена в формате с поддержкой макросов.

Теперь возвращаемся на страницу с цифрами. Выделяем столбец с данными. Нажимаем кнопку «Макросы». В открывшемся окне – список доступных для данной книги макросов. Выбираем нужный. Жмем «Выполнить».

Цифры переместились вправо.

Следовательно, значения в ячейках «стали» числами.

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

Sub Conv()
    With ActiveSheet.UsedRange
        .Replace ",","."        
        arr = .Value
        .NumberFormat = "General"
        .Value = arr
    End With
End Sub

Читайте так же: как перевести число и сумму прописью в Excel.

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

Как рассчитать проценты по кредиту при помощи Excel

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

Вторая – дифференцированная, где ежемесячные платежи уменьшаются.

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

  1. Создать таблицу с исходными данными.
  2. Создать таблицу платежей. Пока никакой точной информации в ней не будет.
  3. Ввести формулу =ПЛТ($B$3/12; $B$4; $B$2) в первую ячейку. При этом используем абсолютные ссылки.

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

15

В первый месяц остаток задолженности будет таким же, как и размер кредита. Далее для его расчета нужно использовать формулу =ЕСЛИ(D10>$B$4;0;E9-G9), соответственно нашей табличке. 

Для расчета выплаты процента нужно использовать эту формулу: =E9*($B$3/12).

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

Математические операции.

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

Что это может быть? Например, сложение с нулём, умножение или деление на 1.

=A2+0

=A2*1

=A2/1

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

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

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

Среднее значение по условию

Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().

Найти среднее арифметическое чисел, которые больше или равны 10.

Функция: =СРЗНАЧЕСЛИ(A1:A8;»>=10″)

Третий аргумент – «Диапазон усреднения» — опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.

Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку

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

Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово «столы»). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.

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

Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно

Расчет процентов от суммы в Эксель

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

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

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

Например, если значения отображаются в колонке B, а результирующий показатель – в ячейке B10, то наша формула будет иметь такой вид.

=B2/$B$10

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

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

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

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

2

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

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

=СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)/общая сумма

Здесь в колонке А записаны наименования товаров, которые друг с другом вместе образуют диапазон. В колонке B описана информация о диапазоне суммирования, которым выступает общее количество доставленных товаров. Условие записано в E1, им выступает наименование продукции, на какую ориентируется программа при определении процента.

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

3

Также возможно написать наименование непосредственно в формулу.

=СУММЕСЛИ(A2:A9;”cherries”;B2:B9)/$B$10

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

  1. Каждый из товаров суммируется между собой.
  2. Потом получившийся результат разделяется на общее значение.

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

=(СУММЕСЛИ(A2:A9;”cherries”;B2:B9)+СУММЕСЛИ(A2:A9;”apples”;B2:B9))/$B$10

Рейтинг автора
5
Материал подготовил
Максим Иванов
Наш эксперт
Написано статей
129
Ссылка на основную публикацию
Похожие публикации