Руководство. Очистка и нормализация данных книги Excel

Статья
11/29/2023

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

Совет

Если вы не знакомы со скриптами Office, рекомендуем начать с руководства по созданию и форматированию таблицы Excel. Сценарии Office используют TypeScript, и этот учебник предназначен для пользователей с начальным и средним уровнем знаний по JavaScript или TypeScript. Если вы впервые работаете с JavaScript, рекомендуем начать с учебника Mozilla по JavaScript.

Предварительные условия

Для работы с этим руководством вам потребуется доступ к скриптам Office. Если вкладка "Автоматизация" не отображается, ознакомьтесь с разделом Поддержка платформы .

Чтение ячейки

Сценарии, созданные с помощью средства записи действий, могут только записывать информацию в книгу. С помощью редактора кода можно редактировать и создавать сценарии, которые также читают данные из книги.

Начните с создания скрипта, который считывает данные и действует на основе прочитанного. На протяжении всего учебника вы будете работать с примером банковского выписки. Эта выписка объединяет чековую выписку и выписку по кредиту. К сожалению, банк сообщает о том, что баланс меняется по-разному. В чековой выписке доходы указываются как положительный кредит, а расходы — в виде отрицательного дебета. В выписке по кредиту все наоборот.

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

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

Скопируйте следующие данные и вставьте их на новый лист, начиная с ячейки A1.

Дата	Счет	Описание	Дебет	Кредит
10.10.2019	Чековый	Виноградник Coho	–20,05
11.10.2019	Кредитный	Телефонная компания	99,95
13.10.2019	Кредитный	Виноградник Coho	154,43
15.10.2019	Чековый	Внешний депозит		1000
20.10.2019	Кредитный	Виноградник Coho — возмещение		–35,45
25.10.2019	Чековый	Органическая компания "Лучшее для вас"	–85,64
01.11.2019	Чековый	Внешний депозит		1000

Перейдите на вкладку Автоматизация и выберите Создать скрипт.
Очистите форматирование. Это финансовый документ, поэтому скрипт изменит форматирование чисел в столбцах Дебет и Кредит , чтобы отобразить значения в виде сумм в долларах. Кроме того, сценарий должен соответствовать ширине столбца данным.

Замените содержимое сценария следующим кодом:
```
function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet.
    let selectedSheet = workbook.getActiveWorksheet();

    // Format the range to display numerical dollar amounts.
    selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");

    // Fit the width of all the used columns to the data.
    selectedSheet.getUsedRange().getFormat().autofitColumns();
}
```
Теперь считывает значение из одного из числовых столбцов. Добавьте следующий код в конец скрипта (до закрытия }).
```
// Get the value of cell D2.
let range = selectedSheet.getRange("D2");
console.log(range.getValues());
```
Запустите сценарий.
В консоли должно отображаться следующее: [Array[1]]. Это не число, так как диапазоны являются двухмерными массивами данных. Этот двухмерный диапазон напрямую регистрируется в консоли. К счастью, редактор кода позволяет просмотреть содержимое массива.
Когда двухмерный массив регистрируется в консоли, она группирует значения столбцов под каждой строкой. Разверните журнал массива, выбрав синий треугольник.
Разверните второй уровень массива, выбрав только что появившиеся синие треугольники. Должно отобразиться следующее:

Изменение значения ячейки

Теперь, когда скрипт может считывать данные, используйте их для изменения книги. Сделайте значение ячейки D2 положительным с помощью Math.abs функции . Объект Math содержит множество функций, к которым имеют доступ сценарии. Дополнительные сведения о Math и других встроенных объектах можно найти в статье Использование встроенных объектов JavaScript в сценариях Office.

Используйте getValue методы и setValue для изменения значения ячейки. Эти методы применимы к одной ячейке. При обработке диапазонов, включающих несколько ячеек, нужно использовать getValues и setValues. Добавьте следующий код в конец скрипта.
```
// Run the `Math.abs` method with the value at D2 and apply that value back to D2.
let positiveValue = Math.abs(range.getValue() as number);
range.setValue(positiveValue);
```
Примечание.

Мы приводим возвращаемое значение range.getValue() к number, используя ключевое слово as. Это необходимо, так как диапазон может включать строки, числа или логические значения. В данном случае нам явно нужно число.
Значение ячейки D2 теперь должно быть положительным.

Изменение значений столбца

Теперь, когда вы знаете, как считывать и записывать данные в одну ячейку, можно обобщить сценарий для работы со всеми дебетовыми и кредитными столбцами.

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

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet.
    let selectedSheet = workbook.getActiveWorksheet();

    // Format the range to display numerical dollar amounts.
    selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");

    // Fit the width of all the used columns to the data.
    selectedSheet.getUsedRange().getFormat().autofitColumns();
}

Добавьте цикл в конце сценария, выполняющий итерацию в строках двух последних столбцов. Для каждой ячейки сценарий устанавливает текущее абсолютное значение.

Обратите внимание, что массив, определяющий расположения ячеек, отсчитывается от нуля. Это означает, что ячейка A1 имеет значение range[0][0].
```
// Get the values of the used range.
let range = selectedSheet.getUsedRange();
let rangeValues = range.getValues();

// Iterate over the fourth and fifth columns and set their values to their absolute value.
let rowCount = range.getRowCount();
for (let i = 1; i < rowCount; i++) {
    // The column at index 3 is column "4" in the worksheet.
    if (rangeValues[i][3] != 0) {
        let positiveValue = Math.abs(rangeValues[i][3] as number);
        selectedSheet.getCell(i, 3).setValue(positiveValue);
    }

    // The column at index 4 is column "5" in the worksheet.
    if (rangeValues[i][4] != 0) {
        let positiveValue = Math.abs(rangeValues[i][4] as number);
        selectedSheet.getCell(i, 4).setValue(positiveValue);
    }
}
```
Эта часть сценария выполняет несколько важных задач. Сначала она получает значения и количество строк используемого диапазона. Это позволяет скрипту просмотреть значения и узнать, когда остановить. Затем выполняется итерация в используемом диапазоне с проверкой каждой ячейки в столбцах Дебет или Кредит. Наконец, если значение в ячейке не равно 0, оно заменяется абсолютным значением. Скрипт игнорирует нули, поэтому можно оставить пустые ячейки в том виде, в который они были.
Запустите сценарий.

Теперь в вашем банковском выписке должны быть правильно отформатированы положительные числа.

Дальнейшие действия

Откройте редактор кода и попробуйте некоторые образцы сценариев для сценариев Office в Excel. Дополнительные сведения о создании скриптов Office см. в статье Основы сценариев Office в Excel .

В следующем наборе учебников по сценариям Office рассматривается использование сценариев Office с помощью Power Automate. Дополнительные сведения о преимуществах объединения двух платформ см. в статье Запуск сценариев Office с помощью Power Automate или руководство. Обновление электронной таблицы из потока Power Automate для создания потока Power Automate, использующего сценарий Office.

Поделиться через