Поваренная книга Экселиста #1 - Преобразовываем ФИО
Привет всем, моему одному подписчику - отдельное трямс)
Подумалось - надо чуть поделиться некоторыми наработками, которые собрались в голове за последние лет эдак 20 работы с различными БД (к коим я с легкостью отношу Эксель). В связи с чем будет ажно целый ряд статеечек на тему, в первую очередь, оптимизации рабочего времени (ну в смысле сделал за 10 минут и дальше листаешь пикабушечку). Поехали с достаточно частой задачи:
РЕЦЕПТ 1: Преобразовываем ФИО в Ф / И / О, без использования VBA и прочей нехристи.
Для начала чуть теории - большинство, наверняка, сталкивались с такой задачей - есть ФИО в одном столбце, а нужно вытянуть только имя, ну или два столбца ИМЯ и ФАМИЛИЯ. Да или даже поменять местами, задачи разные - суть одна. Теперь давайте разбираться что имеем - имеем строку с N-количеством слов, разделенных одинаковым символом " " (ну или чуть сложнее с массивом, содержащим N-количество элементов, в роли разделителя " "). Тут важно понять сам смысл - все, что имеет закономерность, подлежит автоматизации. В нашем случае закономерность будет вот такая - СЛОВО" "СЛОВО" "СЛОВО, следовательно мы ИЛИ должны "выбрать" нужное нам СЛОВО из всей строки, или чуть схитрить) Но начнем с выбора.
ВАРИАНТ 1: Средствами экселя. Определяем нахождение разделителей по длине строки.
1. Создаем отдельную страницу (на всякий случай)
2. Вставляем данные - Допустим вид будет вот такой
ID | ФИО
1 | Иванов Иван Иванович (b2)
3. Выбираем ПЕРВОЕ СЛОВО, ячейка (c2)
=TRIM(LEFT(B2;FIND(" ";B2;1)))
Что сделали - нашли первое вхождение символа " " в строку, и резанули все, что после него.
4. Выбираем ВТОРОЕ СЛОВО, ячейка (d2)
=TRIM(MID(B2;FIND(" ";B2;FIND(" ";B2;1))+1;FIND(" ";B2;FIND(" ";B2;1)+1)-FIND(" ";B2;FIND(" ";B2;1))))
Что сделали - указали Экселю на "координаты" первого и второго разделителей, скорректировали координаты (порезали длину строки на лишний символ " ").
5. Выбираем ТРЕТЬЕ СЛОВО, ячейка (e2)
=TRIM(MID(B2;FIND(" ";B2;FIND(" ";B2;1)+1)+1;LEN(B2)))
Что сделали - то же самое, что и со ВТОРЫМ СЛОВОМ, только так как нам не нужно резать по второй координате - за нее взяли длину строки.
На выходе получили вот так:
Подходит для постоянного применения и в случае, если разделитель всегда один и тот же.
Теперь для тех, кто хочет схитрить)
СПОСОБ 2: Подмена разделителя
1. Выделяем столбец с ФИО.
2. Открываем текстовый редактор (дада, Блокнот / TextEdit), вставляем туда. ОБЯЗАТЕЛЬНО переведите его в формат Plain Text/ просто текст (формат сохранения .txt) (!)
3. Находим функцию "Найти и заменить".
3.1. В поле найти введите символ " " (то есть просто поиск по одиночному пробелу).
3.2. В поле заменить вставьте символ TAB. Для этого на первой строке нажмите TAB, выделите его, cmd+c, cmd+v (или control, у кого какая религия)
3.3. Примените, скопируйте, вставьте в Эксель... И собственно так, если не знали - встречайте, TAB - символ переноса на следующую ячейку))
Надеюсь данные способы вам потребуются) И, главное, на забывайте - работает не только на ФИО)
Понравилось? Что-то интересно? Прошу в комменты) Ну и там лайк/ подписка / кошелек / очки / мотоцикл)
Господа, по работе изспользую ексель в очень больших количествах. то что описал автор - извращение. первое правило работы в екселе: можешь упростить - упрости. и тут разрабы майкрософта немного постарались - уже давно появилась функция "текст по столбцам", которая может практически в любой ситуации, исключение когда текст вообще корявый с кучей самых разных символов. а относительно недавно появилось и мгновенное заполнение, которое на ура справится с этой задачей.
Автор, вы точно поняли о чем пост? Не уверен. То, что вы много в нем работаете - еще не подразумевает что вы в нем работаете, а не заполняете, фил зе дифференс.
в том то и дело - мне приходится в нем создавать такие инструменты, чтобы те кто "заполняют" ничего не сломали и им было максимально просто. да я точно понял о чем пост - как разделить ФИО по столбцам. ваш метод предполагает использование довольно простой формулы для того кто ее хорошо понимает(среди пользователей, далеко не все это могут), и не самой простой для остальных. и многие в комментариях обратили ваше внимание на то что того же результата можно вполне добиться встроенными инструментами ексель. я уж молчу про надстройку PLEX, которая вообще предоставляет простор действий.
Если вы создаете инструменты в Экселе - в паре слов обьясните пж что конкретно делаете. Уж не всякие ли попытки скрестить Эксель с Аксессом?
И молчите дальше про PLEX, потому что совершенно верно делаете.
ну, полагаю что на постсоветском пространстве больше пользователей винды, поэтому PLEX может быть полезен. с аксессом я не работаю. а насчет екселя - я работаю на заводе, где в екселе происходит все, от планирования продукции, до мониторинга эффективности. собственно, если не считать инструменты созданные коллегами на других заводах, все создается в моем департаменте.
Ну, с одной стороны, хочется посмотреть на примеры работы. С другой стороны - это полный пиздец. Теперь выражение про "все заводы стоят, одни гитаристы в стране" полностью раскрыто...
Как бизнес-логику можно вести мать его в экселе? Обрабатывать - да, но вести?!
Данные вы как в эксель вносите? Руками? Автоматизация на уровне "как при царе батюшке было, но только с арифмометром"?
Пиздец.
П.С. Предположение - мать всех ошибок. Когда вы пошлете свой файл посмотреть наверх, а он будет завязан не-кросс-платформенный функционал.. хотя автоматизация завода на экселе.... пойду святой водой глаза помою....
полагаю вы меня не совсем правильно поняли. не автоматизация завода происзодит в екселе, а отдельные процессы. обработка данных выгруженных из бд. расчеты и отчеты. файл скинуть не могу (мало ли кто еще читает пикабу, а у нас неразглашение), но вот визуальный пример. эти отчеты должны быть динамическими, т.е. ими пользуются ежедневно, они актуализируются просто добавляя в исходники выгрузку. ексель прекрасно справляется.
И прошу разрешения использовать ваш комментарий для написания статейки почему нельзя использовать Эксель как автоматизацию, но с указанием того, что я вас не так понял, потому у вас, конкретно, все путем)
да, пожалуйста)
Тогда выдохнул. Примеры более чем понятны, то есть по факту выгружаете из автоматизации. Тогда просто представьте себе ГДЕ можно использовать описанный метод и поймите, что ФИО было дано просто для примера)
Нет, не понравилось.
Во-первых, пробелы заменять на табы во внешних редакторах не нужно. В экселе есть такая кнопочка: "Текст по столбцам". Там можно выбрать один из стандартных разделителей (пробел, запятая, табулятор и пр.) или задать нестандартный, причём можно выбрать несколько сразу.
Во-вторых, если на вашем предприятии было бы много людей, вы бы обнаружили, что в России работают люди без отчества, с отчеством из нескольких слов (Сулейман оглы), с двойным именем, разделённым пробелами, и другими отклонениями.
Кроме того, использование TRIM в ваших формулах не нужно (если в строке всё аккуратно и слова разделяются одним пробелом, в начале и конце пробелов нет, то вместо TRIM достаточно учитывать единичку, а в случае неаккуратного использования пробелов ваши формулы все равно работать не будут).
А еще у вас точки с запятой вместо запятых в формулах. Возможно, это связано с разной локалью в наших экселях, и у вас ошибки нет, точно не уверен.
Единственное что я не понимаю, это почему эксель не поддерживает регулярные выражения в ячейках.
Только допиливанием костылей через VBA
Поконкретнее с этого места... что имеете в виду, когда говорите что не поддерживает? Пример какой-нибудь?
В прямом.
Я не могу искать и вырывать нужные куски текста при помощи регулярных выражений.
Только при помощи всяких ограниченных функций поиска текста, левсимв, правсимв и вырвиглазных формул с их участием.
А вот кстати нет, ввели. В тестовой версии у кого-то в обзоре видел. НО до винды год, до Мака - только когда ВВП выборы проиграет))
Ну. Как я и сказал, они УЖЕ есть, потому что ВБА их отлично поддерживает.
Я не понимаю почему они выкинули столь полезный функционал, который ещё с 2007 года.
Без VBA, в том то и фокус
Будем надеяться.
)))))))))))
Посмотрите на скрин (к слову о вашем "большом предприятии" - базу на 500к пользователей не хотите ли?), и закиньте тег в игнор, вы уже и без меня все знаете))
Ну и далее:
"Во-первых, пробелы заменять на табы во внешних редакторах не нужно. В экселе есть такая кнопочка".
А еще в экселе есть двойные пробелы, разделители в виде " ," ";;;" " ". А теперь откройте эксель и вставьте туда разделитель ";;". Как выйдет - наберите, буду ждать) Текст о сути, а не о разделителе)
Не хочу. Базу вам кто-то написал, и она работает. Но ваш "рецепт" будет ломаться в простейших случаях "Иванов Иван", не говоря уже о "Иванов Иван Иванович".
Суть такая, что вы дали два рецепта: через формулы и через разделители.
Оба рецепта не сработают, если в строках есть лишние пробелы.
Первый рецепт более громоздкий, чем нужно.
Второй - бессмысленный, потому что требует пользоваться внешними редакторами, в то время как в экселе нужная возможность встроена. Более того, в отличие от вашего совета, эта возможность позволяет справиться с двойными пробелами.
Вашу сентенцию про точку с запятой я не понял.
Спасибо, понял, принял, не собираюсь тратить на вас больше свое время)))))))
П.С. Базы в экселе не ведут, в экселе обрабатывают выгрузки. Потому у вас НЕ МОЖЕТ быть различных разделителей, но они могут быть разные. И Эксель НЕ РАЗБИВАЕТ по двум и более символьным разделителям, а выгрузки из CRM бывают ой как разные. Потому вы пишите полную ересь.
На этом точно все)
Потрясающий у вас апломб. Дело не в том, что я якобы уже всё знаю. Дело в том, что вы сами в теме не разобрались и даёте дурацкие советы. Примерно как если б учили людей копать картошку граблями, держа грабли за зубцы.
Функцию "текст по столбцам" уже отменили?
Иванов;;Иван;;Иванович
Пробуйте функцией "текст по столбцам". Заранее мерси.
никаких проблем, разделителем и будет ";" ексель проигнорит двойной символ, но будут пустые столбцы, можно включить "считать последовательные разделители одним" и придёт счастье.
Ок, обьясню по другому - ЭТОТ способ тоже работает. Ситуации бывают разные.
По своему опыту - ни в жизни не буду использовать встроенный обработчик - это лишние 3 экрана, которые мне нафиг не нужны.
Ничто не сможет остановить уверенного в себе человека, если он захочет усложнить себе жизнь ))
Эти все тыкания мышкой каждый раз - нафиг не нужны никому.
В 90% случаев - у вас, для ваших нужд, будет несколько наборов данных с разными форматами.
Намного удобнее подогнать под себя несколько формул, чем бездумно повторять одни и те же действия, или наблюдать за работой макроса.
Да пользуйтесь стандартным функционалом - кто ж вам запретит) А можно и описанным способом. Разные решения под разные задачи.
Текст по столбцам - Поставить галочки - Точка с запятой и Считать последовательные разделители одним. Никаких проблем. Скрин не могу приложить - ругается на рейтинг))
Если вот сейчас и основатель группы не понял о чем пост - пойду долго думать))
и если мозгов чуть больше чем нихуя то просто прочитав как меняете вы можно исправить ваш рецепт, сам принцип, под свою ситуацию.
Тут, скорее, не программирование, а автоматизация.. все-же с программированием в экселе - беда бедовая
Все такие умные, еще ругаются, написали бы пост сами, как по вашему верно! А автору поста спасибо поскакала пробовать
Вопрос практический - есть выгрузка фио из пропринетарного ПО которое внутри не поправить, выгрузка идет в ограниченное по длине поле, т.е. для разных людей фио может занимать 1-2-3-4-5 строк...есть возможно без макросов настроить автомат сцепления, зная что учетный номер человека в программе стоит в ячейке напротив начала записи нового фио.?
Скрин покажите. Есть, но надо посмотреть прям точно о чем речь. Скрин можно сделать просто похожий, достаточно 2-3 строк
так примерно, набрал для примера, там еще такой столбец не один, но если в других потеря части не критична - то фио, да
при этом уточняю, что изначально - файл выгрузки формата txt, с разделителями вида "!" по которым я просто выставляю импорт столбцов фиксированного размера, а результат преобразования - на примере.
минимум 1 рабочий способ, время на реализацию - минута-полторы.
писать об этом статейку?)
Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1
Пацан сказал - пацан сделал)
Если нужно подробнее - можем списаться приватно, скинете выгрузку, тогда уже будет прям 100% решение.
спасибо, посмотрел, решения и ваши и предложенные ниже вполне рабочие, буду пользоваться...
Одна поправочка: нужно наложить Автофильтр на результирующие столбцы, чтобы увидеть СПИСОК имён и фамилий, и если там явно неадекватные - сразу отфильтровать по ним и исправить исходные данные так чтобы нормально делилось.
Про фильтры дальше будет) Но в принципе это решается зумом и пробежать глазками...
В фильтре списочек уже уникальный, по нему пробежаться проще. К тому же можно фильтрануть пустые значения - и посмотреть что в других столбцах. Так просто время сэкономишь.
Задача такая - у меня есть полные ФИО, а в приказ надо херачить фио и инициалы. Может ли Эксель автоматически заменить ФИО на фамилию и инициалы, типа Иванов Иван Иванович на Иванов И.И.??? Как??
Хм. Я вот только вот это не понял:
ВБА ващет для того и предназначен, чтоб не мудрить дополнительные листы и километровые формулы в ячейках.Один раз напрягся, написал функцию, а потом в ячейках просто её вызываешь, типа GetFirstName(E6)
Другая религия - Mac Os. Вот и весь ответ. Да, VBA условно на маке работает... но только условно.
Хм. У меня, увы, пока нет мака — но насколько я знаю из мануала, вся разница — VBA на маке работает в песочнице, отчего не имеет доступа к сторонним объектам COM, процессам и нативным вызовам WinAPI.
Это важно только в довольно специфических задачах, которые нормальные люди на VBA обычно не решают, хотя в принципе могут.
Функционал "внутренних вычислений" в вба абсолютно идентичен в маке и винде.
А насколько я знаю - это первостатейный мазохизм. Пробовал и неоднократно - то Эксель крашнется, то еще какие-нибудь веселые радости. Зачем мне это все надо, если все задачи Экселя можно решить в Экселе? А если нельзя - то и не надо ее там решать)
VBA — это естественная часть экселя, примерно так же, как ваши ноги — ваша естественная часть.
И использовать вба в экселе так же естественно, как использовать ноги для перемещения. Конечно, вы можете отказаться от использования ног и перемещаться, ползая на руках — но это будет не очень умно и совсем не удобно. Вот и с вба так же. Да, без него иногда можно обойтись, но часто это будет не очень умно и совсем не удобно.
Разумеется, вба не панацея: многие вещи в экселе можно легко сделать без него — но есть множество задач, оптимальное решение которых именно в применении вба, даже когда это решение не единственное.
)))))))))))
Откройте файлик с VBA в Numbers / Libra, ок?
А вот если у человека по паспорту имя "Хорхе Луис Мария" — тут обработчик сдохнет.
На одном имени из скольких? Из 100? Из 500? Это можно дообработать и руками)
пропустил, это одно слово
Спасибо автору за пост!
Очень интересно. Спасибо. Подписался, буду ждать продолжения.
Всегда пожалуйста)
Вам бы лично что было бы интересно? Может быть какие-нибудь предпочтения?
Да, мне интересно. У меня в принципе такая же как у вас история потому, что видимо одного года. Тоже в школе учили бейсик ещё на Ямахах. Потом в институте тоже я единственный из группы уже знал, что это такое. Потом появился допотопный комп дома. Ну и по работе использовал по мере возможности эксель для упрощения элементарных и не очень задач т.к. я инженер-конструктор. Навскидку не скажу что сейчас мне интересно было-бы. Из последнего заинтересовали нейросети и использование их в эксель. Они кстати работают с большими базами данных.
Я поведенческие привычки прогнозирую на основе выгрузок и эксель-обработки. Грубо- вероятность того, что человек совершит покупку в %, что является основанием для контакта с ним уже менеджером. В CRM когда попробовал обьяснить что надо - посмотрели как на идиота, залепили 50 часов работы... Написал в экселе за часа полтора, только файлы вышли по 10мб каждый)))
Мне было интересно! Сама раз в месяц бодаюсь с выгрузками. Интересно бывает посмотреть, как люди решают типовые проблемы массивов данных, содержащих подубную инфу.
коллега)))