Замена чисел словами в Excel

Поиск и замена данных в Excel — команда Найти и заменить

Добрый день, уважаемые читатели блога statanaliz.info.

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

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

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

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

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

Если же активна только одна ячейка, то по умолчанию поиск производится на всем листе Excel.

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

Для вывода результатов поиска предлагается два варианта: выводить все результаты сразу – кнопка «Найти все»; либо выводить по одному найденному значению – кнопка «Найти далее». На рисунке выше выделено красной рамкой.

После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия присутствия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки «Найти все» Excel вываливает все найденные ячейки.

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

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

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

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

Поиск нестрогого соответствия символов

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

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

А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах.

Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

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

Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В этом примере в поисковое поле достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч.

Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам и + что-еще + иванов. Данный прием значительно повышает эффективность поиска, т.к.

позволяет оперировать не точными критериями, а приблизительными.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к.

200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал.

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

А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д.

Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

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

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

Продвинутый поиск

{module 111}

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

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

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

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

Первый выпадающий список «Искать» предлагает ограничить поиск одним листом или расширить его до целой книги.

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

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

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

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

Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на «Значения» и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого.

е 0,167), то поиск символов «167» при выборе параметра «Формулы» эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе «Значения» поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – «Примечания». Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки «Учитывать регистр» и «Ячейка целиком». По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались.

Галочка «Ячейка целиком» также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100.

Зато, если установить галочку «Ячейка целиком», то все пойдет, как по маслу.

Поиск и замена данных

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

Либо нажать на горячую клавишу Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.

А как быстро удалить все разрывы строк? Обычно это делают вручную.

Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка.

В строке замены указываем, например, пробел.

Жмем Ok. Все переносы строк заменились пробелами.

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

Если что-то не ищется в ваших данных или наоборот, выдает слишком много лишних ячеек, то попробуйте уточнить поиск с помощью подстановочных символов «*» и «?» или настраиваемых параметров поиска.

Экономьте свое и чужое время.

Всех благ и до встречи на statanaliz.info.

в социальных сетях:

Источник: https://statanaliz.info/excel/upravlenie-dannymi/poisk-i-zamena-dannykh-v-excel/

Извлечение слов из текста в Excel

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

Важное замечание!

Перед применением описанных ниже функций убедитесь, что текст не содержит лишних пробелов (то есть пробелов в начале строки, в конце строки, двойных, тройных и т.д.). Убрать лишние пробелы можно формулой =СЖПРОБЕЛЫ(А1), где А1 — ячейка с текстом. Полученный результат можно скопировать и вставить значениями в исходную ячейку и в дальнейшем работать с «чистым» текстом. 

Задача 1. Подсчет количества слов в тексте

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

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

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

Функция ПОДСТАВИТЬ() имеет 4 аргумента:

1) Ячейка с текстом

2) То, что нужно заменить (указывается в кавычках)

3) То, на что нужно заменить (указывается в кавычка)

4) Необязательный аргумент — номер вхождения заменяемого текста. То есть, если в тексте 4 пробела, мы можем заменить только тот, номер которого укажем. Если аргумент опущен — заменяются все символы. 

Первой функцией мы можем узнать длину текста в ячейке. Второй функцией мы можем заменить все пробелы в тексте на «» (пусто), то есть удалить. Если после удаления мы подсчитаем длину текста еще раз, то она сократится ровно на число удаленных пробелов. Таким образом, чтобы подсчитать пробелы, нужна формула:

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))

Прибавим к ней единицу — и получим число слов в ячейке.

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))+1

 Имейте в виду, что такой способ подсчета принимает символы, отделенные от слов пробелом, за отдельные слова. Например, тире, обрамленное пробелами, также будет считаться отдельным словом. Избежать этого можно, если перед подсчетом пробелов убрать функцией ПОДСТАВИТЬ() лишние знаки из ячейки.  

Задача 2. Извлечение первого слова из текста

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

Найти символ в тексте поможет функция НАЙТИ(). У нее есть 3 аргумента:

1) Искомый текст;

2) Текст, в котором ищем;

3) С какого символа начать поиск (необязательный, если опущен — функция ищет с самого начала).

Функция НАЙТИ работает с учетом регистра и без подстановочных знаков. Если нужно их использовать, или не учитывать регистр — есть аналогичная функция ПОИСК(). 

Теперь мы можем найти позицию первого пробела

=НАЙТИ(» «;A1)

Извлечь символы до пробела поможет функция ЛЕВСИМВ. Ей нужно указать текст, а также число символов, которое нужно извлечь начиная с первого. Если найденный пробел, например, 6-ой символ, то нам нужно извлечь 6-1=5. Формула для извлечения первого слова:

=ЛЕВСИМВ(A1;НАЙТИ(» «;A1)-1)

Если в строке одно слово (то есть нет пробела между первым и вторым словом), данная формула вернет ошибку. Чтобы этого избежать, нужно добавить к ней функцию ЕСЛИОШИБКА(). 

=ЕСЛИОШИБКА(ЛЕВСИМВ(A1;НАЙТИ(» «;A1)-1);A1)

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

Задача 3. Извлечение последнего слова из текста

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

• подсчитаем, сколько в ячейке пробелов, воспользовавшись приемом из Задачи 1. 

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))

• с помощью функции ПОДСТАВИТЬ заменим последний пробел в тексте на символ, которого там точно нет (например, «*» или «$»). Чтобы заменить именно последний пробел, добавим в качестве необязательного аргумента формулу из предыдущего пункта (то есть число пробелов, так как оно равно номеру последнего пробела)

=ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))

• теперь, когда последний пробел заменен на «*», мы можем узнать его позицию с помощью функции НАЙТИ(). В качестве текста, в котором ищем, укажем предыдущую формулу

=НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

=ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

Итоговая формула:

=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))))

Как и в предыдущей задаче, можно обернуть в ЕСЛИОШИБКА, чтобы верно работало с ячейками, в которых только 1 слово. 

=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))));A1)

Если Вас не пугает VBA и макросы, то есть гораздо более изящное решение. Добавьте в модуль книги (можно в личную книгу макросов) следующий код:

Function РЕВЕРС(str As String)

РЕВЕРС = StrReverse(str)

End Function

Это макрофункция. Теперь, используя ее, можно отразить текст зеркально, извлечь первое слово, как в Задаче 2 и отразить его обратно. Формула:

=ЕСЛИОШИБКА(РЕВЕРС(ЛЕВСИМВ(РЕВЕРС(A1);НАЙТИ(» «;РЕВЕРС(A1))));A1)

Берите на вооружение, если любите макросы.  

Задача 4. Извлечение n-ного слова из текста

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

https://www.youtube.com/watch?v=MgkXG-Kb0Vc

Помимо уже использовавшихся в статье функций, нам понадобятся еще 2:

1) ПОВТОР(). Умеет повторять указанный текст указанное число раз.

2) ПСТР(). Имеет 3 аргумента: 

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

Теперь мы вооружены всем нужным инструментарием. Приступаем:

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

=ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)))

• Теперь каждое слово отделено друг от друга числом символов, равным общей длине строки. Чтобы извлечь слово под номером n, нам нужно узнать позицию какого-нибудь пробела между словом n и n-1 в обработанном удлиненном тексте. Это может сделать формула ниже (+1 в ней нужно для правильного поиска первого слова): 

=ДЛСТР(A1)*(n-1)+1

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

• Теперь мы может извлечь из найденной позиции число символов ДЛСТР(A1) (на рисунке — m) и извлеченный текст всегда будет захватывать нужное нам слово целиком (с некоторым количеством предшествующих и последующих пробелов)

=ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1))

• Осталось избавиться от лишних пробелов функцией СЖПРОБЕЛЫ()

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1)))

Не забудьте в формуле заменить n на номер извлекаемого слова или ссылку на ячейку с этим номером. 

Задача 5. Извлечение имя файла из полного пути к нему

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

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

Берем формулу из задачи 3 и заменяем в ней пробелы на «\».

Получим следующую формулу:

=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;»\»;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;»\»;»»)))));A1)

Вставляем в ячейку и вуаля! Имя файла извлечено. 

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

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

аши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru 

Источник: https://golos.io/excel/%40tdots/izvlechenie-slov-iz-teksta-v-excel

Как прописать сумму, число, цифры прописью в Excel

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

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

Как написать сумму прописью на украинском языке

Сумма в рублях, долларах или евро с копейками прописью

Допустим, мы делаем какие-то расчеты в таблице и получаем итоговую сумму в рублях 1526,23

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

Propis (Amount;Money;lang;Prec)

где

Amount — это ссылка на ячейку с числом

Money — тут указывается вид валюты, можно указать рубли, доллары и евро («RUB», «USD», «EUR») — валюта обязательно указывается в кавычках.

Prec — показывать (1) или не показывать (0) дробную часть

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

Чтобы создать пользовательскую функцию Propis, необходимо скопировать код, указанный ниже, далее нажмите ALT+F11, чтобы открыть VBA,  добавьте новый пустой модуль через меню Insert — Module и вставьте туда скопированный код

Макрос пользовательской функции суммы прописью

Function Propis(Amount As String, Optional Money As String = «RUB», Optional lang As String = «RU», Optional Prec As Integer = 1) Dim whole As Double Amount = Replace(Amount, «-«, Application.International(xlDecimalSeparator)) Amount = Replace(Amount, «.», Application.International(xlDecimalSeparator)) Amount = Replace(Amount, «,», Application.

International(xlDecimalSeparator)) Sum = WorksheetFunction.

Round(CDbl(Amount), 2) Money = UCase(Money) lang = UCase(lang) whole = Int(Sum) fraq = Format(Round((Sum — whole) * 100), «00») Select Case Class(whole, 1) + Class(whole, 2) * 10 Case 1, 21, 31, 41, 51, 61, 71, 81, 91 w_rus_r = «рубль» w_rus_d = «доллар» w_rus_e = «евро» w_en_r = «rubles» w_en_d = «dollars» w_en_e = «euro» Case 2, 3, 4, 22, 23, 24, 32, 33, 34, 42, 43, 44, 52, 53, 54, 62, 63, 64, 72, 73, 74, 82, 83, 84, 92, 93, 94 w_rus_r = «рубля» w_rus_d = «доллара» w_rus_e = «евро» w_en_r = «rubles» w_en_d = «dollars» w_en_e = «euro» Case Else w_rus_r = «рублей» w_rus_d = «долларов» w_rus_e = «евро» w_en_r = «rubles» w_en_d = «dollars» w_en_e = «euro» End Select Select Case fraq Case 1, 21, 31, 41, 51, 61, 71, 81, 91 f_rus_r = «копейка» f_rus_d = «цент» f_rus_e = «цент» f_rus_p = «сотая» f_en_r = «kopecks» f_en_d = «cents» f_en_e = «cents» f_en_e = «cents» Case 2, 3, 4, 22, 23, 24, 32, 33, 34, 42, 43, 44, 52, 53, 54, 62, 63, 64, 72, 73, 74, 82, 83, 84, 92, 93, 94 f_rus_r = «копейки» f_rus_d = «цента» f_rus_e = «цента» f_en_r = «kopecks» f_en_d = «cents» f_en_e = «cents» Case Else f_rus_r = «копеек» f_rus_d = «центов» f_rus_e = «центов» f_en_r = «kopecks» f_en_d = «cents» f_en_e = «cents» End Select If Prec = 0 Then fraq = «» f_rus_r = «» f_rus_d = «» f_rus_e = «» f_en_r = «» f_en_d = «» f_en_e = «» End If If lang = «RU» Then Select Case Money Case «RUB» Out = ScriptRus(whole) & » » & w_rus_r & » » & fraq & » » & f_rus_r Case «USD» Out = ScriptRus(whole) & » » & w_rus_d & » » & fraq & » » & f_rus_d Case «EUR» Out = ScriptRus(whole) & » » & w_rus_e & » » & fraq & » » & f_rus_e End Select End If If lang = «EN» Then Select Case Money Case «RUB» Out = ScriptEng(whole) & » » & w_en_r & » » & fraq & » » & f_en_r Case «USD» Out = ScriptEng(whole) & » » & w_en_d & » » & fraq & » » & f_en_d Case «EUR» Out = ScriptEng(whole) & » » & w_en_e & » » & fraq & » » & f_en_e End Select End If Propis = WorksheetFunction.Trim(Out) End Function Private Function Class(m, i) Class = Int(Int(m — (10 i) * Int(m / (10 i))) / 10 (i — 1))End Function Private Function ScriptRus(n As Double) As String Dim Nums1, Nums2, Nums3, Nums4 As Variant Nums1 = Array(«», «один «, «два «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «) Nums2 = Array(«», «десять «, «двадцать «, «тридцать «, «сорок «, «пятьдесят «, «шестьдесят «, «семьдесят «, «восемьдесят «, «девяносто «) Nums3 = Array(«», «сто «, «двести «, «триста «, «четыреста «, «пятьсот «, «шестьсот «, «семьсот «, «восемьсот «, «девятьсот «) Nums4 = Array(«», «одна «, «две «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «) Nums5 = Array(«десять «, «одиннадцать «, «двенадцать «, «тринадцать «, «четырнадцать «, «пятнадцать «, «шестнадцать «, «семнадцать «, «восемнадцать «, «девятнадцать «) If n = 0 Then ScriptRus = «Ноль» Exit Function End If ed = Class(n, 1) dec = Class(n, 2) sot = Class(n, 3) tys = Class(n, 4) dectys = Class(n, 5) sottys = Class(n, 6) mil = Class(n, 7) decmil = Class(n, 8) sotmil = Class(n, 9) mlrd = Class(n, 10) If mlrd > 0 Then Select Case mlrd Case 1 mlrd_txt = Nums1(mlrd) & «миллиард » Case 2, 3, 4 mlrd_txt = Nums1(mlrd) & «миллиарда » Case 5 To 20 mlrd_txt = Nums1(mlrd) & «миллиардов » End Select End If If (sotmil + decmil + mil) > 0 Then sotmil_txt = Nums3(sotmil) Select Case decmil Case 1 mil_txt = Nums5(mil) & «миллионов » GoTo www Case 2 To 9 decmil_txt = Nums2(decmil) End Select Select Case mil Case 1 mil_txt = Nums1(mil) & «миллион » Case 2, 3, 4 mil_txt = Nums1(mil) & «миллиона » Case 0, 5 To 20 mil_txt = Nums1(mil) & «миллионов » End Select End Ifwww: sottys_txt = Nums3(sottys) Select Case dectys Case 1 tys_txt = Nums5(tys) & «тысяч » GoTo eee Case 2 To 9 dectys_txt = Nums2(dectys) End Select Select Case tys Case 0 If dectys > 0 Then tys_txt = Nums4(tys) & «тысяч » Case 1 tys_txt = Nums4(tys) & «тысяча » Case 2, 3, 4 tys_txt = Nums4(tys) & «тысячи » Case 5 To 9 tys_txt = Nums4(tys) & «тысяч » End Select If dectys = 0 And tys = 0 And sottys 0 Then sottys_txt = sottys_txt & » тысяч «eee: sot_txt = Nums3(sot) Select Case dec Case 1 ed_txt = Nums5(ed) GoTo rrr Case 2 To 9 dec_txt = Nums2(dec) End Select ed_txt = Nums1(ed)rrr: ScriptRus = mlrd_txt & sotmil_txt & decmil_txt & mil_txt & sottys_txt & dectys_txt & tys_txt & sot_txt & dec_txt & ed_txt ScriptRus = UCase(Left(ScriptRus, 1)) & LCase(Mid(ScriptRus, 2, Len(ScriptRus) — 1)) End Function Private Function ScriptEng(ByVal Number As Double) Dim BigDenom As String, Temp As String Dim Count As Integer ReDim Place(9) As String Place(2) = » Thousand » Place(3) = » Million » Place(4) = » Billion » Place(5) = » Trillion » strAmount = Trim(Str(Int(Number))) Count = 1 Do While strAmount «» Temp = GetHundreds(Right(strAmount, 3)) If Temp «» Then BigDenom = Temp & Place(Count) & BigDenom If Len(strAmount) > 3 Then strAmount = Left(strAmount, Len(strAmount) — 3) Else strAmount = «» End If Count = Count + 1 Loop Select Case BigDenom Case «» BigDenom = «Zero » Case «One» BigDenom = «One » Case Else BigDenom = BigDenom & » » End Select ScriptEng = BigDenom End Function Private Function GetHundreds(ByVal MyNumber) Dim result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right(«000» & MyNumber, 3) If Mid(MyNumber, 1, 1) «0» Then result = GetDigit(Mid(MyNumber, 1, 1)) & » Hundred » End If If Mid(MyNumber, 1, 1) «0» And (Mid(MyNumber, 2, 1) «0» Or Mid(MyNumber, 3, 1) «0») Then result = result & «And » End If If Mid(MyNumber, 2, 1) «0» Then result = result & GetTens(Mid(MyNumber, 2)) Else result = result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = resultEnd FunctionPrivate Function GetTens(TensText) Dim result As String result = «» If Val(Left(TensText, 1)) = 1 Then Select Case Val(TensText) Case 10: result = «Ten» Case 11: result = «Eleven» Case 12: result = «Twelve» Case 13: result = «Thirteen» Case 14: result = «Fourteen» Case 15: result = «Fifteen» Case 16: result = «Sixteen» Case 17: result = «Seventeen» Case 18: result = «Eighteen» Case 19: result = «Nineteen» Case Else End Select Else Select Case Val(Left(TensText, 1)) Case 2: result = «Twenty » Case 3: result = «Thirty » Case 4: result = «Forty » Case 5: result = «Fifty » Case 6: result = «Sixty » Case 7: result = «Seventy » Case 8: result = «Eighty » Case 9: result = «Ninety » Case Else End Select result = result & GetDigit _ (Right(TensText, 1)) End If GetTens = resultEnd FunctionPrivate Function GetDigit(Digit) Select Case Val(Digit) Case 1: GetDigit = «One» Case 2: GetDigit = «Two» Case 3: GetDigit = «Three» Case 4: GetDigit = «Four» Case 5: GetDigit = «Five» Case 6: GetDigit = «Six» Case 7: GetDigit = «Seven» Case 8: GetDigit = «Eight» Case 9: GetDigit = «Nine» Case Else: GetDigit = «» End SelectEnd Function

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

=Propis(B2;»RUB»;»RU»;1)

Числа прописью с копейками  заглавными или строчными буквами в Excel

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

Function РубПропись(Сумма As Double, Optional Без_копеек As Boolean = False, _ Optional КопПрописью As Boolean = False, Optional начинитьПрописной As Boolean = True) As String'Функция для написания суммы прописью Dim ed, des, sot, ten, razr, dec Dim i As Integer, str As String, s As String Dim intPart As String, frPart As String Dim mlnEnd, tscEnd, razrEnd, rub, cop dec = Array(«», «одна «, «две «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «) ed = Array(«», «один «, «два «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «) ten = Array(«десять «, «одиннадцать «, «двенадцать «, «тринадцать «, «четырнадцать «, «пятнадцать «, «шестнадцать «, «семнадцать «, «восемнадцать «, «девятнадцать «) des = Array(«», «», «двадцать «, «тридцать «, «сорок «, «пятьдесят «, «шестьдесят «, «семьдесят «, «восемьдесят «, «девяносто «) sot = Array(«», «сто «, «двести «, «триста «, «четыреста «, «пятьсот «, «шестьсот «, «семьсот «, «восемьсот «, «девятьсот «) razr = Array(«», «тысяч», «миллион», «миллиард») mlnEnd = Array(«ов «, » «, «а «, «а «, «а «, «ов «, «ов «, «ов «, «ов «, «ов «) tscEnd = Array(» «, «а «, «и «, «и «, «и «, » «, » «, » «, » «, » «) razrEnd = Array(mlnEnd, mlnEnd, tscEnd, «») rub = Array(«рублей», «рубль», «рубля», «рубля», «рубля», «рублей», «рублей», «рублей», «рублей», «рублей») cop = Array(«копеек», «копейка», «копейки», «копейки», «копейки», «копеек», «копеек», «копеек», «копеек», «копеек») If Сумма >= 1000000000000# Or Сумма < 0 Then РубПропись = CVErr(xlErrValue): Exit Function '&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& If Round(Сумма, 2) >= 1 Then intPart = Left$(Format(Сумма, «000000000000.00»), 12) For i = 0 To 3 s = Mid$(intPart, i * 3 + 1, 3) If s «000» Then str = str & sot(CInt(Left$(s, 1))) If Mid$(s, 2, 1) = «1» Then str = str & ten(CInt(Right$(s, 1))) Else str = str & des(CInt(Mid$(s, 2, 1))) & IIf(i = 2, dec(CInt(Right$(s, 1))), ed(CInt(Right$(s, 1)))) End If On Error Resume Next str = str & IIf(Mid$(s, 2, 1) = «1», razr(3 — i) & razrEnd(i)(0), _ razr(3 — i) & razrEnd(i)(CInt(Right$(s, 1)))) On Error GoTo 0 End If Next i str = str & IIf(Mid$(s, 2, 1) = «1», rub(0), rub(CInt(Right$(s, 1)))) End If РубПропись = str '''''''''''''''''' If Без_копеек = False Then frPart = Right$(Format(Сумма, «0.00»), 2) If frPart = «00» Then frPart = «» Else If КопПрописью Then frPart = IIf(Left$(frPart, 1) = «1», ten(CInt(Right$(frPart, 1))) & cop(0), _ des(CInt(Left$(frPart, 1))) & dec(CInt(Right$(frPart, 1))) & cop(CInt(Right$(frPart, 1)))) Else frPart = IIf(Left$(frPart, 1) = «1», frPart & » » & cop(0), frPart & » » & cop(CInt(Right$(frPart, 1)))) End If End If РубПропись = str & » » & frPart End If ''''''''''''''''''' РубПропись = str & frPart If начинитьПрописной Then Mid$(РубПропись, 1, 1) = UCase(Mid$(РубПропись, 1, 1))' If начинитьПрописной Then РубПропись = UCase(Left(РубПропись, 1)) & Mid(РубПропись, 2)End Function

• Без копеек (1), с копейками (0)
• Копейки прописью (1), числом (0)
• Начинать прописью (0), заглавной (1)

Вот как используется функция

Примечание

• Данная функция будет работать с числами от 0 до 99 999 999
• Перед копирование кода переключите раскладку клавиатуры на русский язык (для корректного копирования русского текста)
• Код VBA необходимо вставлять во все файлы (Книги Excel), где вы хотите, чтобы она работала
• После вставки код, необходимо сохранить файл с поддержкой макросов xlsm (в Excel, начиная с 2007 версии)
• Функцию можно либо набирать в ручную, либо, если вы забыли как она пишется, через мастер функций (кнопка fx в строке формул, категория Определенные пользователем)

58 Комментариев

Источник: http://sirexcel.ru/makrosi_vba/kak-propisat-summu-chislo-cifry-propisyu-v-excel/

Замена символов в Microsoft Excel

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

Скачать последнюю версию Excel

Способы замены символов в Excel

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

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

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

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

Поиск с заменой

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

1. Кликаем по кнопке «Найти и выделить», которая располагается во вкладке «» в блоке настроек «Редактирование». В появившемся после этого списке делаем переход по пункту «Заменить».
2. Открывается окно «Найти и заменить» во вкладке «Заменить». В поле «Найти» вводим число, слова или символы, которые требуется отыскать и заменить. В поле «Заменить на» выполняем ввод данных, на которые будет производиться замена.

   Как видим, в нижней части окна имеются кнопки замены – «Заменить всё» и «Заменить», и кнопки поиска — «Найти всё» и «Найти далее». Жмем на кнопку «Найти далее».

3. После этого производится поиск по документу искомого слова. По умолчанию, направление поиска производится построчно. Курсор останавливается на первом же результате, который совпал. Для замены содержимого ячейки жмем на кнопку «Заменить».
4. Чтобы продолжить поиск данных, опять жмем на кнопку «Найти далее». Тем же способом меняем следующий результат, и т.д.

Можно найти все удовлетворяющие запросу результаты сразу.

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

Автоматическая замена

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

Процедура выполняется практически моментально.

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

Урок: как заменить точку на запятую в Экселе

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

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

1. Находясь во вкладке «Заменить», в окне «Найти и заменить» жмем на кнопку Параметры.
2. Открывается окно дополнительных параметров. Оно практически идентично окну расширенного поиска. Единственное отличие – присутствие блока настроек «Заменить на».

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

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

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

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

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

   Единственной настройкой значения для вставки будет являться все тот же формат ячеек. Для выбора формата вставляемого значения жмем на одноименную кнопку напротив параметра «Заменить на…».

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

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

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

3. Не забываем в поля «Найти» и «Заменить на…» вписать соответствующие значения. Когда все настройки указаны, выбираем способ выполнения процедуры. Либо жмем на кнопку «Заменить все», и замена происходит автоматически, согласно введенным данным, или же жмем на кнопку «Найти все», и отдельно производим замену в каждой ячейке по тому алгоритму, о котором писалось уже выше.

Урок: Как сделать поиск в Экселе

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

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

Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Источник: http://lumpics.ru/replacing-characters-in-excel/