Автоматически переносить данные из одной ячейки на разных вкладках листа в одну таблицу
В настоящее время я пытаюсь создать шаблон счета-фактуры со встроенным журналом, который позволит мне просто дублировать вкладку шаблона, переименовывать его в номер счета-фактуры, а затем выводить общую сумму задолженности на вкладку журналов, чтобы отслеживать, что мне причитается и когда. я получил чек.
В настоящее время в настройке он есть, поэтому сумма всегда находится в ячейке D21, но было бы здорово просто сохранить сумму в виде значения и вывести ее в журнал, чтобы она не всегда читала одну и ту же ячейку.
В настоящее время мне приходится вручную просматривать и помещать ='tab name'!D21 на каждую вкладку строк, что раздражает. Я пробовал перетаскивать вниз каждую строку, но к номеру ячейки добавляется только 1, а не к имени листа (оглядываясь назад, это было очевидно). Есть ли способ решить эту проблему?
Ответы 2
Если я правильно понимаю, у вас есть таблица из n листов. С каждого листа вы хотите скопировать определенную ячейку и использовать ее для заполнения таблицы на другом листе той же таблицы. И в настоящее время вы делаете это вручную для каждой строки в целевом столбце.
Было бы полезно иметь иллюстрированный пример структуры электронной таблицы, чтобы прояснить вопрос. Кроме того, было бы также полезно знать, какие инструменты вы хотите использовать для этого (только формулы IE, макросы, скрипты приложений и т. д.).
С помощью формул мы можем разрешать ссылки на ячейки, используя функцию ДВССЫЛ [1].
Вот пример:
Рассмотрим электронную таблицу с 6 листами, и мы хотим скопировать значение A1 первых 5 листов.
В Sheet6 мы можем использовать формулу: =ДВССЫЛ("Лист"&ROW()&"!A1")
Что для каждой строки будет выглядеть примерно так: =Sheet1!A1 в зависимости от номера строки, в которой находится формула. Таким образом, вы можете просто поместить его в верхний ряд и перетащить вниз.
Это предполагает, что ваши вкладки имеют соглашение об увеличении номеров, которое вы можете рассмотреть для облегчения процесса.
КОСВЕННЫЙ [1] https://support.google.com/docs/answer/3093377?hl=en
Вы также можете использовать скрипт приложений, чтобы:
- Получить все листы
- Для каждого листа прочитайте значение ячейки
- Запишите значение в последнюю строку определенного столбца на другом листе.
Для этого не потребуется определенного соглашения об именах для всех листов, но вам потребуется настроить сценарий.
Например:
/**
* @OnlyCurrentDoc
*/
function pupolateColumnFromSheets() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheets = ss.getSheets();
for (let i=0; i < sheets.length-1; i++) {
let value = sheets[i].getRange('A1').getValue();
let lastSheet = sheets[sheets.length-1];
lastSheet.getRange(`A${lastSheet.getLastRow()+1}`)
.setValue(value);
}
}
Эта функция копирует значение определенной ячейки со всех листов и записывает его в столбец А на последнем листе. Это не зависит от названий листов, но предполагает, что вы хотите записать значение на последний лист документа. Недостатком является отсутствие ссылки на ячейку, но ее можно настроить для записи формулы вместо значений. С помощью этого подхода вы также можете установить имя целевого листа.
/**
* @OnlyCurrentDoc
*/
const ss = SpreadsheetApp.getActiveSpreadsheet();
const targetSheetName = "Sheet6";
const targetSheet = ss.getSheetByName(targetSheetName);
function pupolateColumnFromSheets() {
let sheets = ss.getSheets();
sheets.forEach((sheet) => {
let sheetName = sheet.getSheetName();
if (sheetName != targetSheetName) {
targetSheet.getRange(`A${targetSheet.getLastRow()+1}`)
.setValue(`=${sheetName}!A1`);
}
});
}
В этом последнем примере мы пишем формулу для определенного столбца целевого листа, которая ссылается на определенную ячейку из всех других листов.
Я подготовил для вас несколько образцов таблиц.
Пример формул: https://docs.google.com/spreadsheets/d/14FwIA7dfg5M6YtNf95JU4FsPt4iEZte97ExalRLuB8o/edit?gid=0#gid=0
Пример сценария приложений:
https://docs.google.com/spreadsheets/d/10EZY1e5-6L8CryTF1cIBDC1hV-Q5jOorPKhlxMBV_UM/edit?gid=0#gid=0
Надеюсь, это поможет!
Вы можете ввести имена листов в один столбец на вкладке журналов, скажем, столбец A: счет-фактура 1, счет-фактура 2 и т. д., а затем вы можете использовать функцию INDIRECT(), чтобы перевернуть строки/значения в столбце A (счет-фактура 1, счет-фактура 2 и т. д.) в переменную.
Поскольку ваши данные на каждом листе соответствуют шаблону, вы легко сможете нажать Ctrl+D вниз по одному и тому же значению ячейки, но для разных имен листов.
Например, если ваши значения находились в ячейке B2 на листе «Счет1» и листе «Счет2»:
Column A Column B
Invoice1 =INDIRECT(A1&"!$B$2")
Invoice2 =INDIRECT(A2&"!$B$2")
Для более настраиваемого подхода я бы рекомендовал прочитать Apps Script.
Справочный материал:
Как сейчас написано, ваш ответ неясен. Пожалуйста, отредактируйте , чтобы добавить дополнительную информацию, которая поможет другим понять, как это относится к заданному вопросу. Более подробную информацию о том, как писать хорошие ответы, вы можете найти в справочном центре.