Формулы excel работа с таблицами: Работа в Экселе с формулами и таблицами для начинающих

Содержание

Как работать с таблицами в Excel

Unknown 21:47:00 инструкция , совет , столбец , строка , таблица , Эксель , ячейка , Excel для новичков , Excel для чайников

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

Я расскажу Вам все, что необходимо знать что бы начать работу с MS Excel. Давайте приступать.

Основные понятия

Строки — строки в Excel нумеруются числами начиная от 1. Ячейки в первой строке будут A1, B1, C1 и так далее.
Столбцы — столбцы в Excel обозначаются латинскими буквами начиная с A. Ячейки в первом столбце будут A1, A2, A3 и так далее.
Выбранная ячейка отображает с какой ячейкой таблицы Вы сейчас работаете.
Ссылка на ячейку показывает адрес выбранной ячейки.
Панель формул отражает введенную в выбранную ячейку информацию, будь то число, текст или формула.

Как выбрать ячейку

Существует несколько способов, что бы выбрать ячейку:

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

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

Как внести данные в ячейку

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

Как редактировать содержимое ячейки.

Для того, что бы изменить содержимое ячейки в Excel, выберите ее двойным щелчком левой кнопкой мышки. Либо выберите стандартым методом, а затем нажмите F2. Также Вы можете менять данные в ячейки при помощи в панели формул.

Как переместиться между ячейками при помощи клавиатуры

Используйте клавиши со стрелками, что бы перемещаться между ячейками в таблицах Excel. Нажмите Ввод что бы переместиться на одну ячейку вниз, либо Tab, что бы перейти на одну ячейку вправо.

Как переместить или копировать ячейку

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

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

Как вставить столбец или строку

Для того, что бы вставить столбец в Excel, кликните правой кнопкой на букве столбца и выберите Вставить. Excel всегда вставляет столбец слева от выбранного столбца.

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

Резюме

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

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

Топ-5 функций и формул Excel для бухгалтеров, которые упростят работу

Автоматизация финансового планирования

1. Адресация

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

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

Вуаля — вы получили данные по зарплате Иванова.

2. Функция «СУММ»

С ее помощью можно сложить сразу нескольких чисел. Эта функция — альтернатива сложения чисел через знак плюс.

Можно также прописать функцию вручную, написав: равно, СУММ.

3. Поиск и замена

Функция поиска помогает найти текстовые или числовые данные на листе или по всей книге. Чтобы вызвать функцию, щелкните в «Ленте меню» раздел «Главная» и затем в «Панели инструментов» найдите раздел «Редактирование». И в нём нажмите кнопку «Найти и выделить».

Вы не обязательно должны помнить слово полностью. Например, вы помните что фамилия начинается на «Ива».

Функция «Замена» — дополнение «Поиска» и заменяет слова, числа или части слов.

4. Горячие клавиши

Помимо функций в Excel есть удобные комбинации клавиш, которые упростят вам работу:

Ctrl Shift 4 — определяет денежный формат ячейки;
Ctrl Shift 7 — устанавливает внешней границы выделенного диапазона ячеек;
Shift F2 — редактирует примечания;
Ctrl ; — вводит текущую дату;
Alt = — запускает функцию «Автосумма».

5. Умная таблица

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

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

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

Другие функции, клавиши и формулы, которые упростят работу бухгалтера в Excel

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

«Клерк» нашел спеца по Excel, которые объясняет все на простом языке, без сложных терминов. С иллюстрациями, подсказками, шаблонами.

Олег Зык — бухгалтер, оценщик, предприниматель и создатель курсов по Excel.

Он научит вас:

работать с любыми видами данных, графиками, шаблонами и таблицами;
форматировать и печатать документы в Эксель;
вести подсчеты с помощью самых сложных формул;
сортировать данные и проводить их аналитику.

Структурированные ссылки в таблицах Excel

В этом руководстве объясняются основы структурированных ссылок Excel и приводятся некоторые приемы их использования в реальных формулах.

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

Структурированный справочник Excel

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

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

Например, чтобы суммировать значения в ячейках B2:B5, вы используете функцию СУММ с обычной ссылкой на диапазон:

=СУММ(B2:B5)

Чтобы сложить числа в столбце «Продажи» таблицы 1, используйте структурированную ссылку:

=СУММ(Таблица1[Продажи])

Ключевые особенности структурированных ссылок

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

Легко создается

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

Устойчивость и автоматическое обновление

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

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

Можно использовать внутри и снаружи стола

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

Автоматическое заполнение формулы (рассчитываемые столбцы)

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

Как создать структурированную ссылку в Excel

Создать структурированную ссылку в Excel очень просто и интуитивно понятно.

Если вы работаете с диапазоном, сначала преобразуйте его в таблицу Excel. Для этого выделите все данные и нажмите Ctrl+T. Для получения дополнительной информации см. Как создать таблицу в Excel.

Чтобы создать структурированную ссылку, вам нужно сделать следующее:

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

В качестве примера давайте просуммируем количество продаж за 3 месяца в каждой строке нашей таблицы с именем Sales . Для этого мы набираем =СУММ( в E2, выбираем B2:D2, вводим закрывающую скобку и нажимаем Enter:

В результате весь столбец E автоматически заполняется этой формулой:

=СУММ(Продажи[@[Январь]:[Март]])

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

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

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

Например, чтобы найти наибольшее число в нашей типовой таблице, мы начинаем вводить формулу MAX, после открывающей скобки набираем «s», выбираем в списке таблицу Sales

и нажимаем Tab или дважды щелкаем имя .

В результате имеем вот такую формулу:

=MAX(Продажи)

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

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

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

В качестве примера, давайте разберем следующую формулу, которая суммирует итоги Юг и Север столбцы в таблице с именем Регионы :

Справочник включает три компонента:

Имя таблицы
Спецификатор элемента
Спецификаторы столбцов

Чтобы увидеть, какие ячейки действительно вычисляются, выберите ячейку с формулой и щелкните в любом месте строки формул. Excel выделит указанные ячейки таблицы:

Имя таблицы

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

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

Спецификатор столбца

Спецификатор столбца ссылается на данные в соответствующем столбце без строки заголовка и строки итогов. Спецификатор столбца представлен именем столбца, заключенным в скобки, например. [Юг].

Чтобы сослаться на несколько смежных столбцов, используйте оператор диапазона, например [[Юг]:[Восток]].

Спецификатор элемента

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

Спецификатор элемента	Относится к
[#Все]	Вся таблица, включая данные таблицы, заголовки столбцов и итоговую строку.
[#Данные]	Строки данных.
[#Заголовки]	Строка заголовка (заголовки столбцов).
[#Всего]	Итоговая строка. Если итоговой строки нет, возвращается null.
[@имя_столбца]	Текущая строка, т. е. та же строка, что и формула.

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

Например, чтобы добавить числа в столбцы Юг и Запад текущей строки, вы должны использовать следующую формулу:

=СУММ(Регионы[@Юг], Регионы[@Запад])

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

=СУММ(Регионы[@[Продажи на юге]], Регионы[@[Продажи на Западе]])

Операторы структурированных ссылок

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

Оператор диапазона (двоеточие)

Как и в случае обычных ссылок на диапазоны, вы используете двоеточие (:) для ссылки на два или более смежных столбца в таблице.

Например, приведенная ниже формула суммирует числа во всех столбцах между Юг и Восток .

=СУММ(Регионы[[Юг]:[Восток]])

Союз операторов (запятая)

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

Например, вот как можно суммировать строки данных в столбцах South и West .

=СУММ(Регионы[Юг], Регионы[Запад])

Оператор пересечения (пробел)

Используется для ссылки на ячейку на пересечении определенной строки и столбца.

Например, чтобы вернуть значение на пересечении Всего строки и Запад столбца, используйте эту ссылку:

=Регионы[#Всего] Регионы[[#Все],[Запад]]

Обратите внимание, что в этом случае требуется спецификатор [#All], поскольку спецификатор столбца не включает итоговую строку. Без него формула вернула бы #NULL!.

Правила синтаксиса ссылок на таблицы

Чтобы отредактировать или создать структурированные ссылки вручную, следуйте этим инструкциям:

1. Заключить спецификаторы в скобки

Все спецификаторы столбцов и специальных элементов должны быть заключены в [квадратные скобки].

Спецификатор, содержащий другие спецификаторы, должен быть заключен во внешние скобки. Например, Регионы[[Юг]:[Восток]].

2. Разделяйте внутренние спецификаторы запятыми

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

Например, чтобы вернуть заголовок столбца Юг , вы вводите запятую между [#Заголовки] и [Юг] и заключаете всю эту конструкцию в дополнительный набор квадратных скобок:

=Регионы[[#Заголовки],[Юг]]

3. Не заключайте в кавычки заголовки столбцов

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

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

В структурированных ссылках некоторые символы, такие как левые и правые скобки, знак решетки (#) и одинарные кавычки (‘), имеют особое значение. Если какой-либо из вышеуказанных символов включен в заголовок столбца, необходимо использовать одинарную кавычку перед этим символом в спецификаторе столбца.

Например, для заголовка столбца «Элемент №» спецификатором является [Элемент ‘#].

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

Чтобы улучшить читаемость ссылок на таблицы, вы можете вставлять пробелы между спецификаторами. Обычно считается хорошей практикой использовать пробелы после запятых. Например:

=СРЕДНЕЕ(Регионы[Юг], Регионы[Запад], Регионы[Север])

Ссылки на таблицы Excel — примеры формул

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

Найти количество строк и столбцов в таблице Excel

Чтобы получить общее количество столбцов и строк, используйте функции COLUMNS и ROWS, для которых требуется только имя таблицы:

КОЛОННЫ ( таблица )

РЯДЫ ( таблица )

Например, чтобы найти количество столбцов и строк данных в таблице с именем Sales используйте следующие формулы:

=СТОЛБЦЫ(Продажи)

=СТРОКИ(Продажи)

Чтобы включить в подсчет заголовок и итоговых строк , используйте спецификатор [#ALL]:

=СТРОКИ(Продажи[#Все])

На приведенном ниже снимке экрана показаны все формулы в действии:

Подсчет пробелов и непробелов в столбце

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

Для подсчета пробелов в столбце используйте функцию СЧИТАТЬ ПРОПУСТЫ. Чтобы подсчитать непустые ячейки в столбце, используйте функцию COUNTA.

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

Пробелы:

=СЧИТАТЬПУСТО(Продажи[январь])

Непустые:

=СЧЕТЧИК(Продажи[янв])

Для подсчета непустых ячеек в видимых строках в отфильтрованной таблице используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ с номером_функции, установленным на 103:

=ПРОМЕЖУТОЧНЫЙ ИТОГ(103,Продажи[Янв])

Сумма в таблице Excel

Самый быстрый способ сложить числа в таблице Excel — включить параметр «Строка итогов». Для этого щелкните правой кнопкой мыши любую ячейку в таблице, выберите Table и щелкните Totals Row . Итоговая строка сразу же появится в конце вашей таблицы.

Иногда Excel может предположить, что вы хотите подвести итоги только в последнем столбце, а остальные ячейки в строке «Итого» оставить пустыми. Чтобы исправить это, выберите пустую ячейку в строке «Итого», щелкните стрелку, появившуюся рядом с ячейкой, а затем выберите в списке функцию СУММ:

Будет вставлена формула ПРОМЕЖУТОЧНЫХ ИТОГОВ, которая суммирует значения только в видимых строках , игнорируя отфильтрованные строки:

=ПРОМЕЖУТОЧНЫЙ ИТОГ(109,[январь])

Обратите внимание, что эта формула работает только в строке Итого . Если вы попытаетесь вручную вставить его в строку данных, это создаст циклическую ссылку и в результате вернет 0. Формула СУММ со структурированной ссылкой тоже не будет работать по той же причине:

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

=СУММ(B2:B5)

Вне таблицы формула СУММ со структурированной ссылкой работает нормально:

=СУММ(Продажи[январь])

Обратите внимание, что, в отличие от ПРОМЕЖУТОЧНЫХ ИТОГОВ, функция СУММ суммирует значения во всех строках, видимых и скрытых.

Относительные и абсолютные структурированные ссылки в Excel

По умолчанию структурированные ссылки Excel ведут себя следующим образом:

Ссылки на несколько столбцов являются абсолютными и не изменяются при копировании формул.
Ссылки на один столбец являются относительными и изменяются при перетаскивании по столбцам. При копировании/вставке соответствующей командой или сочетаниями клавиш (Ctrl+C и Ctrl+V) они не изменяются.

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

Абсолютная структурированная ссылка на один столбец

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

Относительная ссылка на столбец (по умолчанию)

таблица[столбец]

Абсолютная ссылка на столбец

таблица[[столбец]:[столбец]]

Чтобы сделать абсолютную ссылку на текущую строку , поставьте перед идентификатором столбца символ @:

таблица[@[столбец]:[столбец]]

Чтобы увидеть, как относительные и абсолютные ссылки на таблицы работают на практике, рассмотрим следующий пример.

Предположим, вы хотите сложить данные о продажах определенного продукта за 3 месяца. Для этого мы вводим название целевого продукта в какую-либо ячейку (в нашем случае F2) и используем функцию СУММЕСЛИ, чтобы получить общее количество продаж января :

=СУММЕСЛИ(Продажи[Элемент], $F$2, Продажи[январь])

Проблема в том, что когда мы перетаскиваем формулу вправо для расчета итогов за два других месяца, ссылка [Элемент] изменяется, и формула ломается:

Чтобы исправить это, сделайте ссылку [Item] абсолютной, но оставьте [Jan] относительной:

=СУММЕСЛИ(Продажи[[Элемент]:[Элемент]], $F$2, Продажи[январь])

Теперь вы можете перетащить измененную формулу в другие столбцы, и она отлично работает:

Относительная структурированная ссылка на несколько столбцов

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

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

Абсолютное задание диапазона (по умолчанию)

таблица[[столбец1]:[столбец2]]

Ссылка на относительный диапазон

таблица[столбец1]:таблица[столбец2]

Для ссылки на текущую строку внутри таблицы используйте символ @:

[@column1]:[@column2]

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

=СУММ(Продажи[@[Январь]:[Фев]])

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

=СУММ(Продажи[@Январь]:Продажи[@Фев])

Обратите внимание на преобразование формулы в столбце F (имя таблицы опущено, так как формула находится внутри таблицы):

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

Вас также может заинтересовать

Как заблокировать ссылки на формулы ячеек в Excel при использовании таблиц данных

Быстрая навигация

Одной из самых мощных функций формул Excel является возможность создавать абсолютные ссылки, которые не перемещаются при перетаскивании, чтобы расширить формулы ячеек или скопируйте их в разные места электронной таблицы. Большинство пользователей Excel выясняют, как заблокировать эти ссылки, либо переключая параметры с помощью F4 или с помощью символа $ (знак доллара) перед ссылками на столбцы и/или строки.

Когда вы начинаете использовать таблицы данных в Excel, многие пользователи с разочарованием обнаруживают, что обычные методы закрепления ссылок на строки или столбцы не работают! Это может занять много времени, чтобы построить ваши электронные таблицы, когда они используют таблицы. К счастью, есть обходные пути! В этом кратком руководстве мы рассмотрим некоторые из них…

Почему мы используем таблицы данных в первую очередь?

Если вам интересно, почему вы вообще хотите использовать таблицы данных вместо просто упорядоченных ячеек в Excel, вероятно, стоит быстро освежить в памяти…

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

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

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

`Как работают обычные ссылки на таблицы данных`

`Ссылки на столбцы таблицы данных`

При работе с таблицами данных в Excel ссылки на ячейки выглядят немного иначе, чем обычно A1 комбинация букв и цифр для столбцов и строк. Это связано с тем, что каждая строка таблицы действует так, как будто она находится в своей собственной электронной таблице с 1 строкой. Это означает, что ссылки должны относиться только к имени таблицы и столбцу. Строка считается самой собой.

Давайте посмотрим на простую таблицу: Если бы мы хотели сложить все столбцы Продажи в таблице данных, формула выглядела бы так:

 =  СУММА  (  Таблица1[Продажи]  )

Выход для этой формулы:

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

Теперь добавим данные в таблицу: Формула для суммирования столбца Продажи остается прежней:

 =  СУММ  (  Таблица1[Продажи]  )

Однако теперь вывод для этой формулы обновился автоматически:

Без каких-либо действий вычисления включают дополнительные данные! Не нужно менять ссылки в формуле SUM() !

`Пример таблицы данных с несколькими ссылками`

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

 =  СУММЕСЛИМН  (  [Продажи]  ,  [Регион]  ,  [@Регион]  )

Должно получиться так:

5

Обратите внимание, что последний термин – критерии для СУММЕСЛИМН () – использует @ (символ «at»). Это обозначение означает, что вы имеете в виду значение столбца столбца Region в текущей строке . Просто нажмите ВВЕДИТЕ , чтобы заполнить этой формулой весь столбец, и вы увидите результат этой ссылки в заполненной таблице.

Вычисление СУММЕСЛИ () суммирует все Продажи для каждого Региона , давая соответствующую сумму для каждой строки из региона Север (а также для каждого из остальных).

Теперь, когда вы видите, какими мощными могут быть таблицы данных Excel, вы, вероятно, захотите их использовать… Чтобы быстро и надежно создавать сложные формулы с использованием таблиц данных, вам необходимо иметь возможность блокировать ссылки, поэтому мы узнать дальше… Нажмите вперед!

`Как создавать блокирующие ссылки`

Обычно легко создавать столбцы и строки, блокируя ссылки в формулах с помощью F4 или добавляя знаки доллара в строку и столбец ссылки… Это позволяет перетаскивать формулы вниз на несколько строк или по нескольким столбцам, чтобы быстро построить рабочую электронную таблицу.

`Пример обычных относительных и абсолютных (заблокированных) ссылок на ячейки`

Возьмем простой пример:

Здесь мы создаем формулу для расчета квартального дохода на основе объема и цены за единицу. Объем будет меняться каждый квартал в новой колонке, но цена за единицу останется прежней, поэтому мы блокируем ссылку, нажимая F4 или введите знаки доллара для $F$2 вручную.

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

Когда мы отпускаем кнопку, вычисляются формулы для каждого квартала. Ссылка на ячейку объема (выделена синим цветом) остается «относительной» ссылкой, которая перемещается вместе с ячейкой формулы. Однако ссылка на цену за единицу (выделена красным) стала «абсолютной», то есть 9 0003 заблокировал в его исходном местоположении.

`Создание абсолютных (блокирующих) ссылок в таблицах данных`

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

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

`Как построить ссылки блокировки в таблицах данных`

`Пример данных`

Давайте расширим нашу таблицу региональных продаж до полных 6 месяцев. Так могли бы выглядеть ваши данные, если бы вы импортировали их из базы данных или другого программного обеспечения для создания отчетов. Это очень удобно для машинного чтения, но не для человека. В этом примере мы собираемся назвать таблицу в Excel как DataTable .

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

Эта таблица будет называться Сводная таблица . Чтобы заполнить эту таблицу данными, мы будем использовать другую простую формулу СУММЕСЛИМН (), но мы будем работать внутри таблицы и искать информацию внутри другой таблицы…

`Построение формулы, которая относится к заблокированным Столбцы и строки таблицы данных`

Начиная со столбца North и строки Jan-16 , постройте следующую формулу:

 =  СУММЕСЛИМН  ( Таблица данных[[Продажи]:[Продажи]] ,  Таблица данных[[Регион]:[Регион]] ,  B$1 ,  Таблица данных[[Месяц]:[Месяц]] ,  Сводная таблица[@[Месяц]:[Месяц]]  )

Это будет выглядеть так:

В этой формуле мы блокируем ссылки тремя разными способами:

Ссылка на весь заблокированный столбец в Таблица данных.
Ссылка на текущую строку заблокированного столбца в таблице данных.
Ссылка на заблокированную строку заголовка таблицы данных.

Каждый из них использует немного отличающийся синтаксис, поэтому я покажу каждый из них по порядку:

`1. Блокировка целых столбцов таблицы данных`

Обычные ссылки на столбцы таблицы данных выглядят так, когда вы находитесь внутри одной и той же таблицы:

  [Продажи]

Когда вы находитесь вне таблицы, в которой находится столбец, они выглядят следующим образом:

  DataTable[Продажи]

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

  DataTable[[Продажи]:[Продажи]]

`2.`

Ссылка на текущую строку заблокированного столбца таблицы данных Нормальные текущие ссылки на строки для таблиц данных выглядят так, когда вы находитесь внутри той же таблицы: [@Month] Когда вы находитесь вне таблицы, в которой находится столбец, они выглядят следующим образом: PivotTable[@Month] Подобно блокировке всего столбца, как показано выше, хитрость блокировки текущая ссылка на строку должна рассматриваться как диапазон, но символ @ используется только в начале ссылки на диапазон: Сводная таблица[@[Месяц]:[Месяц]] 3. Блокировка заголовка таблицы данных Ссылка Работать с заголовками таблиц данных Excel немного сложно, поскольку Excel обрабатывает их как фиксированные элементы. Это означает, что он не будет помнить, что это даты или другие специальные типы данных. Они становятся именами полей. Предполагается, что количество столбцов в вашей таблице данных не изменится со временем — только количество строк. Это означает, что для ссылки на заголовок в таблице данных в формуле (и чтобы эту формулу можно было перетаскивать), необходимо заблокировать ссылку. К счастью, строка заголовка таблицы данных всегда остается на одном месте, поэтому вы можете заблокировать ее, как обычную ссылку на ячейку в Excel. В приведенном выше примере формулы именно это мы и сделали: B$1 Это обозначение гарантирует, что при перетаскивании формулы она всегда будет ссылаться на строку 1 в текущем столбце. (Номер строки заблокирован, но столбец по-прежнему является относительным.) Завершенная формула таблицы данных После перетаскивания формулы вниз и по всей новой таблице данных сводной таблицы вы должны увидеть динамическое изменение уравнения для заполнения остальных ячеек. . Новая формула в ячейке E7 считывает: = СУММЕСЛИМН ( DataTable[[Продажи]:[Продажи]] , DataTable[[Регион]:[Регион]] , E$1 , Data:Table[[Месяц] ]] , Сводная таблица[@[Месяц]:[Месяц]] ) Если все сделано правильно, формула выглядит очень похоже в каждой ячейке таблицы данных! Единственное, что изменится, — это ссылки на строку заголовка таблицы данных, поскольку это обычные ссылки блокировки для ячеек Excel.