Структурированная ссылка в excel - IT Новости
Microclimate.su

IT Новости
87 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Структурированная ссылка в excel

Структурирование данных в Excel

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

  1. Для начала отсортируем данные по столбцу Company.
  2. На вкладке Данные (Data) нажмите кнопку Промежуточный итог (Subtotal).
  3. В списке При каждом изменении в (At each change in) выберите Company. Этот столбец мы будем использовать, чтобы структурировать нашу таблицу.
  4. В списке Операция (Use function) выберите Количество (Count).
  5. Поставьте галочку напротив Company (см. рисунок ниже).
  6. Нажмите ОК.Результат:

  • Чтобы свернуть группу ячеек, нажмите знак “” (минус). Можно использовать цифры, чтобы свернуть или развернуть группы по уровню. К примеру, нажмите 2, чтобы отображались только подуровни.
  • Примечание: Нажмите 1, чтобы отображать только итоги, или 3, чтобы отобразить всё.

    Чтобы свернуть группу столбцов, выполните следующие действия:

    1. Выделите столбцы A и B.
    2. На вкладке Данные (Data), кликните по Группировать (Group).
    3. Нажмите на знак ““.Результат:
    4. Чтобы удалить структуру, выделите любую ячейку из набора данных и на вкладке Данные (Data) нажмите кнопку Промежуточный итог (Subtotal). Затем кликните по Убрать все (Remove All).

    Обобщаем с умом: внешние ссылки и консолидация в Excel

    Здравствуйте, уважаемые читатели. Как часто перед Вами стоит задача консолидации, или свода данных из разных листов и книг в одну таблицу? Думаю, что часто. Консолидация в Excel — это трудоемкая, кропотливая и неприятная работа, но в этой статье я расскажу, как ее облегчить. Для сбора информации из разных источников есть два подхода: внешние ссылки на ячейки, или инструмент «Консолидация».

    Внешние ссылки в Excel

    Думаю, каждый из Вас знает, что внешняя ссылка в Эксель – это ссылка на ячейку, находящуюся в другой книге. И самый распространенный способ собрать данные из разных источников в одну – как раз внешние ссылки.

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

    Существует много способов сослаться на ячейку с другого листа, или книги. Я расскажу Вам самый простой:

    1. Откройте все файлы – свод и исходники
    2. Кликните по ячейке, в которую нужно вставить ссылку, и введите знак равно (как вы это делаете, когда создаете формулы).
    3. Выберите файл с исходным значением и кликните на этом значении. В строке формул появится ссылка на это значение.
    4. Если к этому значению нужно прибавить другие из остальных источников – допишите «+» и повторяйте п.3-4 до полного заполнения.
    5. Когда все ссылки на ячейки сделаны, нажмите Enter , будет просчитан сводный результат.

    Внешняя ссылка имеет специфический внешний вид: имя файла в квадратных скобках, имя листа, восклицательный знак, координаты ячейки:

    Когда Вы закроете исходные файлы, к ссылке слева добавится еще и адрес файла-исходника:

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

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

    Если на одном компьютере открыт и сводный документ и исходник, изменения в исходной таблице сразу же «подтянутся» и в свод. Если хотя бы одна из этих книг закрыта – понадобится принудительное обновление. Так же, при повторном открытии сводного файла, Эксель предложит обновить данные. Сделайте это, если нужно актуализировать информацию.

    Управление внешними ссылками

    Чтобы управлять ссылками на источники в других книгах, нажмите на ленте Данные – Подключения – Изменить связи . В открывшемся окне отобразится весь список подключенных файлов и кнопки-команды:

    • Обновить – принудительное обновление всех ссылок
    • Изменить – указать новую книгу Excel, в которой располагаются подключенные данные
    • Открыть – открывает документ-источник
    • Разорвать связь – удаляет внешние ссылки из этой связи, заменяет их на значение
    • Состояние – обновляет статус доступности файлов с данными
    • Запрос на обновление связей – укажите программе, нужно ли обновлять данные при открытии файла

    Если у Вас большой файл с несколькими листами, может понадобиться много времени на поиски внешних ссылок. Так как быстро найти внешние ссылки в документе Excel? Предложу рецепт. Все подобные ссылки имеют общий элемент – открывающая и закрывающая квадратные скобки, в которые записано имя файла-исходника. В других случаях такие скобки используются редко. Нажмите Ctrl+F для открытия окна поиска, в поле «Найти» введите квадратную скобку и нажмите «Найти все». В результатах поиска точно будут все внешние ссылки.

    Применение внешних ссылок – самый простой и очевидный способ собрать данные из нескольких таблиц в одну. Но если данных достаточно много, этот метод неприменим. Тогда пользуемся инструментом «Консолидация».

    Консолидация данных в одну таблицу

    Этот инструмент упрощает сведение информации в одну таблицу данных, это удачный способ автоматизации процесса.

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

    Давайте выполнять. Открываем все файлы-исходники и свод. Активируем «Свод». Ищем на ленте Данные – Работа с данными – Консолидация . Откроется окно подключения внешних данных.

    Теперь пошагово выполняем:

    1. В поле «Функция» выбираем «Сумма» (или что-то другое, если нужно)
    2. В поле «Ссылка» указываем ссылку на данные в первом файле-источнике (вместе с шапкой). Лучше всего, указать столбцы полностью, например A:D . Тогда не нужно будет переопределять диапазон при добавлении новых данных.
    3. Нажимаем «Добавить», ссылка появляется в поле «Список диапазонов»
    4. Повторяйте п. 2-3, пока не добавите ссылки на все файлы-исходники

    1. Установите галочки «Подписи верхней строки» и «Значения левого столбца», чтобы Эксель сам подобрал заголовки шапки и категории в строках
    2. Установите галку «Создавать связи с исходными данными», тогда консолидация будет сделана с помощью ссылок на ячейки. Иначе – будут просто вставлены итоговые значения.
    3. Нажмите Ок для завершения настройки консолидации.

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

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

    Такой вот простой инструмент, решающий одну из самых противных задач в Эксель. Я предпочитаю применять именно его, ручная работа по внесению ссылок – не для меня. Рекомендую и Вам пользоваться. Хотя, более удачным решением будет применение сводной таблицы (когда это возможно), или создание собственной VBA-программы с детальными настройками сведения.

    На этом оставлю Вас экспериментировать с Консолидацией, и приступаю к написанию поста о защите данных в Эксель. Читайте, эта информация сейчас, как никогда актуальна. До встречи!

    Ссылки в Excel

    Когда пользуешься различными формулами Excel, то приходится обращать внимание на ссылки. Ссылки в Excel, как известно, бывают разными: относительными, абсолютными, внешними, в виде имен диапазонов и др.

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

    Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.

    Как несложно заметить, вместо чисел мы делаем ссылки на складываемые ячейки или сразу на целый диапазон. Ссылки вместо чисел – это основное отличие Excel от калькулятора. Но чтобы они работали правильно, неплохо бы различать абсолютные и относительные ссылки.

    Относительные ссылки Excel

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

    Как видим, при копировании формулы диапазон суммирования автоматически перемещается вслед за формулой, позволяя за минимум действий рассчитать итоговую строку. Подобный расчет возможен благодаря относительности ссылок, которые как бы привязаны к ячейке с формулой, а не к адресу, на который указывают. Если формула копируется вправо, то и вычисляемый диапазон смещается туда же. Если вниз или вверх, то же самое произойдет с диапазоном вычисления. По умолчанию ссылки в рамках одной книги Excel делаются относительными и имеют следующий вид:

    где буква обозначает столбец, а число – строку.

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

    Абсолютные ссылки

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

    Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.

    Числитель «поехал» вниз правильно, а вот знаменатель должен был остаться на месте. Чтобы все получилось правильно, нужно зафиксировать ссылку на итог, чтобы она не «ездила» вслед за формулой. Короче говоря, ссылку на итоговую сумму нужно сделать абсолютной, для чего перед названием строки и столбца ставится значок доллара $ ( в английской раскладке):

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

    Смешанные ссылки

    Помимо абсолютных и относительных ссылок встречаются еще и т.н. смешанные ссылки, когда фиксируется только строка или столбец:

    Закрепленная строка никак не влияет на абсолютность столбца, а абсолютный столбец не зависит от относительности строки. Для выбора правильного варианта нужно знать, куда копируется ячейка с формулой и не будет никаких проблем. Строго говоря, для расчета доли в примере выше ссылку на итог можно было сделать смешанной, т.к. фиксация была необходима только по строке. Но если формула копируется и по вертикали, и по горизонтали, тогда уж точно не обойтись без полной абсолютной ссылки.

    Как быстро установить символ доллара $

    Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно. Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4 . Ссылка начнет менять свой режим «абсолютности». После нажатия F4 относительная ссылка станет абсолютной (и по строкам, и по столбцам). Если нажать F4 еще раз, то абсолютной станет только строка, следующее нажатие сделает абсолютным только столбец. Очередное нажатие F4 сделает ссылку снова относительной. И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т.д. Последовательным нажатием F4 останавливаетесь на нужном варианте.

    » src=»https://statanaliz.info/wp-content/plugins/a3-lazy-load/assets/images/lazy_placeholder.gif» data-lazy-type=»image» data-src=»https://statanaliz.info/wp-content/uploads/2015/01/ssylki-06_F4.gif» alt=»Последовательное нажатия » w />

    Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.
    Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно). Но про другие листы и книги поговорим в следующем параграфе.

    Ссылки на другие листы и книги

    Excel умеет ссылаться не только в пределах одного листа, но и на другие листы и даже другие файлы Excel. Такой функционал значительно расширяет диапазон вычислений. Если в формулах есть ссылки на другие файлы, то следует обратить внимание на следующее. Во-первых, при открытии файла с внешними связями Excel назойливо напоминает об этом выскакивающим посреди экрана окошком. Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было. Во-вторых, при изменении или удалении внешнего источника мгновенно меняются и результаты вычислений, что нужно далеко не всегда. В третьих, если возникнут ошибки в расчетах, то на обнаружение их источника может потребоваться дополнительное время, т.к. данные находятся в другом файле. В общем, сразу после вычислений с использованием данных из других книг внешние ссылки рекомендуется удалить: Данные → Подключения → Изменить связи → Разорвать связь.

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

    Стиль ссылок

    И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)). Такой стиль ссылок иногда может быть полезен, но довольно редко. Поэтому неплохо уметь возвращать нормальный вид адреса ячейки. Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.

    Могу ли я сделать структурированную ссылку абсолютной в excel 07?

    У меня есть таблица «A» с 2 столбцами «Foo» и «Bar». У меня есть формула со структурированной ссылкой A [Foo]. Когда я заполняю эту формулу по горизонтали, я хочу, чтобы ссылка оставалась A [Foo], но теперь во втором столбце ссылка превращается в [Bar]. Есть ли способ сделать эту структурированную ссылку абсолютной?

    Было бы шокирующим, что это не поддерживается, если нет.

    Перетащите его по горизонтали и Foo, если в таблице указано несколько столбцов.

    excel excel-2007 excel-formula

    7 ответов

    5 Решение Dirk Vollmar [2008-12-12 23:59:00]

    существует разница между копированием и перетаскиванием. Перетаскивание также называется «заполняющими» формулами. На веб-сайте поддержки Microsoft вы найдете следующее объяснение:

    Перемещение, копирование и заполнение структурированные ссылки

    Все структурированные ссылки остаются при копировании или перемещении формулы который использует структурированную ссылку.

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

    Итак, это в основном означает, что поведение, которое вы видите, по дизайну. Если вы хотите, чтобы ссылка была абсолютной, вы должны скопировать формулу, а не перетащить/ «заполнить» ее.

    Вы можете сделать ссылку абсолютной, дублируя ссылку на столбец, как если бы это был диапазон. Похож на следующее:

    Поместите A [[Foo]: [Foo]] в формулу и перетащите ее. Ссылка останется в столбце [Foo]. Обратите внимание: вам нужно перетащить формулу. Копирование/вставка не будет работать.

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

    На этом сайте есть статья и видеоролик об этой проблеме, где я пытаюсь объяснить это четко.:)

    2 SimonH [2012-01-24 14:19:00]

    Я не мог создать идею Control + drag . Однако, выбрав исходную ячейку и те, которые нужно заполнить, и используя Control + R , заполните правильно, ярлык, похоже, скопировал, не изменяя структурированные ссылки по столбцам.

    Попробуйте вставить имя таблицы с функцией INDIRECT, например;

    В вашем случае INDIRECT(«A[Foo]»)

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

    1 tekNorah [2011-12-28 03:20:00]

    Похоже, вы не можете сделать структурированную ссылку (.ie — A [Foo]) абсолютной. Если вы хотите использовать абсолютные ссылки в своей формуле, чтобы вы могли использовать дескриптор заполнения, используйте старый метод абсолютной ссылки ($ A $2 или $A2)

    Название таблицы: DiscountPricing

    0 [2009-04-27 12:37:00]

    звучит как ваш по-прежнему перетаскивание/наполнение, цель для нижней части ячейки, а не в углу. Росс

    0 cLFlaVA [2008-12-12 23:43:00]

    Кажется, что если вы удерживаете Ctrl при копировании формул, он останется неподвижным/абсолютным.

    0 0 голоса
    Рейтинг статьи
    Читать еще:  Как вставить файл в таблицу excel
    Ссылка на основную публикацию
    Adblock
    detector