Массовая замена текста формулами в Excel
HTML-код
- Опубликовано: 13 сен 2024
- Как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).
Скачать пример www.planetaexc...
Заходите в гости ↓↓↓
Мои книги planetaexcel.ru...
Мои онлайн-курсы www.planetaexc...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Самый лучший эксель канал!!! Спасибо
Честно говоря, никогда не пишу комментариев. Но хотел просто сказать большое спасибо Николаю Павлову за его труд. Видна реальная и очень тщательная работа, т.к. постоянно появляется новый материал на его сайте и попутно на канале в RUclips, что очень радует. Николай, успехов Вам и крепкого здоровья, что бы и дальше нас так радовали.
Спасибо! :)
Большое спасибо за видеоурок!
Супер! Спасибо Вам!
Как всегда изящное решение. А девятки - это гениально! Только я обычно использовал условие, если больше 0.
Здравствуйте! Спасибо за Вашу работу. Профессионал с большой буквы. Давно подписан на Ваш канал и очень часто нахожу помощь для себя.
Александр, спасибо за видео. Раньше думал, что Excel совсем непонятный, но вот уже очень плотно работаю с Excel, дошел до VBA и т.д. Купил вашу книгу по PQ - изучаю. Очень доходчиво. Спасибо ещё раз.
Николай, спасибо! Как всегда, кратко, четко, доступно!
крайне полезная штука, когда сводные таблицы нужно причесать
Один из тех каналов, когда ставишь лайк перед просмотром. Так как тут бесполезной информации не бывает.
Супер! Очень жду продолжение с PQ.
Спасибо за то, что делитесь знаниями, это бесценно!
Это ЧУДО!! Спасибо огромное, за обучение!
Николай, ваше видео это просто находка. Спасибо огромное! Кучу формул в экселе пересмотрела, но до такого бы не додумалась. Жаль ютуб не позволяет больше одного лайка ставить, я бы этот видос залайкала :)
Шикарное видео. Надо будет потренироваться, обязательно пригодится. Спасибо.
Прекрасный формат подачи!
Ну прям, то что нужно, я очень редко пишу комменты, но мне прям зашло! Я еще добавил заглавную букву отдельной таблицей! И ваша формула просто затащила ))) очень круто! спасибо! А то столько время уходила на все это раньше! Жду еще видео :)
Офигенно. Меня максимум хватило бы на найти и заменить
Виртуозно! Спасибо.
Огромное спасибо за Ваши уроки!
Спасибо. Полезно и применимо в жизни.
Просто здОрово! Благодарствую, Николай!
Ну прямо таки классический пример того, почему НЕ НУЖНО (!!!) делать базы данных в Excel'е. :-D
Заодно и о том, что, разработчикам БД, не нужно забывать о создании справочников... Хотя, они-то редко об этом забывают. А вот заставить пользователей, таки, заполнять справочники и выбирать потом данные из них - практически нереально. :-(
PS. Сразу, глядя на эти примеры, вспомнились связанные типы данных в Excel'е, в частности "География". :-D Был-бы интернет - и вот готовые - заполненные ! - справочники уже в программе. А учитывая то, что этих новых типов данных там уже десятка с 2 на вводили, возможности открываются достаточно широкие. Жаль, что пощупать новые функции дают лишь избранным.
Николай, вначале вы произносите, что являетесь тренером и экспертом по продуктам Microsoft Office и по Excel В ЧАСТНОСТИ, но на вашем канале нет других видео по продуктам Microsoft Office КРОМЕ Excel. Уверен, что вы можете сделать такое же классное видео, например, по Power Point.
Лучше уж по Акцессу
Тренинги я веду и по Word, Outlook, Power Point, да. Можно видео и по другим программам делать, но где взять столько времени? :) На один 15-20 минутный ролик уходит день.
@@planetaexcel ради разнообразия
Шикарно!
Блестяще! Как всегда) Спасибо огромное!
Всегда полезные видео. Спасибо!
Спасибо огромное. Очень полезно и нужно!
Полезный контент! Крутая подаче информации
Лучший просто🙏
Нет слов, оригинально.
Как всегда полезно! Ждём PQ урок)
Спасибо за труд
Николаю привет) словил дежавю при просмотре)))
Добрый день, Николай.
Огромное Вам спасибо за труд и канал.
Видео бесподобны. Вы объясняете очень подробно и понятно.
Желаю успехов во всех Ваших начинаниях.
P.S. Мне кажется, что можно обойти регистрочувствительность функции НАЙТИ, тем что использовать другую функцию ПОИСК.
Уверен, что решение в Power Query будет вообще бомбическим)).
Можно использовать нерегистрочувствительную ПОИСК вместо НАЙТИ, но проблема в том, что ПОДСТАВИТЬ - регистрочувствительная :(
лучший эксель канал
Превосходно, спасибо!
Спасибо за видео. Жаль, что у нас на работе 2007 Excel....
А мне знакомый (2 дня) подогнал 365 офис. Индус, логин и пароль, разумеется на честном слове.
А эти формулы будут и в 2007 прекрасно работать, скажу вам по секрету :)
@@planetaexcel ну конечно, когда знаешь - все просто!
начиная с 2013 офиса появилась функция ЕСЛИМН(), в ней сотни полторы аргументов, обычно их хватает, чтобы забить туда условия как в справочнике в этом виде. Результат тот же, если не надо динамических массивов.
Спасибо,помогли)
Как раз сейчас с этой проблемой борюсь!)))
спасибо за старания!!
Супер
Николай, я обожаю Excel и ваши видео это супер. Подскажите может Вы создавали таблицу для «автолюбителей»? Я имею в виду хорошо бы иметь все данные о своём автомобиле в одном файле: 1. Общий расход топлива; 2. Расход топлива в текущем ( прошлых месяцах); 3. Расход топлива зимой/ летом; 4. Стоимость техосмотра; 5. Стоимость ремонта ( по категориям: двигатель, ходовая, прочее); 6. Прочие данные. Если был такой опыт может есть возможность снять видео ?
шикардос.... чес слово
Супер! Спасибо!
Большое спасибо за работу но есть пожелание!
для простоты понимания и закрепления сложных формул хорошо бы разбивать формулы по ячейкам чтобы видеть из постепенную работу.
а уже потом в конце все собирать в 1 формулу.
сразу все в 1 формуле мне сложно воспринимать ((( спасибо..
Было интересно и познавательно_)
Умница 👏
Бомбически)))))))
9999 - круть. буду использовать.
Наверное методически более правильным было бы искать ИНН.
С другой стороны, канал могут смотреть не только в России. Опять же - грамотно.
ИНН лучше, но тоже не панацея, ибо если у юрлица несколько филиалов в разных регионах, то ИНН у них всех одинаковый-как у головной конторы.
Спасибо
Огромное вам спасобо, у меня такой вопрос: Можно ли защитить выпадающий список?
Спасибо!
Большое вам спасибо! Ваши видео очень понятны и доступны. У меня вопрос , а можно ли бесплатно скачать эксель 2016 с ресурса? Надстройку PLEX не получилось установить.
Гениально
Класс!!!👍
Есть у Вас урок на тему автописьма в exsel или word? Разные условия коммерческих предложений вымораживает копировать в письма, есть ли вариант подстановки, чтобы получить нормальный текст с предложением цены по различным услугам в разной ценовой категории, но чтобы это выглядело как письмо?
Николай, спасибо за урок.
На мой взгляд этот подход очень полезен, когда есть большой объём информации для исправления.
Для исключения "разнобоя" я обычно использую принцип "Выбери из списка, а не набери". Стоит ли в будущем изменить подход? Оператор должен не ВВОДИТЬ название (кто во что горазд), а ВЫБИРАТЬ из списка. Ибо ведь могут найтись грамотеи, которые "Морозко" могут написать "Марозко", "Мрозко", "Морозка"...
Как думаете?
Однозначно. Выпадающие списки - наше всё.
Правда ломаются они простым копированием и вставкой, к сожалению :(
@@planetaexcel Это да... Я к тому, что когда работаешь с большими массивами информации то уже хочешь чёткого однообразия. А то пользователи там с маленькой, там с большой, там сократят, там с ошибкой напишут...
Ещё раз спасибо за Ваши уроки.
Такой вопрос, как можно сделать поиск ближайшего числа по двум условиям в строке, например есть длина и высота, чтобы обе эти величины были больше значений, которые я задаю. Было бы хорошо если бы кто-нибудь помог с этим, а то пока не придумал как это сделать.
почему то некоторые строки не заменяются на предложенный текст. на что обратить внимание? как исправить?
5:20 - в обычном офисе если протянуть первую ячейку с формулами вниз на еще 2 ячейки - увидим же результат 4 и #ЗНАЧ?
Добрый, многое из видеоуроков относится к офис по подписки. А если его нету, уточняли как это сделать в обычном, удобно будет.
В этом видео все формулы будут работать в любой версии Office.
Я недавно начал изучать excel и нашел ваш канал! Но пока не могу понять систему, в какой последовательности осваивать предоставляемый вами материал(
Трудно сказать. Это не онлайн-видеоучебник, где уроки расположены в логической последовательности и по возрастанию сложности. Тут я публикую видео на очень разные темы и очень разного уровня сложности, которые меня заинтересовали. Систематично и по возрастанию - это вам онлайн-курсы нужны, скорее.
Формула чувствительна к регистру, например ромашка уже не ищется, как этого избежать?
Здравствуйте, у меня вот такой интересный вопрос о защите ячеек. Я создала в экселе в формате таблицы необходимые мне формулы, что бы при добавлении новой строки добавлялись и все формулы. Возможно ли как то защитить эти ячейки с формулами, что бы они не менялись случайно другими пользователями, но при добавлении новой строки другим пользователем так же в неё добавлялись? Надеюсь понятно описала свою задачу, не могу найти пока что решение, а ваш канал пояснил мне многие вопросы)
К сожалению, такое только макросом. Несложным, но все же...
@@planetaexcel а у вас есть подобное видео как такое сделать?
Здравствуйте. А как можно насторить свой формат текста? На пример если А цвет красный с шрифтом жирным, а если В то зеленый с шрифтом жирным. И еще выскакивал текст при написание первой буквы или какую нить цифру к нему приписать
Ищите информацию про «условное форматирование».
Как найти и отсеить большое количество строк и разного значения
Благодарю за интересные и жизненные решения в эксель. Я немного не допонял одного момента. В четвёртом снизу элементе (невский проспект 32) по идее должна быть замена названия города и слова "пр. " на "пр-т". По факту в тексте корректируется первое (имеющее наименьший порядковый символ в ячейке) в соответствии с данными справочника. Можно ли как-то модифицировать формулу для замены 2-3 несоответствий в ячейке?
Досмотрите видео до конца - я там как раз про это и рассказываю. И в файле-примере тоже есть про это (на последнем листе).
Добрый день Николай, подскажите пожалуйста. Мне нужно вставить в 1ю ячейку имя из 2й при условии что в 3й ячейки значение больше 0. Какую функцию мне следует выбрать.??? помогите пожалуйста
ЕСЛИ (IF)
Николай, вы когда нибудь работали категорийным менеджером?
Нет, а кто это?
Николай привет от вашего ученика! Шикарное видео! Скажите, когда будет продолжение с PQ? Очень нужно, т.к. с функцией List.Accumulate мне лично не все очевидно, а вариант использования Excel не подходит, т.к. я практически полностью перешёл на Power BI Report Server в конторе. И такая проблема пока что решается мной коряво посредством множественного использования Replacer.ReplaceText
List.Accumulate в PQ это цикл for,погуглите Вам сразу станет все понятно)
Вот решение Вашей задачи с помощью List.Accumulate
ruclips.net/video/PvfXPEi2SNQ/видео.html
@@user-sn9se8dv3q спасибо полезное видео. Как работает List.Accumulate я понимал, однако мысль как применить к ситуации множественного замены мне не приходила в голову ))). В любом случае, как я понял нужно создавать и постоянно пополнять список замены. А я пытался разрешить ситуацию, когда "очень умные" пользователи системы постоянно придумывают новые названия для вполне известных компаний ))). Даже пытался писать функцию парсинга рус.профиля, правда они отлавливают роботизированные обращения к их сайту ))) и переодически блокируют IP
@@user-sn9se8dv3q кстати при поиске совпадений я частенько использую функцию, которую Николай описывал у себя в книге и на сайте - функция коэффициента подобия. Правда я добавил ещё одну функцию, чтобы сравнивать не только по символам, но и по словам и находить среднее совпадение между ними
@@user-nb5oh6fi6s только если у вас библиотека состоит более чем из 50 пар,код начинает безобразно тормозить и Table.Buffer уже не спасает)
@@user-sn9se8dv3q да это мы уже проходили. В Power BI PQ работает чуток быстрее, однако моя модель с обращением к 4 базам данных, и 5 сайтам обрабатывается порядка 5-7 минут ))). И все равно оно того стоит. Руками ничего не делаешь. Как говорит мой шэф: Кнопку нажал и спина в мыле! 😁
Николай, как в Ексель убрать сотни тысяч пустых строк? Если нажимаю Скрыть, то выходит ошибка "Объект не возможно переместить за пределы листа".
Посмотрите вот эту статью - должно помочь www.planetaexcel.ru/techniques/12/114/
Все здорово, но что означает повторный прогон формулы?
Еще раз применить ту же формулу к уже полученным результатам, чтобы заменить незамененные фрагменты. Тупо, но вынужденно :)
Чистой воды шаманство😁 благодарю
У меня ничего не получилось. Но я правда заменял транскрипционный английский текст на немецкие буквы.
комментарии модерируются ?
В смысле? Ну, спам блокирую и неадекватов, бывает. Но редко. Здесь таких мало :)
@@planetaexcel Просто после просмотра видео захотел поделиться альтернативным методом решения задачи массовой замены текста формулами. При некоторых недостатках , вымученный мной метод имел одно весомое преимущество - он ищет не только точные соответствия как в Вашем видео , но и соответствия по выражениям типа регулярных ("*М*скв?", "*C*П*б*" и . д.). Вроде написал комментарий под видео, описал в нем свой подход. И как мне показалось комментарий опубликовал, но через день найти его под видео не смог. Вот и закралось подозрение, что его нещадно порезали, как ненужную конкуренцию , подрывающую авторитет автора видео))). Может конечно сбой какой был ...
13:50 Не Питер, а Петербург! Свои деревни кличками обзывайте.
=ЕСЛИ(ПОИСК...
и не надо мучиться с умными таблицами и доп. прогонами
Почему в 21 веке мы мучаемся с этой так сказать программой. Разработчиков как собак, изобретите норм программу для людей.
Думаю, проблема в том, что все люди очень разные :)
Ничего не работает и не применяет значение где частично. ППц столько сил угрохал на эту херню
Спасибо!
Спасибо
Спасибо!!!