Как я могу ссылаться на динамически расширяемые/сокращаемые таблицы Excel в Excel 2010?

Наша система использует электронные таблицы шаблонов, каждый шаблон имеет два листа: Data и RawData. RawData содержит таблицы Excel с данными, заполненными приложением. Пользователи несут ответственность за создание отчетов (на листе данных), которые они хотят, на основе данных в листе RawData. Теперь данные в таблицах могут различаться, сегодня в таблице может быть 5 строк, на следующий день в ней может быть 10 строк.

Если в RawData у меня есть таблица 1 со столбцом 1, как мне сослаться на данные в этом столбце на листе данных таким образом, чтобы учесть рост/уменьшение данных в таблице 1?

Я думал, что могу использовать формулу массива и ссылку и использовать формулу массива {= Table1 [column1]}, но когда я ее определяю, мне всегда нужно указывать фиксированное количество строк. Кажется, нет никакого способа указать количество строк/столбцов, до которых будет расширяться формула массива, и получить это число из формулы, аналогично тому, как работают динамические диапазоны. Если я выберу слишком много строк, формула массива помещает значение #NA в ячейки, которые выходят за пределы доступных элементов.

Спасибо

PS. Использование VBA не вариант.


excel excel-formula excel-2010
person costa    schedule 08.08.2012    source источник
comment
Вы определенно можете использовать что-то вроде =SUM(Table1[column1]), и это всегда будет относиться ко всему содержимому столбца1. Это то, что вы спрашиваете?   -  person Doug Glancy    schedule 08.08.2012
comment
Какие функции вы используете для ссылки на лист RawData?   -  person Stepan1010    schedule 08.08.2012
comment
Для этой ситуации есть отличный обходной путь формулы, но все они очень специфичны для конкретного случая, поэтому трудно понять, какой (если вообще какой-либо) метод решит вашу проблему. Обычно это гибрид ob countifs, match, if и offset, работающих вместе для динамического выбора и управления таблицей в заданном пространстве. Если вы можете предоставить много деталей макета таблицы на листе, я был бы рад дать ответ.   -  person danielpiestrak    schedule 08.08.2012
comment
@ Stepan1010: я хочу просто получить данные столбца как есть. Пользователь применит форматирование (в том числе условное форматирование). Идея состоит в том, что я хочу, чтобы данные расширялись, если исходные данные растут. Я не беспокоюсь об агрегатных формулах. Я знаю, что могу справиться с ними, даже когда данные уменьшаются или растут.   -  person costa    schedule 09.08.2012
comment
Чтобы дать вам более конкретный пример, предположим, что на листе RawData у меня есть Table1 со Column1, которая сегодня содержит значения 1, 2, 3, 4, 5. Пользователь хочет применить простое преобразование и умножить эти значения на 10, поэтому в таблице данных пользователю необходимо отобразить 10, 20, 30, 40 и 50. Это можно сделать, например, с помощью формулы массива. Но хитрость в том, что завтра, когда шаблон будет переоценен, Table1 может иметь 10 значений или 2 значения. Данные пользователя также должны увеличиваться или уменьшаться, и это то, что я ищу.   -  person costa    schedule 09.08.2012
comment
Есть ли несколько таблиц данных на листе RawData? Или это только один стол?   -  person Stepan1010    schedule 09.08.2012
comment
Да, столов несколько. Пользователь может создавать свои собственные отчеты из нескольких таблиц и выбирать нужные данные. Опять же, моя проблема связана с шаблоном, в котором вам нужны все данные в столбце таблицы, и вы не знаете, сколько строк будет в этой таблице.   -  person costa    schedule 09.08.2012
comment
@costa Это определенно выполнимо, но трудно понять, что вы пытаетесь сделать, без подробностей. Я сделал книгу с таблицами, которые извлекают данные из таблицы RawData, включая сводную таблицу, которая извлекается из различных таблиц. Я использовал много формул массива. В некоторых случаях я создавал формулу массива, которую нужно было вручную заполнять в нижние ячейки. Одна формула монстра используется для заполнения таблицы уникальными идентификаторами. Поскольку люди могут добавлять новые идентификаторы, мне нужно вручную расширить эту таблицу и перетащить формулу вниз, пока идентификаторы не перестанут отображаться.   -  person Zairja    schedule 09.08.2012
comment
Веселые времена! {=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(LodgTbl[PC], MATCH(0, COUNTIF($A$1:A1, LodgTbl[PC]), 0)),INDEX(MealTbl[PC], MATCH(0, COUNTIF($A$1:A1, MealTbl[PC]), 0))),INDEX(TransTbl[PC], MATCH(0, COUNTIF($A$1:A1, TransTbl[PC]), 0))),INDEX(MiscTbl[PC], MATCH(0, COUNTIF($A$1:A1, MiscTbl[PC]), 0))), "")}   -  person Zairja    schedule 09.08.2012
comment
И это было его другом для суммирования значений в той же строке, что и идентификатор (ПК): =IF([@PC]<>"", SUMIF(LodgTbl[PC],[@PC],LodgTbl[Cost])+ SUMIF(MiscTbl[PC],[@PC],MiscTbl[Cost])+ SUMIF(MealTbl[PC],[@PC],MealTbl[Cost])+ SUMIF(TransTbl[PC],[@PC],TransTbl[Cost]),"") Итак, формулы массива работают нормально, но нам нужно знать, что вы пытаетесь сделать.   -  person Zairja    schedule 09.08.2012
comment
@Zairja: Спасибо. Я думаю, вы сказали: «Мне нужно вручную расширить эту таблицу». Я не хочу ничего делать вручную, если данные растут или уменьшаются. Чтобы использовать другую парадигму, то, что я хочу сделать, похоже на представление sql. Если базовая таблица изменяется, представление также изменяется. Сводные таблицы в сочетании с динамическими диапазонами на самом деле ближе всего к тому, что я хочу, потому что в них отражаются изменения в данных подчеркивания (при обновлении сводной таблицы)   -  person costa    schedule 09.08.2012
comment
Возможно, вы можете предоставить некоторые образцы данных или пример чего-то конкретного, чего вы пытаетесь достичь с помощью формул массива, включающих столбцы таблицы. Может быть, использование сводных таблиц или других формул может решить вашу проблему?   -  person Zairja    schedule 09.08.2012
comment
@Zairja: я уже объяснил, чего хочу добиться в меру своих возможностей. Я не думаю, что предоставление примера добавит больше к тому, что я только что написал в этой теме. Сводные таблицы подходят ближе всего. Спасибо за формулы.   -  person costa    schedule 10.08.2012
comment
@Zairja: на самом деле, я передумала :-) проверьте эту таблицу: dl.dropbox .com/u/35370420/pivot_tables.xlsx. Если вы добавите в список еще один элемент и обновите сводную таблицу, эта новая строка будет включена в сводную таблицу. Это то, что я хочу сделать: как таблица поверх таблицы/диапазона или просто представление таблицы/диапазона. Взгляните на имена: Формулы->Менеджер имен. Данные сводной таблицы указаны как этот диапазон.   -  person costa    schedule 10.08.2012
comment
да, поэтому сводные таблицы суммируют данные и позволяют отображать данные по-разному. Это то, что вы пытались сделать? Такие вещи являются частью ключевых ингредиентов, которые люди должны знать, чтобы понять ваш вопрос. Таким образом, в основном люди должны знать, что вы пытаетесь сделать с данными на листе RawData?   -  person Stepan1010    schedule 10.08.2012
comment
@ Stepan1010: Вот рабочий процесс: приложение .Net вводит данные в файл Excel в виде таблиц Excel на листе необработанных данных. Пользователь берет файл Excel и пишет свой собственный отчет в листе данных на основе данных в листе необработанных данных. Пользователь сохраняет файл. Приложение обновляет данные на листе необработанных данных и пересчитывает электронную таблицу. Приложение представляет содержимое листа данных пользователю. Если количество строк меняется в одной из таблиц, лист данных должен отражать новое содержание.   -  person costa    schedule 10.08.2012
comment
@ Stepan1010: (продолжение), если пользователь хочет сообщить данные из одной из таблиц и добавить свои столбцы, он / она должен сделать это таким образом, чтобы учитывать сокращение / рост данных. Вернемся к примеру со сводной таблицей. Если я добавлю новый напиток и обновлю сводную таблицу, этот напиток автоматически появится в сводной таблице. Если бы я прикрепил внешнюю таблицу к электронной таблице, Excel получил бы данные из этой таблицы. Если я решу обновить таблицу, Excel повторно запустит запрос и обновит содержимое.   -  person costa    schedule 10.08.2012
comment
@ Stepan1010: (продолжение) Это та же концепция, но вместо внешней таблицы вы должны использовать динамический диапазон. Извините, но я не знаю, как еще это объяснить.   -  person costa    schedule 10.08.2012
comment
@ Stepan1010: еще одно замечание, в рабочем процессе приложение запускается каждый день и обновляет данные. Пользователь больше не вносит никаких изменений в лист данных (конечно, если какие-либо изменения не требуются). Отчет должен запускаться ежедневно или разово для любого дня в году.   -  person costa    schedule 10.08.2012
comment
Думаю, я понимаю, к чему вы стремитесь... За исключением использования сводной таблицы или VBA, нет никакого способа заставить вашу таблицу автоматически увеличиваться или уменьшаться. Вы можете поместить полный столбец формул и просто скрыть тот факт, что они ничего не оценивают. В вашем вопросе говорится, что ваша формула массива помещает #NA, поэтому, возможно, вы можете изменить эти формулы массива, чтобы учесть пустые/лишние строки или ошибки отлова. Мне все еще нужен пример формулы массива, которую вы пытаетесь использовать для таблицы (пример данных), чтобы увидеть, в чем проблема, если это что-то, что мы можем изменить или решить с помощью сводной таблицы.   -  person Zairja    schedule 10.08.2012
comment
Большинство функций Excel позволяют использовать пробелы. Возьмем, к примеру, vlookup — вы можете просто сослаться на максимально возможную область, в которой будут содержаться ваши данные. Если вы пытаетесь что-то сделать с отчетами за определенные периоды времени, вы можете попробовать - Динамические именованные диапазоны - gilliganondata.com/index.php/2010/08/17/ — будет отображаться отчетный период чтобы люди не думали, что ваша отчетность на период позже, чем вы. - Трудно помочь, если мы не знаем, какие функции пользователи используют для ссылки на лист RawData.   -  person Stepan1010    schedule 10.08.2012
comment
@Zairja: Спасибо вам обоим за ваш вклад. Я не думаю, что возможно делать то, что я хочу, кроме использования сводных таблиц. Просто чтобы вы знали, что у меня есть опыт программирования, я много программировал на sql и создавал отчеты, используя разные продукты. Если вы посмотрите с этой точки зрения, я в основном пытаюсь использовать концепцию представления sql, и я пытаюсь подогнать простой шаблон отчетности в Excel - самое простое, я думаю, выбрать данные из таблицы. Я не думаю, что за все эти годы Excel продвинулся в этом направлении, хотя я думаю, что мог бы.   -  person costa    schedule 10.08.2012

Ответы (2)


arrow_upward
1
arrow_downward

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

person costa    schedule 10.08.2012
comment
Всегда есть способ сделать то, что вы хотите. Но, как сказал Гимп в самом начале, все они зависят от формулы (естественно, потому что формулы имеют разные способы ввода параметров/аргументов). Если вы можете сказать нам, какие конкретные функции вы используете, которые возвращают ошибки, мы, вероятно, могли бы помочь больше. - person Stepan1010; 11.08.2012

arrow_upward
0
arrow_downward

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

=OFFSET(Reference, Rows, Cols, Height, Width)
Reference = Start of your table
Rows = 0 - no offset
Cols = 0 - no offset
Height = COUNTA($A:$A) - counts the number of entries in column A
Width = COUNTA($6:$6) - counts the number of entries in row 6

Если бы я назвал этот именованный диапазон «тестовый диапазон», я мог бы просто перейти к ячейке и сказать =rows(testrange), и он вернул бы значение динамического диапазона.

* Вы можете обернуть COUNTA в MAX, например, «MAX (1, ​​COUNTA ($ A: $ A))», чтобы предотвратить ошибки ссылок, когда в таблице нет данных.

person grandocu    schedule 28.01.2013