Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Найти и заменить сразу несколько значений - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Найти и заменить сразу несколько значений (Формулы/Formulas)
Найти и заменить сразу несколько значений
111 Дата: Понедельник, 18.09.2023, 13:14 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2013
Добрый день!
Я ищу решение для поиска определенных значений во всех формулах таблицы Google и, когда они будут найдены, должны быть заменены на другие.
В табличном документе для этого будет создан лист, в столбце A будут перечислены значения, которые необходимо заменить, а в столбце B - на которые нужно заменить.
 
Ответить
СообщениеДобрый день!
Я ищу решение для поиска определенных значений во всех формулах таблицы Google и, когда они будут найдены, должны быть заменены на другие.
В табличном документе для этого будет создан лист, в столбце A будут перечислены значения, которые необходимо заменить, а в столбце B - на которые нужно заменить.

Автор - 111
Дата добавления - 18.09.2023 в 13:14
Gustav Дата: Вторник, 03.10.2023, 00:10 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2733
Репутация: 1136 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Признаюсь, не смог особо нафантазировать случаи, когда требуется подобная модификация формул по частям сразу во многих местах файла. Но коль задача всё же востребована, то попробовал ее решить, исходя из формальной логики, без привязки к конкретной жизненной ситуации. Хотя в последний момент перед публикацией всё же пришла в голову такая (и подозреваю, нередкая) ситуация, как создание месячного листа-шаблона с формулами, тиражирование его на многие месяцы, а потом обнаружение некоторой ошибки в формулах и необходимость соответствующей коррекции в уже растиражированных рабочих листах.

Итак, задачу выполняет созданная процедура replaceFormulaParts (код см. ниже). До ее выполнения должен быть подготовлен список замен на служебном листе "Replacer". Список имеет заголовки колонок в ячейках A1:C1: pattern, flags, replacement. Для "централизованного" задания этих заголовков можно поместить в ячейку A1 такую формулу:
Код
={"pattern" \ "flags" \ "replacement"}


Ниже заголовков в трёх колонках отображается информация о заменах. В список для обработки включаются только строки из диапазона A2:C, имеющие непустые ячейки в колонке A (pattern). К одной и той же формуле (из одной ячейки) правила замены применяются последовательно сверху вниз, т.е. непустая (по колонке A) замена, расположенная в строке с наименьшим номером, выполняется первой.

Шаблоны для замены, помещенные в первую колонку "pattern", оформляются в соответствии с правилам регулярных выражений (объект RegExp). Во второй колонке "flags" при этом хранятся флаги регулярных выражений, т.е. специальные опции которые влияют на поиск или предоставляют дополнительную информацию (подробнее можно здесь: https://learn.javascript.ru/regexp-introduction). Так, указание флага "i" делает поиск независящим от регистра, а при указанном флаге "g" ищутся все совпадения, а не только первое. Например, для замены всех "F" и "f" на "G" нужно указать: в первой колонке - F (или f) , во второй - ig , в третьей - G . Если оставить ячейку второй колонки пустой, то на G будет будет замен только самый первый символ F (или f, если в первой колонке была указана строчная "эф").

В процедуре предусмотрен массив названий листов-исключений, т.е. листов, которые не следует обрабатывать. Соответственно, если лист не должен быть подвергнут замене формул, то его имя следует включить в массив excludedSheets. Как минимум, по здравому смыслу, сюда нужно включить лист "Replacer", содержащий сам справочник замен. Лист, указанный в массиве исключений, не обязательно должен физически присутствовать в файле (т.е. ошибки не будет):
[vba]
Код
const excludedSheets = ["Replacer", "ЛистФизическиНеСуществующий"];
[/vba]

А вот и сама процедура (после копирования в свой документ выполните в редакторе скриптов команду: ПКМ \ Форматировать документ):
[vba]
Код
function replaceFormulaParts() {
  const ss = SpreadsheetApp.getActive();

  // массив названий листов-исключений (листы, которые не обрабатываются)
  const excludedSheets = ["Replacer", "ЛистФизическиНеСуществующий"];

  // лист Replacer - справочник замен с заголовками в A1:C1: ={"pattern" \ "flags" \ "replacement"}
  let values = ss.getSheetByName("Replacer").getDataRange().getValues();
  values = values.filter(row => row[0]); // фильтруем по первому столбцу - непустому pattern
  const headers = values[0];

  // готовим массив замен rpl, преобразуя каждую строку в объект с именованными свойствами
  // по подходу отсюда: https://kierandixon.com/convert....objects
  const rpl = []; // массив строк-объектов
  for (let i = 1; i < values.length; i++) {
    const entry = {};
    for (let j = 0; j < headers.length; j++) {
      const key = headers[j];
      if (key === '') continue;
      entry[key] = values[i][j];
    }
    rpl.push(entry);
  }
  if (!rpl.length) {
    Logger.log('Нет подходящих шаблонов для замены');
    return;
  }

  // цикл по всем рабочим листам файла, за исключением необрабатываемых
  ss.getSheets().forEach(sh => {
    if (!~excludedSheets.indexOf(sh.getSheetName())) {
      // выполнение продолжается только для листов, не указанных в списке исключений

      // для очередного листа получаем массив формул из заполненной части листа
      const range = sh.getDataRange();
      const formulas = range.getFormulas();

      // перебираем полученный массив по строкам (i) и столбцам (j)
      for (let i = 0; i < formulas.length; i++) {
        for (let j = 0; j < formulas[0].length; j++) {
          if (formulas[i][j]) {
            // если очередной элемент массива является формулой (непустая строка),
            // то производим замены согласно заданному списку замен (лист "Replacer")
            let strFormula = formulas[i][j];
            for (let k = 0; k < rpl.length; k++) {
              const regex = new RegExp(rpl[k].pattern, rpl[k].flags);
              strFormula = strFormula.replace(regex, rpl[k].replacement);
            }
            // записать формулу на лист, если были изменения
            if (strFormula != formulas[i][j]) {
              range.getCell(i + 1, j + 1).setFormula(strFormula);
            }
          }
        }
      }
    }
  });
}
[/vba]


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеПризнаюсь, не смог особо нафантазировать случаи, когда требуется подобная модификация формул по частям сразу во многих местах файла. Но коль задача всё же востребована, то попробовал ее решить, исходя из формальной логики, без привязки к конкретной жизненной ситуации. Хотя в последний момент перед публикацией всё же пришла в голову такая (и подозреваю, нередкая) ситуация, как создание месячного листа-шаблона с формулами, тиражирование его на многие месяцы, а потом обнаружение некоторой ошибки в формулах и необходимость соответствующей коррекции в уже растиражированных рабочих листах.

Итак, задачу выполняет созданная процедура replaceFormulaParts (код см. ниже). До ее выполнения должен быть подготовлен список замен на служебном листе "Replacer". Список имеет заголовки колонок в ячейках A1:C1: pattern, flags, replacement. Для "централизованного" задания этих заголовков можно поместить в ячейку A1 такую формулу:
Код
={"pattern" \ "flags" \ "replacement"}


Ниже заголовков в трёх колонках отображается информация о заменах. В список для обработки включаются только строки из диапазона A2:C, имеющие непустые ячейки в колонке A (pattern). К одной и той же формуле (из одной ячейки) правила замены применяются последовательно сверху вниз, т.е. непустая (по колонке A) замена, расположенная в строке с наименьшим номером, выполняется первой.

Шаблоны для замены, помещенные в первую колонку "pattern", оформляются в соответствии с правилам регулярных выражений (объект RegExp). Во второй колонке "flags" при этом хранятся флаги регулярных выражений, т.е. специальные опции которые влияют на поиск или предоставляют дополнительную информацию (подробнее можно здесь: https://learn.javascript.ru/regexp-introduction). Так, указание флага "i" делает поиск независящим от регистра, а при указанном флаге "g" ищутся все совпадения, а не только первое. Например, для замены всех "F" и "f" на "G" нужно указать: в первой колонке - F (или f) , во второй - ig , в третьей - G . Если оставить ячейку второй колонки пустой, то на G будет будет замен только самый первый символ F (или f, если в первой колонке была указана строчная "эф").

В процедуре предусмотрен массив названий листов-исключений, т.е. листов, которые не следует обрабатывать. Соответственно, если лист не должен быть подвергнут замене формул, то его имя следует включить в массив excludedSheets. Как минимум, по здравому смыслу, сюда нужно включить лист "Replacer", содержащий сам справочник замен. Лист, указанный в массиве исключений, не обязательно должен физически присутствовать в файле (т.е. ошибки не будет):
[vba]
Код
const excludedSheets = ["Replacer", "ЛистФизическиНеСуществующий"];
[/vba]

А вот и сама процедура (после копирования в свой документ выполните в редакторе скриптов команду: ПКМ \ Форматировать документ):
[vba]
Код
function replaceFormulaParts() {
  const ss = SpreadsheetApp.getActive();

  // массив названий листов-исключений (листы, которые не обрабатываются)
  const excludedSheets = ["Replacer", "ЛистФизическиНеСуществующий"];

  // лист Replacer - справочник замен с заголовками в A1:C1: ={"pattern" \ "flags" \ "replacement"}
  let values = ss.getSheetByName("Replacer").getDataRange().getValues();
  values = values.filter(row => row[0]); // фильтруем по первому столбцу - непустому pattern
  const headers = values[0];

  // готовим массив замен rpl, преобразуя каждую строку в объект с именованными свойствами
  // по подходу отсюда: https://kierandixon.com/convert....objects
  const rpl = []; // массив строк-объектов
  for (let i = 1; i < values.length; i++) {
    const entry = {};
    for (let j = 0; j < headers.length; j++) {
      const key = headers[j];
      if (key === '') continue;
      entry[key] = values[i][j];
    }
    rpl.push(entry);
  }
  if (!rpl.length) {
    Logger.log('Нет подходящих шаблонов для замены');
    return;
  }

  // цикл по всем рабочим листам файла, за исключением необрабатываемых
  ss.getSheets().forEach(sh => {
    if (!~excludedSheets.indexOf(sh.getSheetName())) {
      // выполнение продолжается только для листов, не указанных в списке исключений

      // для очередного листа получаем массив формул из заполненной части листа
      const range = sh.getDataRange();
      const formulas = range.getFormulas();

      // перебираем полученный массив по строкам (i) и столбцам (j)
      for (let i = 0; i < formulas.length; i++) {
        for (let j = 0; j < formulas[0].length; j++) {
          if (formulas[i][j]) {
            // если очередной элемент массива является формулой (непустая строка),
            // то производим замены согласно заданному списку замен (лист "Replacer")
            let strFormula = formulas[i][j];
            for (let k = 0; k < rpl.length; k++) {
              const regex = new RegExp(rpl[k].pattern, rpl[k].flags);
              strFormula = strFormula.replace(regex, rpl[k].replacement);
            }
            // записать формулу на лист, если были изменения
            if (strFormula != formulas[i][j]) {
              range.getCell(i + 1, j + 1).setFormula(strFormula);
            }
          }
        }
      }
    }
  });
}
[/vba]

Автор - Gustav
Дата добавления - 03.10.2023 в 00:10
111 Дата: Четверг, 04.01.2024, 00:42 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2013
Gustav, спасибо!
 
Ответить
СообщениеGustav, спасибо!

Автор - 111
Дата добавления - 04.01.2024 в 00:42
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Найти и заменить сразу несколько значений (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!