Все мы работаем с базами в той или иной степени. Есть куча софтов для работы с ними(удаление дублей, нормализация, сравнение и т.д.). Но бывает узконаправленная потребность в обработке базы: К примеру у нас есть база email
ass, мы прочекали только мыла и теперь нам нужно выбрать с нашей базы под эти мыла которые остались после нашего чека пароли. И как это реализовать не прибегая к танцам с бубном и поиску софта(если таковой есть) я вам покажу как быстро и просто это сделать с помощью формул Excel.
И так:
1.Копируем нашу базу в эксель, клацаем на верхнюю левую ячейку А1 и вставляем базу.
2.Разделяем базу на на 2 колонки мыло и пасс. Что бы это сделать выделяем нашу базу в экселе, сверху клацаем на вкладку данные -> текст по столбцам, там выбираем наш разделитель который в базе идет между мылом и паролем, нажимаем далее\готово. В итоге получаем столбец А - мыла, столбец В - пароли.
3.Далее в столбец С вставляем те мыла к которым нужно выбрать пароли с базы.
4. В ячейку D1 вставляем формулу:
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$С$1:$С$1013;0));"";A1)
Немного распишу о ней для тех кто работает впервые с экселем:
A1;$С$1:$С$1013;0 - здесь вы указываете формуле откуда брать значения, с чем сравнивать.
А1 берет мыло с ячейки А1 сравнивает с диапазоном С1:С1013 \\столько строк у меня было в примере, если у вас больше или меньше то у вас тут будет другое. ваше число.
"";A1 - выводит результат, если есть совпадения выводит значение самой ячейки, если нету то что в "", в нашем случае ничего между кавычек нет соответственно формула будет выводить пустую строку.
Нюансы: Формулу вставляйте в ячейку D1 на против первого значения которое в ячейке А1, как на скрине, это важно!
5.Теперь у нас в ячейке D1 есть формула которая сравнивает значения ячейки А1 с диапазоном в столбце С и выводит результат в D1, то есть она берет мыло с А1 сравнивает с массивом мыл в С столбце, и если оно там есть то выводит его в D1 как раз на против нашего мыла и пароля!
Теперь нам нужно сделать так что бы формула сравнила остальные мыла со столбца А, для этого нам нужно просто копировать ячейку D1 вниз, клацаем на D1 и тянем за правый нижний угол ячейки вниз, таким образом формула копируется вниз, но автоматически меняет сравниваемые ячейки: A1 -> A2 -> A3 ->... но при этом благодаря знакам $ в формуле диапазон в столбце С фиксирован.
6. В итоге получаем в столбце D результат, те же мыла к которым нужно подобрать пасс из базы, но они напротив паролей!
7. Соответственно в следующий столбец E, за результатами, копируем пароли со столбца В, потом выделяем оба столбца Е и В копируем их в тхт файл, меняем разделитель, нормализуем базу и вуаля. Можно конечно формулой убрать пароли на против пустых строк, но на первый раз не буду сильно заморачивать вам голову)
Хотя тут и много текста, с разьяснениями, на самом деле вся процедура обработки базы занимает меньше минуты, работать можно с базами довольно внушительных обьемов, так как Exel не написан на колене и не будет захлебываться от 10-ти миллионных и более баз. Плюс можно сохранить файл шаблон с формулой эксель и потом просто вставлять туда свою базу.
P.S. На скрине очепятка вместо "те мыла которые нужно убрать из базы" ---> "те мыла под которые нужно найти пароли"
P.P.S Если статья будет кому-либо полезна, то напишу еще на другие вариации и запросы работы с базами с помощью Excel, которые вы можете предложить.
ass, мы прочекали только мыла и теперь нам нужно выбрать с нашей базы под эти мыла которые остались после нашего чека пароли. И как это реализовать не прибегая к танцам с бубном и поиску софта(если таковой есть) я вам покажу как быстро и просто это сделать с помощью формул Excel.И так:
1.Копируем нашу базу в эксель, клацаем на верхнюю левую ячейку А1 и вставляем базу.
2.Разделяем базу на на 2 колонки мыло и пасс. Что бы это сделать выделяем нашу базу в экселе, сверху клацаем на вкладку данные -> текст по столбцам, там выбираем наш разделитель который в базе идет между мылом и паролем, нажимаем далее\готово. В итоге получаем столбец А - мыла, столбец В - пароли.
3.Далее в столбец С вставляем те мыла к которым нужно выбрать пароли с базы.
4. В ячейку D1 вставляем формулу:
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$С$1:$С$1013;0));"";A1)
Немного распишу о ней для тех кто работает впервые с экселем:
A1;$С$1:$С$1013;0 - здесь вы указываете формуле откуда брать значения, с чем сравнивать.
А1 берет мыло с ячейки А1 сравнивает с диапазоном С1:С1013 \\столько строк у меня было в примере, если у вас больше или меньше то у вас тут будет другое. ваше число.
"";A1 - выводит результат, если есть совпадения выводит значение самой ячейки, если нету то что в "", в нашем случае ничего между кавычек нет соответственно формула будет выводить пустую строку.
Нюансы: Формулу вставляйте в ячейку D1 на против первого значения которое в ячейке А1, как на скрине, это важно!
5.Теперь у нас в ячейке D1 есть формула которая сравнивает значения ячейки А1 с диапазоном в столбце С и выводит результат в D1, то есть она берет мыло с А1 сравнивает с массивом мыл в С столбце, и если оно там есть то выводит его в D1 как раз на против нашего мыла и пароля!
Теперь нам нужно сделать так что бы формула сравнила остальные мыла со столбца А, для этого нам нужно просто копировать ячейку D1 вниз, клацаем на D1 и тянем за правый нижний угол ячейки вниз, таким образом формула копируется вниз, но автоматически меняет сравниваемые ячейки: A1 -> A2 -> A3 ->... но при этом благодаря знакам $ в формуле диапазон в столбце С фиксирован.
6. В итоге получаем в столбце D результат, те же мыла к которым нужно подобрать пасс из базы, но они напротив паролей!
7. Соответственно в следующий столбец E, за результатами, копируем пароли со столбца В, потом выделяем оба столбца Е и В копируем их в тхт файл, меняем разделитель, нормализуем базу и вуаля. Можно конечно формулой убрать пароли на против пустых строк, но на первый раз не буду сильно заморачивать вам голову)
Хотя тут и много текста, с разьяснениями, на самом деле вся процедура обработки базы занимает меньше минуты, работать можно с базами довольно внушительных обьемов, так как Exel не написан на колене и не будет захлебываться от 10-ти миллионных и более баз. Плюс можно сохранить файл шаблон с формулой эксель и потом просто вставлять туда свою базу.
Пожалуйста,
Вход
или
Регистрация
для просмотра содержимого URL-адресов!
P.S. На скрине очепятка вместо "те мыла которые нужно убрать из базы" ---> "те мыла под которые нужно найти пароли"
P.P.S Если статья будет кому-либо полезна, то напишу еще на другие вариации и запросы работы с базами с помощью Excel, которые вы можете предложить.