Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти и заменить сразу несколько значений в Excel?

Как найти и заменить несколько значений в Excel?

Например, у меня есть столбец с 18000 доменами и другой список с 210 000 доменами, где эти 18000 доменов могут быть найдены. CTRL + H позволит мне выполнить поиск и замену, а в поле поиска мне нужно ввести все 18000 доменов, например * domain1.com *, * domain2.com *, * domain3.com *, и заменить их пустыми строками. Когда я пытаюсь сделать это с помощью find and replace из excel, я не могу добавить более одного значения в поле Find. Как я могу сделать это для нескольких значений?

1 ответ

  • Как найти и заменить несколько текстов, используя win32com.client в python

Я пытаюсь добавить несколько строковых значений в документ Word с помощью функции find and replace, используя библиотеку win32com.client Python. Я могу найти и заменить одно значение, но не знаю, как это сделать для нескольких значений. Вот что у меня есть на данный момент: import win32com.client.

Не мог бы кто-нибудь помочь мне? Рабочая книга Excel содержит пробелы, которые я хочу найти и заменить. Чтобы заменить все экземпляры in the current worksheet I am on на , мне нужно, чтобы он искал текущий рабочий лист в рабочем листе, на котором я нахожусь в рабочей книге. Может ли кто-нибудь помочь мне с этим?

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

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

Надеюсь, это вам поможет.

Вы можете использовать подстановочные знаки в функции find/replace, поэтому в вашем случае можно использовать что-то вроде

Домен* в поле find, а в поле erstatte ничего нет.

Похожие вопросы:

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

Я хотел бы знать, как я могу использовать заменяющее слово, которое никогда не будет одинаковым. В принципе, я хотел бы найти и заменить слово x, скажем, на 5 строк со словом A, a.

Можно ли заменить несколько значений в шаблоне FreeMarker? Например, если я хочу заменить a и b на c, я должен сделать это: $ но я бы предпочел.

Этот вопрос касается функции find and replace с использованием библиотеки win32com.client Python для добавления нескольких строк в документ Word. В этом случае я могу найти одно значение и заменить его, но я не знаю остальных.

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

Должен существовать способ поиска и замены текста в документе Word на основе данных в Excel Более конкретно: Колонка a = текст, который я хочу найти Колонка B = текст, которым я хочу заменить текст, найденный в колонке a.

У меня есть 2 таблицы Excel, в которых я хочу найти и заменить значения, но я хочу иметь несколько значений замены для замены одного совпадающего значения. Лист 1: Лист 2: Сопоставить значение.

Читайте так же:
Как найти и заменить в верхнем или нижнем колонтитуле в Excel?

Система, определенная в Excel, была создана с тремя входами (x1, x2, x3). Я пытаюсь найти влияние этих трех входов на выход (y). Есть только одна ячейка, через которую каждый вход может быть подан в систему. вставить переменные списка.

У меня есть очень большой лист Excel с 1000 значениями, содержащими более 200 различных стран, я хочу изменить каждую страну на зону 1, зону 2, зону 3 . на зону 10 и т.д. Я также хочу иметь.

1_6_2014__12-20-00_AM в Excel, могу ли я использовать функцию, в которой я заменяю первые два на /, следующие два на пробел, и последний _ тоже на пробел. может быть.

excel подбор слагаемых для нужной суммы

Хороший инструмент — надстройка Поиск решения в MS Excel!

Например, вы можете использовать его для поиска варианта, в котором различные числа могут превратиться в определенную сумму (например, вам нужно посмотреть, какие счета могут превратиться в платежи). Предположим, вы хотите найти сумму 10 заданных чисел:

Для начала активируйте надстройку или убедитесь, что она активирована (в Excel 2013): Файл / Параметры, раздел Надстройки, выберите Управление: Надстройки Excel, нажмите Перейти к. Установите флажок Поиск решения и нажмите OK.

На ленте под вкладкой Данные появится инструмент Поиск решения:

Теперь нужно придумать, как мы можем параметрами в разных строках «подбирать» сумму. Я сделал вариант, когда в столбце мы указываем множитель 0 или 1, в соседнем столбце считаем произведение, и потом значения складываем в итоговую сумму:

  • в ячейках столбца B указываем 0 или 1 (сейчас неважно, что конкретно)
  • в ячейке C4 формула =A4*B4
  • в ячейках C5:C14 — аналогично, с учетом номера строки
  • в ячейке C3 формула =СУММ(C4:C14)

Теперь запустим Поиск решения. И заполним его:

  • Оптимизировать целевую функцию: $C$3
  • До: Значения: 10
  • Изменяя ячейки переменных: $B$4:$B$14
  • В соответствии с ограничениями: — добавляем (кнопка Добавить) три условия: 1) значения переменных должны быть целые, 2) значения должны быть >= 0, 3) 2) значения должны быть

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

Реальные сценарии могут отличаться от указанных.

  • Например, мы выгрузили из интернет-банка все платежи, которые поступили на наш счет за последний месяц. Один из клиентов разбивает сумму своего платежа на несколько отдельных счетов и платит частями. Мы знаем общую сумму оплаты и количество счетов, но не знаем их сумм. Надо подобрать те суммы в истории платежей, которые дадут в общем заданное значение.
  • У нас есть несколько рулонов стали (линолеума, бумаги. ), из которых надо подобрать под заказ те, что дадут заданную длину.
  • Блэкджек или в народе «очко». Надо набрать карты суммарной стоимостью максимально близкой к 21 баллу, но не превысить этот порог.

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

Читайте так же:
Как извлечь расширение файла из рабочего листа?

Рассмотрим некоторые способы решения такой проблемы в Excel.

Способ 1. Надстройка Поиск решения (Solver)

Это дополнение поставляется вместе с Excel и предназначено для решения задач линейной и нелинейной оптимизации с набором ограничений в общем случае. Для его подключения вам необходимо:

  • в Excel 2007 и новее зайти Файл — Параметры Excel — Надстройки — Перейти (File — Excel Options — Add-ins — Go)
  • в Excel 2003 и старше — открыть меню Сервис — Надстройки (Tools — Add-ins)

И установите соответствующий флажок. Команда появится на вкладке или в меню Данные.

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

  • Диапазон A1:A20 содержит наши числа, из которых мы будем выбирать нужные, чтобы «вписаться» в заданную сумму.
  • Диапазон В1:B20 будет своего рода набором переключателей, т.е. будет содержать нули или единички, показывая, отбираем мы данное число в выборку или нет.
  • В ячейке E2 стоит обычная автосумма всех единичек по столбцу B, подсчитывающая кол-во выбранных чисел.
  • В ячейке E3 с помощью функции СУММПРОИЗВ (SUMPRODUCT) считается сумма попарных произведений ячеек из столбцов А и B (то есть A1*B1+A2*B2+A3*B3+. ). Фактически, здесь подсчитывается сумма чисел из столбца А, отобранных единичками из столбца В.
  • В розовую ячейку E4 пользователь вводит желаемую сумму для подбора.
  • В ячейке E5 вычисляется абсолютное по модулю значение погрешности подбора с целью ее будущей минимизации.
  • Все желтых ячейках Е8:E17 хотелось бы получить список отобранных чисел, т.е. тех чисел из столбца А, напротив которых в столбце В есть единички. Для этого необходимо выделить сразу все (!) желтые ячейки и в них ввести вот такую формулу массива:

При вводе формулы вводите ее не как обычную формулу, а как формулу массива, например, нажмите Ctrl+Shift+Enter вместо Enter. Эта формула также используется в ВПР, который выводит сразу все найденные значения (а не только первое).

Теперь перейдем на вкладку (или в меню) Данные и запустим инструмент Поиск решения (Data — Solver):

В открывшемся окне выполните следующие действия:

  • Задать как целевую функцию (Target Cell) — ячейку вычисления погрешности подбора E5. Чуть ниже выбрать опцию — Минимум, т.к. мы хотим подобрать числа под заданную сумму с минимальной (а лучше даже нулевой) погрешностью.
  • В качестве изменяемых ячеек переменных (Changing cells) задать диапазон столбца переключателей B1:B20.
  • С помощью кнопки Добавить (Add) создать дополнительное условие на то, что ячейки диапазона B1:B20 должны быть бинарными (т.е. содержать только 0 или 1):


С помощью той же кнопки, при необходимости, создать ограничение на количество чисел в выборке. Например, если мы знаем, что сумма была разбита на 5 счетов, то:

После ввода всех параметров и ограничений мы нажимаем кнопку Найти решение (Solve). Этот процесс может занять от нескольких секунд до нескольких минут (в тяжелых случаях) и завершается появлением следующего окна:

Теперь вы можете либо оставить найденное решение (Save Found Solution), либо откатиться к предыдущим значениям (Restore Original Values).

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

Читайте так же:
Как найти и заменить цвет заливки в Excel?

Найденные комбинации можно сохранять виде сценариев (кнопка Сохранить сценарий), чтобы вернуться к нем позднее с помощью команды Данные — Анализ «что-если» — Диспетчер сценариев(Data — What-If Analysis — Scenario Manager):

Очень удобно иметь возможность отобразить все найденные решения, сохраненные как сценарии, в сравнительной таблице с помощью кнопки Report (Summary):

Способ 2. Макрос подбора

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

Для использования макроса нажмите сочетание Alt+F11, в открывшемся окне редактора Visual Basic вставьте новый модуль через меню Insert — Module и скопируйте туда этот код:

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

в Excel 2007 и новее зайти Файл — Параметры Excel — Надстройки — Перейти
в Excel 2003 и старше — открыть меню Сервис — Надстройки

и установить соответствующий флажок. Тогда на вкладке или в меню Данные появится нужная команда.
Способ 2. Макрос подбора

Excel подстановка значений из другой таблицы

У меня есть одна задачка, и уже третий день ломаю голову – не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут – скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация) .

Заранее спасибо. Андрей, Москва.

Привет всем!

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

Пример работы с функцией ВПР

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

Две таблицы в Excel – сравниваем первые столбцы

Вот, как это сделать.

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

A2 – значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 – полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок "$" – он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

2 – номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца – то значение можно было бы копировать из 2-го или 3-го столбца);

Читайте так же:
Как изменить цвет формы в зависимости от значения ячейки в Excel?

ЛОЖЬ – ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какой должна быть формула

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

Это значение было обнаружено и автоматически заменено

Чтобы цена была проставлена и для других наименований товара – просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растянуть формулу (копирование формулы в другие ячейки)

После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали – будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!

Значения из одной таблицы подставляются в другую.

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

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

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

Синтаксис ВПР

V PR означает «Вертикальный просмотр». Эта команда переносит данные между столбцами. Для работы со строками существует горизонтальный просмотр — GPR.

Аргументы для этой функции следующие

  • Желаемое значение — значение, которое необходимо найти в таблице;
  • Таблица — таблица данных, из которой будут извлечены данные. В частности, массив, содержащий данные, должен находиться справа от исходного массива, иначе функции VPR не будут работать;
  • Номер столбца массива, из которого будут извлечены значения;
  • Вид диапазона — логический аргумент, определяющий точность или приближение значения (0 или 1).

Как перемещать данные с помощью ВПР?

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

В ячейку D3 нужно извлечь цену гречки из соответствующей таблицы. Напишите =PR и заполните аргументы.

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

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

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

Интервалы отображения — установите значение 0, так как нам нужны точные значения, а не приблизительные.

От правой таблицы к левой таблице цена на гречку выросла.

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

Читайте так же:
Как найти и перечислить все ссылки (внешние ссылки) в Excel?

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

  • 22 Апрель, 2011 –
  • Уроки Excel –
  • Tags : таблицы excel, уроки excel, эксель данные
  • 75 Comments

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

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

Для решения этой проблемы я предлагаю два варианта.

Вариант 1. Используйте функцию VPR. пример загрузки

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

Все, мы вызываем функцию FFT. Чтобы вставить цену, щелкните в ячейке, куда будет введена цена (в нашем примере — C5), затем щелкните значок fx на панели инструментов (или Вставка-функция) и в открывшемся окне выберите ссылки и массивы, а затем БПФ. Как показано на рис.

И нажмите кнопку OK. Настройка параметров подстановки открывает следующее окно:

Искомое значение — щелкните в ячейке с искомым значением, которое для нас является кормом для кошек.

Таблица — это таблица, из которой берутся данные. Щелкните на квадрате с красной стрелкой и обведите мышью нашу таблицу прайс-листов, нажмите Enter

Здесь нужно указать один столбец — номер таблицы, из которой берутся цены. Первый столбец — это название, а второй — цена. Таким образом, мы ставим число 2

интервальный просмотр – здесь можно ввести либо ЛОЖЬ либо ИСТИНА. Других вариантов нет. Можно либо словами написать, либо ввести цифру 0 или 1. 0-ЛОЖЬ, 1-ИСТИНА. Если вводим ЛОЖЬ — выполняется поиск точного соответствия заданному параметру, если вы введете ИСТИНА, то таким образом Вы даете разрешение на поиск приблизительно соответствия, то есть поиск максимально похожего заданному параметру. Чтобы было меньше ошибок, лучше всегда указывать ЛОЖЬ, т.е. поиск точного соответствия.

Вот и все, нажмите OK и будьте счастливы 🙂

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

В новом открывшемся окне пишем имя диапазона, например «прайс»

И тогда в формуле ВПР можно просто впечатать имя диапазона


И второй способ решения данной задачи — подстановка данных в excel через функцию СУММЕСЛИ

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector