Letysite.ru

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

Ссылка на таблицу в excel

Как сортировать таблицу в Excel, если в ней есть ссылки на ячейки?

У меня есть таблица данных в excel на листе 1, которая ссылается на различные ячейки во многих других листах. Когда я пытаюсь сортировать или фильтровать лист, ссылки меняются при перемещении ячейки. Однако я не хочу вручную входить в каждую ячейку и вставлять знаки $ везде, потому что некоторые ссылки непрерывны, и я могу захотеть автоматически заполнить позже.

например, один из моих столбцов данных использует лист 2, столбец B 23:28, лист 2, столбец C 1:15 и некоторые другие. Этот раздел столбца, который использует столбец B листа 2, находится в одном месте перед сортировкой, как и каждый из других разделов. Если я вставлю знаки$, я больше не смогу вставлять пустые строки и использовать автозаполнение для новых данных.

В общем, я хотел бы, чтобы мои ссылки на ячейки были статическими без использования знаков$, но только для сортировки/фильтрации. Мой текущий обходной путь включает копирование значений таблицы, вставку в пустой лист и сортировку. Я надеюсь, что лучший способ сделать это.

21 ответов

Я уверен, что это можно решить с помощью

для меня это работало как ниже —

У меня были ссылки на имя листа в Формуле для того же листа. Когда я удалил текущее имя листа из формулы и отсортировал его, он работал правильно.

даже с абсолютными ссылками сортировка не обрабатывает ссылки правильно. Относительные ссылки делаются, чтобы указать на то же относительное смещение от нового местоположения строки (что, очевидно, неправильно, потому что другие строки не находятся в том же относительном положении), а абсолютные ссылки не изменяются (потому что сортировка опускает шаг перевода абсолютных ссылок после каждой перестановки строки). Единственный способ сделать это-вручную переместить строки (преобразовав ссылки в Абсолют) один за другим. Затем Excel выполняет необходимый перевод ссылок. Сортировка Excel недостаточна, поскольку она этого не делает.

У вас есть пара вариантов:

(1) нет никакого способа обойти ссылки на ячейки, перепутанные при сортировке, когда не используются статические ссылки. Самый простой способ справиться с этим-просто скопировать и вставить как значения перед сортировкой, которая может быть автоматизирована с помощью простого макроса VBA.

(2) вы также можете попробовать использовать именованные диапазоны, если вы используете несколько общих диапазонов в своих формулах. Вы можете определить ‘Лист2!В23:28’ а ‘Range1 и ссылка ‘Range1’ в ваших формулах. В этом случае сортировка, очевидно, не повлияет на указанный диапазон, поскольку он определен в другом месте.

У меня были проблемы с этим сам и нашел лучшее и простое решение.

  1. скопируйте данные, которые вы хотите сделать в таблицу, а затем сортировать.
  2. вставить где-то еще в вашей электронной таблице
  3. значения будут меняться из-за ваших ссылок на ячейки, но мы не хотим этого, поэтому нажмите всплывающее окно, чтобы сделать «вставить специальный» и выберите Вставить только «значения».
  4. превратите свои новые данные в таблицу и отсортируйте свою жизнь прочь.

надеюсь, вы найдете, что это работает. С уважением.

мы также Боремся с той же проблемой.

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

здесь, пожалуйста, найдите образец макроса

У меня была такая же проблема; у меня был мастер-лист, который был резюме информации о других листах в моей книге.

Если вы просто хотите фильтровать / сортировать на листе, где хранятся ваши данные, а затем вернуть его в исходное состояние (независимо от того, что вы фильтруете/сортируете), просто сделайте свой первый столбец номером строки.

после первоначального фильтра / сортировки вы можете просто прибегнуть к «номеру строки», чтобы вернуть все в нормальное состояние. Примечание: это работает только если вы всегда добавить новых строк в конец списка в определенной последовательности.

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

очень простой способ-сохранить проблемные столбцы из отфильтрованных данных, но добавить столбец в отфильтрованный раздел со ссылками на ячейки (просто «=»ячейка»»).

таким образом, формулы проблемы останутся нетронутыми, но отфильтрованные данные будут ссылаться на них правильно, чтобы вы могли использовать эти ячейки для сортировки соответственно

попробуйте этот метод:

  1. вставьте столбец слева от ячеек для сортировки.
  2. предположим, что cols B: G должны быть отсортированы на основе B
  3. копировать col B
  4. вставить значения в col a
  5. выделить cols B: G
  6. заменить = знаки на # signs
  7. сортировка cols A:G на основе a
  8. выделить cols B: G
  9. заменить # signs на = signs
  10. удалить col А.
  11. вуаля!

Мне нужно было сортировать ячейки со ссылками и действительно нужно было избегать вставки значений для работы.. «Поворотный стол» сделал свое дело.

  1. подготовьте таблицы со ссылками.
  2. выберите таблицу (со ссылками) и вставьте сводную таблицу
  3. в сводной таблице выберите необходимые фильтры, чтобы сводная таблица выглядела как исходная таблица (при необходимости).
  4. сортировка / фильтрация данных по мере необходимости.

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

надеюсь, это поможет. Андрей!—1—>

Не уверен, что это удовлетворит всех, но я нашел простой обходной путь, который исправил проблему для меня. Переместите все ссылочные ячейки / формулы на другой лист, чтобы не ссылаться ни на какие ячейки на листе с сортируемой таблицей. Если вы сделаете это, вы сможете разобраться!

Это один хороший ответ чтобы понять, как сортировка работает от другого пользователя и я добавлю свои заметки, чтобы знать, как это не полностью правильно, а что правильно:

влияние на формулу после сортировки такое же, как копирование. Сортировка не перемещает содержимое строк, она копирует их. Формула может (или не может в зависимости от abs/относительных ссылок) использовать новые данные, как и скопированная формула.

Я имею в виду, что если формулу можно скопировать от 1 строки к другой, и эффекты не меняются, сортировка не повлияет на результаты формулы. Если Формулы настолько сложны и зависят от положения, что их копирование изменяет относительное содержимое, не сортируйте их.

и мое примечание, которое я испытал на практике:

вышеуказанный пользователь говорит правильно, но на самом деле он имеет некоторое исключение: части формулы столбцов, содержащие имя листа (например, sheet1!А1) рассматриваются как абсолютные ссылки (несмотря на копирование это изменяет ссылки, если они относительны ) таким образом, часть формулы будет скопирована без изменения ссылок относительно изменения места формулы Это включает в себя текущие ячейки листа, адресованные полностью как: sheet1!A2 и будет рассматриваться как абсолютные ссылки(только для сортировки) Я протестировал это из excel 2010, и я не думаю, что эта проблема будет решена в других версиях. Решение состоит в том, чтобы скопировать и пропустить специальное значение в другом месте, а затем использовать сортировку.

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

самый простой способ-использовать функцию OFFSET. Таким образом, для исходного примера формула будет: = смещение (c2, 0, -1)*1,33 =»используя текущую ячейку (c2) в качестве опорной точки, получите содержимое ячейки в той же строке, но один столбец слева (b2) и умножьте его на 1,33″

Я использовал «сохранить как», чтобы скопировать лист в новый файл в текстовом формате msdos. Это удаляет формулы, заменяя содержимое ячейки только вычисленными значениями. Затем откройте новый файл с разделителями табуляции и отсортируйте после определения столбцов. Мне нужно было отсортировать вычисленные значения по связанной текстовой строке (destination) для журнала пробега, чтобы я мог суммировать пробег для каждого назначения.

дата начала окончания расстояния назначения

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

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

другое решение -: (1) скопируйте всю таблицу —вставить как ссылку в той же таблице, отныне работайте только над «связанной таблицей» (2) скопируйте столбец со значениями для сортировки и вставьте значения как раз рядом со столом для сортировки (3) Выберите таблицу и замените all =, например #, это изменит ссылку в статическом тексте (4) сортировка таблицы по вставленным значениям (5) Замените все # на=, ссылки возвращаются Готово! Он идет довольно быстро при использовании ярлыков excel

Читать еще:  Как удалить свое видео с ютуба

Я надеялся найти здесь почему, но я думаю, что ответ проще, чем мы его делаем.

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

EX: я нахожусь в книге сегодня, и я ссылаюсь на данные для торгового представителя на другой странице.

Если мои критерии (имя представителя) сегодня!C1 (или $C1, $c$1), и я пытаюсь сортировка, лист не распознает действие. Но если вы удалите имя, ссылающееся на страницу, на которой вы находитесь (избыточная ссылка действительно), эта проблема должна прекратиться.

Итак, SUMIFS («продажи»!С1, «Продажи»!А1, Сегодня!C1) теперь будет SUMIFS («продажи»!С1, «Продажи»!A1, C1)

Если кто-то может просветить, почему это работает именно так, это было бы замечательно.

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

исправлено для меня!

простое решение-скопируйте из excel и вставьте в Google листы. Он копирует все ваши ссылочные ячейки как абсолютные ячейки, чтобы вы могли начать с нуля. Затем отсортируйте и загрузите обратно как сетку excel и переформатируйте ее для своих нужд.

Ссылка на таблицу в excel

Сегодня поговорим про ТАБЛИЦЫ. Не про таблицы, а именно про ТАБЛИЦЫ . Именно так Microsoft предложил называть те замечательные таблицы, о которых пойдёт речь ниже. В зачаточном состоянии они появились в Excel 2003 и назывались там » списками » («lists»). В Excel 2007 их довели до ума и переименовали в ТАБЛИЦЫ (TABLES), а то что раньше все нормальные люди называли таблицами, теперь предложено называть ДИАПАЗОНОМ (range). В России этот подход не прижился, да и чего ради людям менять задним числом устоявшиеся термины, поэтому TABLES мы будем называть » умными таблицами «, а таблицы в их общеупотребительном понимании оставим в покое.

Умные таблицы

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

Зачем они нужны?

Те люди, кто много работает в Excel со временем понимают, что, создавая свои таблицы, надо придерживаться определенных жёстких правил.

  1. Всем столбцам давать уникальные названия колонок.
  2. Не допускать пустых столбцов и строк в таблице.
  3. Не допускать разнородных данных в пределах одной колонки. Если уж решили, что, например, в колонке E должен хранится объем продаж в штуках, то не надо туда же вносить объём продаж, скажем, в деньгах у части строк таблицы.
  4. Не объединять ячейки без самой крайней необходимости.
  5. Форматировать таблицу, чтобы она выглядела одинаково во всех своих частях. То есть элементарно рисовать сетку, выделять цветом заголовки столбцов.
  6. Закреплять области, чтобы заголовок был всегда виден на экране.
  7. Ставить фильтр по умолчанию.
  8. Вставлять строку подитогов.
  9. Грамотно использовать абсолютные и относительные ссылки в формулах, чтобы их можно было протягивать без необходимости внесения изменений.
  10. При рабте с таблицей не выделять цветом строки/столбцы за пределами таблицы. Это поветрие, кстати очень сильно распространено, — взять выделить всю строку или весь столбец одним кликом мыши и закрасить. И наплевать, что в таблице 100 строк, а закрасилось помимо них ещё 1 000 000 строк. А потом невинно интересоваться: «Почему мои файлы так много весят?»

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

Так вот к 13-й версии (Excel 2007) его разработчики пригляделись к типовым действиям квалифицированных пользователей Excel и падарили нам функционал умных таблиц, за что им огромное спасибо. Потому что большую часть того, что я только что перечислил умные таблицы либо делают сами автоматически, либо очень сильно облегчают настройку оного.

Итак, давайте познакомимся, как создаются умные таблицы и какими полезными свойствами обладают.

1.Создание умной таблицы

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

  1. Способ 1 — на ленте ГЛАВНАЯ выбираем Форматировать как таблицу , выбираем понравишейся дизайн (при этом вам доступны 60 стандартных способа форматирования)
  2. Способ 2 — Нажимаем Ctrl-T
  3. Способ 3 — На ленте ВСТАВКА выбрать Таблица

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

2.Форматирование

Если до того, как поумнеть, ваша таблица имела форматирование (рамки, цвета букв и фона и т.п.), то возможно вам стоит это форматирование сбросить, чтобы оно не «конфликтовало» с форматированием умной таблицы. Для этого:

  1. Выделите таблицу целиком — проще всего 2 раза нажать Ctrl-A (латинская «A»!)
  2. На ленте ГЛАВНАЯ щёлкните Стили ячеек , далее стиль Обычный

При этом все проблемы с форматированием сразу решаются. Однако вам придётся восстанавливать форматы столбцов ячеек: формат даты, времени, нюансы числового формата (типа количества знаков после точки), но это не очень сложно. В любом случае вам решать — сбрасывать форматирование этим способом, либо каким-то другим, менее «разрушительным», но знать о нём надо.

3.Предпросмотр стиля таблицы

Через меню Форматировать как таблицу вы можете увидеть, как будет выглядеть ваша таблица при приминении любого имеющегося стандартного стиля. Очень удобно и наглядно!

4.Прочие плюшки и полезности.

  1. Чередующийся цвет строк или столбцов! Да знаете ли вы, что раньше для этого надо было 10 минут колдовать с условным форматированием с бубном и крысиными костями!. «А теперь? Оглянитесь вокруг, — какие вам корпуса понастроили, какие газоны разбили, водопровод, телевизор, газовая кухня, парники, цветники. «
  2. Включение строки итогов одним нажатием!
  3. Фильтр по умолчанию
  4. Первый и последний столбец могут быть выделены жирным шрифтом
  5. При прокрутке таблицы столбцы видны БЕЗ закрепления областей! Чего ж вам боле?!

5.Упрощенное выделение таблицы, столбцов, строк

6.Умная таблица имеет имя и его можно изменять

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

7.Вставка срезов.

В Excel 2010 появилась такая полезная функция как срезы . Это наглядные фильтры, которые можно добавлять к сводным таблицам, а также и к умным таблицам тоже. Посмотрим как это работает:

8.Структурированные формулы.

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

Умные Таблицы Excel – секреты эффективной работы

В MS Excel есть много потрясающих инструментов, о которых большинство пользователей не подозревают или сильно недооценивает. К таковым относятся Таблицы Excel. Вы скажете, что весь Excel – это электронная таблица? Нет. Рабочая область листа – это только множество ячеек. Некоторые из них заполнены, некоторые пустые, но по своей сути и функциональности все они одинаковы.

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

Как создать Таблицу в Excel

В наличии имеется обычный диапазон данных о продажах.

Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

Есть горячая клавиша Ctrl+T.

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

Как правило, ничего не меняем. После нажатия Ок исходный диапазон превратится в Таблицу Excel.

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

Читать еще:  Списки в excel 2020

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

то она автоматически переделается в

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

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

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

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

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

3. В таблицу по умолчанию добавляется автофильтр, который можно отключить в настройках. Об этом чуть ниже.

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.


Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

5. Новые столбцы также автоматически включатся в Таблицу.

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

Помимо указанных свойств есть возможность сделать дополнительные настройки.

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

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

Для фильтрации Таблицы следует выбрать интересующую категорию.

Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

Ограничения Таблиц Excel

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

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

Excel: как делать ссылки на сводную таблицу

Я поясню ее на примере отчета, фрагмент которого показан на рис. 1. Это сводная таблица, которая сформирована по некоторой базе данных. В таблице показаны объемы продаж по шести контрагентам. Предположим, что эти данные мы решили вставить в другую таблицу в виде ссылок на ячейки сводного отчета, и уже там сделать окончательный расчет. Посмотрим, что из этого получится. Чтобы не усложнять задачу, я создам ссылки на том же рабочем листе, где расположена сводная таблица. Дальше делаем так:

1. Становимся на свободную ячейку, пусть это будет « D3 ».

2. Набираем символ «=» (начало формулы).

3. Щелкаем левой кнопкой мыши на ячейке « B3 » (я хочу сделать ссылку на сумму реализации по контрагенту « ТОВ «Топаз» »). В ячейке « D3 » вместо ссылки мы увидим такой результат: « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма»; $A$1;»Покупатель»;»ТОВ «»Топаз»»») ». При этом значение в ячейке « D3 » будет равно « 119,80 », что соответствует объемам продаж по « ТОВ «Топаз» ».

4. Копируем эту формулу вниз до ячейки « D8 » (на всю высоту сводной таблицы). Результат во всех ячейках будет одинаковым — « 119,80 ». То есть функция получения данных из сводного отчета сослалась на одну и ту же ячейку сводной таблицы.

Причина такого поведения лежит в параметрах функции « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() ». Таких параметров у нее четыре. Первым идет название поля, по которому нужно выбрать итог. В нашем случае это поле « Сумма ». Так это поле называлось в исходной базе, с этим именем оно и попало в сводный отчет. Вторым параметром стоит ссылка на ячейку с заголовком поля. В формуле эта ссылка выглядит как « $A$1 ». Кстати, абсолютная адресация в данном случае обязательна! Третий параметр — название поля, по которому Excel будет выбирать данные из сводного отчета. В формуле указано, что поиск конкретного числа в сводной таблице нужно делать по полю « Покупатель ». Последний параметр — это строка для поиска конкретного значения среди покупателей. В нашей функции указано значение « ТОВ «Топаз» ». Поэтому Excel выберет итог именно по этому контрагенту. Сразу бросается в глаза, что большинство параметров в функции « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() » указаны в виде текстовых строк. Именно поэтому не сработала корректировка адресов при копировании формулы в ячейки « D3:D8 », и все функции вернули один и тот же результат.

Кстати, исправить такую ситуацию несложно: нужно вместо фиксированного элемента « «ТОВ «»Топаз»»» » поставить ссылку на ячейку « A3 ». То есть формула в ячейке « D3 » должна выглядеть так: « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Сумма»;$A$1;»Покупатель» ; A3 ) » (изменения выделены полужирным начертанием). В этом варианте после копирования формулы вниз до ячейки « D8 » мы получим правильные объемы реализации по каждому контрагенту.

Однако речь сейчас о другом. Использование функции « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() » имеет свои преимущества и недостатки. Среди преимуществ я бы указал, что независимо от порядка сортировки записей в сводной таблице ссылка через функцию обеспечит правильный результат. И это понятно — извлечение данных из сводного отчета функция делает по ключевому полю, а не по адресу рабочего листа! Если посмотреть на формулу в ячейке « D3 », то ключевым полем для обращения к сводной таблице является название фирмы « ТОВ «Топаз» ». И при этом не имеет никакого значения, где конкретно находится запись по этой фирме — на первой позиции отчета или в самом конце. Данные Excel подставит правильно.

Недостаток работы с функцией « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() » состоит в том, что нужно корректировать значение ключевого поля или заменять его ссылкой. Поэтому в некоторых случаях удобнее вместо встроенной функции использовать ссылки на ячейки сводной таблицы. Чтобы вставить такие ссылки автоматически (отказаться от использования функции « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() »), нужно знать одну тонкость.

Читать еще:  В каком формате сохранять таблицы excel

Секрет Встроенную функцию « =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() » Excel использует только при ссылках на поля в области данных сводного отчета. При организации ссылок на заголовки строк или колонок он вставляет обычные ссылки на ячейки рабочего листа.

Зная это правило, мы легко получим «нормальные» ссылки на ячейки сводной таблицы. Для этого делаем так:

1. Открываем документ, как на рис. 1.

2. Становимся на ячейку « D3 ».

3. Вводим символ « = » (начинаем запись формулы).

4. Щелкаем левой кнопкой мыши на ячейке « A3 ». Excel добавит в текущую ячейку ссылку « =A3 », где записано название фирмы. В данном конкретном случае — это « ТОВ «Топаз» ».

5. Нажимаем « Enter » (завершаем ввод формулы).

6. Копируем формулу в ячейку « E3 ».

Смотрим на содержимое ячеек « D3 » и « E3 ». Как и следовало ожидать, там находятся обычные ссылки: « = A3 » и « =B3 ». Одна указывает на ячейку с названием фирмы, вторая — на объем реализации. Теперь с этими ссылками можно делать все что угодно — переносить на другой лист, использовать в расчетах и т. д.

И последнее. Работа с обычными ссылками незаменима, когда нужно построить график по данным сводного отчета (!) в программе Excel 2003. При создании такого графика Excel 2003 формирует его на отдельном листе, а это не всегда удобно. Чтобы отказаться от такой возможности и построить диаграмму на текущем листе, нужно создать рабочую область со ссылками на данные сводной таблицы. А затем по этим ссылкам сформировать диаграмму. Для таблицы на рис. 1 процедура выглядит так:

1. Открываем документ, переходим на ячейку « D3 ».

2. Вводим в нее формулу « =A3 ».

3. Копируем формулу в ячейки « D3:E8 ». В результате мы получим копию данных из сводной таблицы в виде формул.

4. Строим график по данным « D3:E8 ».

5. Чтобы скрыть «рабочую область», форматируем значения в блоке « D3:D8 » белым цветом или ставим график поверх ячеек « D3:D8 », чтобы закрыть им вспомогательную информацию (рис. 2).

На сегодня все. Удачной работы! Жду ваши вопросы, замечания и предложения на bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .

Наш сайт корисний для вас?

Підписатися на найактуальнішу розсилку для бухгалтера бюджетної установи

«Умная таблица» в Excel

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

Общие правила создания и заполнения таблиц

Чтобы ваша таблица, «обычная» или «умная», правильно работала, следует придерживаться при ее создании и заполнении следующих несложных правил:

  • не использовать в таблице объединенные ячейки;
  • присваивать графам (колонкам) уникальные имена;
  • в одной графе использовать один формат данных;
  • не оставлять при заполнении таблицы пустых строк;
  • в «обычной» таблице следует выделить строку заголовков (шапку таблицы) форматированием (цвет, шрифт) и закрепить на начальном экране.

Создание «Умной таблицы»

Для создания «Умной таблицы» необходимо выбрать любую ячейку внутри таблицы без форматирования или выделить произвольный диапазон, в котором планируется создать такую таблицу, и нажать кнопку «Форматировать как таблицу» на вкладке «Главная». Откроется окно выбора формата будущей «Умной таблицы»:

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

В примере заголовки уже присутствуют внутри диапазона с таблицей, поэтому галочку «Таблица с заголовками» оставляем. Нажав «OK», получим следующую «Умную таблицу»:

Теперь при записи формулы создаются адреса с именами колонок, а при нажатии «Enter» формула автоматически копируется во все ячейки этой графы:

Адреса с именами колонок создаются, если аргументы для формулы берутся из той же строки. Аргументы, взятые из других строк, будут отображены обычными ссылками.

Когда «Умная таблица» уже создана, подобрать для нее подходящее цветовое оформление становится легче. Для этого нужно выбрать любую ячейку внутри таблицы и снова нажать кнопку «Форматировать как таблицу» на вкладке «Главная». При наведении курсора на каждый образец форматирования, «Умная таблица» будет менять цветовое оформление в режиме предпросмотра. Остается только выбрать и кликнуть на подходящем варианте.

При выборе любой ячейки внутри «Умной таблицы» на панели инструментов появляется вкладка «Работа с таблицами Конструктор». Перейти в нее можно, нажав на слово «Конструктор».

На вкладке «Конструктор» отображены все инструменты для работы с «Умной таблицей» (неполный перечень):

  • редактирование имени таблицы;
  • изменение цветового чередования строк на цветовое чередование столбцов;
  • добавление строки итогов;
  • удаление кнопок автофильтра;
  • изменение стиля таблицы (то же, что и по кнопке «Форматировать как таблицу» на вкладке «Главная»);
  • удаление дубликатов;
  • добавление срезов*, начиная с Excel 2010;
  • создание сводной таблицы;
  • удаление функционала «Умной таблицы» командой «Преобразовать в диапазон».

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

Работа с «Умной таблицей»

Начните заполнять новую строку или новый столбец, и после заполнения первой ячейки «Умная таблица» автоматически расширится на эту строку или на этот столбец. Если в таблице использовались формулы, то при добавлении новой строки они будут автоматически в нее скопированы.

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

Чтобы автоформа всегда была доступной, ее кнопку необходимо добавить на панель быстрого доступа (вверху слева). Для непосредственного перехода к настройке панели быстрого доступа нажмите на правую кнопку этой панели в виде черточки с треугольником внизу. Откроется следующее меню:

В этом меню необходимо выбрать пункт «Другие команды…» для перехода к окну настройки панели быстрого доступа:

Здесь необходимо выбрать в верхнем раскрывающемся списке «Команды не на ленте», в окне со списком команд выделить пункт «Форма…», нажать кнопку «Добавить >>», а затем кнопку «OK». На панели быстрого доступа появится кнопка вызова автоформы.

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

Обратите внимание, что ячейка с формулой не редактируется. Добавление новой записи или сохранение внесенных изменений в существующую строку происходит при нажатии кнопок «Добавить», «Закрыть» или «Enter» на клавиатуре. При нажатии кнопок «Назад» или «Далее» внесенные изменения не сохраняются.

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

Самым простым способом было бы вставить формулу =[предыдущая ячейка]+1 , но она не копируется автоматически в новую запись. А в ячейках с этой формулой программа Excel выводит замечание: «Несовместимая формула в вычисляемом столбце».

Для нашего примера подойдет следующая формула: =СТРОКА([@Наименование])-1 , которая вычисляет номер текущей строки рабочего листа Excel и уменьшает ее на единицу, так как первая запись в нашей «Умной таблице» начинается со второй строки рабочего листа Excel.

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

Преимущества «Умной таблицы»

К преимуществам «Умной таблицы» можно отнести следующие:

  • автоматическое форматирование по выбранному шаблону;
  • автоматическое расширение «Умной таблицы» при добавлении новых записей или колонок;
  • автоматическое копирование формул в новую запись;
  • возможность добавления строки итогов (критерий расчета итога для каждой отдельной графы можно выбрать из раскрывающегося списка);
  • возможность использования автоформы* для добавления новых записей и редактирования существующих (такая же возможность существует и для обычных таблиц).

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

Ссылка на основную публикацию
Adblock
detector