Работа с таблицами в excel формулы: Самые полезные формулы Excel — Раздел 5. Урок 1 — Онлайн-курс «Digital-аналитика» — This is Data

Практическая работа по теме «Работа с электронными таблицами Excel»

Практическая работа.

Тема:
Работа с электронными таблицами Excel.

Цель: освоить основные принципы работы с электронными
таблицами.

Задачи: 

­ Изучить основные понятия.

­ Создать электронную таблицу в
Microsoft Excel.

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

­ Научиться выполнять в Microsoft
Excel построение диаграмм и графиков.

Теоретический материал.

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

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

Обработка включает в себя:

1)                    
Проведение различных вычислений с использованием мощного аппарата
функций и формул;

2)                    
Исследование влияния различных факторов на данные;

3)                    
Решение задач оптимизации;

4)                    
Получение        выборки     данных,      удовлетворяющих       определенным

критериям;

5)                    
Построение графиков и диаграмм; 6)
    Статистический анализ данных.

Таким образом,
применение электронных таблиц имеет достаточно обширный спектр.

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

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

Листы
можно добавлять или удалять, используя контекстное меню или через главное       меню:
        Вставка/Лист     и        Правка/Удалить лист
соответственно.

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

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

Последняя строка – строка
состояния.

Рабочее поле
редактора Excel представляет собой таблицу. Каждая ячейка этой таблицы имеет
свой адрес, который определяется латинскими буквами по горизонтали и арабскими
цифрами по вертикали. Например: А4, B12, AC23 и т.д. В ссылке на ячейку сначала
указывают имя столбца, затем номер строки. Всего ЭТ может содержать до 256
столбцов, пронумерованных от А до IV. Строки последовательно нумеруются
цифрами, от 1 до 65 536 (максимально допустимый номер строки). При формировании
формул используются именно ссылки на ячейки, которые содержат необходимые
данные. В ячейках размещают текст, числа, формулы.

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

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

Если в ссылке
используется знак $, то она называется   абсолютной, а адреса таких ссылок
абсолютными. Абсолютные адреса при перемещении формул не изменяются.

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

Например, ссылка $D$5 при
перемещении не изменится.

$D7
– при перемещении формулы с такой ссылкой будут меняться только номера строк.
Ссылка является абсолютной по столбцам и относительной по строкам.

D$7 – при
перемещении формулы изменятся только имена столбцов. Ссылка будет относительной
по столбцам и абсолютной по строкам.

Построение формул

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

Для
ввода формулы в Excel выделите ячейку, где она будет размещена, затем введите
знак = и далее введите числа, функции или ссылки на ячейки, которые будут
содержать данные.        После          завершения          ввода нажмите
ENTER. Например:

Ячейка С3 содержит произведение ячеек А3 и В3. обратите
внимание, что формула видна не только в самой ячейке, но и в специальной строке
формул, где ее всегда удобно исправить или просмотреть.

При выделении любой другой ячейки можно видеть результат
работы формулы:

Изменяя значения в
ячейках А3 и В3 можно увидеть, что Excel производит расчет новых данных
автоматически.

Для построения значений в Excel

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

1.      
Командой главного меню: Вставка / Функция.

2.      
При выборе знака = в ячейке с будущей формулой слева от строки
формул автоматически появится выборка десяти последних вызванных формул. Если
ни одна формула не подходит пользователю, необходимо выбрать в списке Другие
функции…, после чего появится Мастер функций.

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

Формат данных

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

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

1)               
правой кнопкой мыши вызвать контекстное меню, в котором выбрать
команду Формат ячеек для вызова окна Формат ячеек;

2)               
выбрать команду главного меню Формат / Ячейки… Результатом
будет также раскрытие командного окна Формат ячеек.

После открытия
окна Формат ячеек необходимо выбрать в окне Числовые форматы
нужный Вам формат. На рис. 3 приведен пример формата даты. Просто укажите один
из предлагаемых в окне справа форматов и нажмите ОК. В выбранной ячейке будет
применен выбранный вами формат.

Построение диаграмм и графиков

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

Создать диаграмму в Excel можно по шагам с
помощью Мастера диаграмм, вызов которого осуществляется с панели инструментов
соответствующей кнопкой. Сначала Мастер диаграмм предлагает выбрать тип
диаграммы (шаг 1), затем указать диапазон ячеек из соответствующей таблицы (шаг
2), если диапазон с исходными данными заранее не был выделен. Для выделения
несмежных областей с исходными данными следует сначала выделить первую область,
затем нажать клавишу <Ctrl> и, удерживая ее, выделить остальные области.
На последнем шаге (шаг 3) необходимо выбрать место расположения диаграммы: на
новом листе или на текущем.  

Для
редактирования диаграммы выделите ее. Затем вызовите контекстное меню, щелкнув
по любому месту диаграммы. Через команды контекстного меню в построенную
диаграмму можно вносить различные изменения: менять ее тип, изменять текст и
шрифты, добавлять и удалять данные, метки и т. д.      

Расчетные операции в Microsoft Excel. 

Создание и редактирование таблиц. Расчет по формулам.

Задание 1.1. Создание
таблиц в Microsoft Excel.

Составить таблицу по теме «Общий анализ крови», используя
образец. 

Задание
1. 2. Составить таблицу. «Отчет движения медикаментов за текущий
месяц» в табличном процессоре EXCEL. Используя математические формулы,
заполнить данными столбец «РАСХОД».

Методические
рекомендации для выполнения задания 1.2.

1.                     
Сделать текущей ячейку А1. Ввести заголовок таблицы «Отчет
движения медикаментов», нажать на клавиши Alt (левый)+ Enter, набрать на
клавиатуре «за январь 2020 года». Выделить ячейки А1 — А7 (они
становятся черными). Нажать на значок а. Заголовок
располагается по середине 7 столбцов;

2.                     
Сделать текущей ячейку А2. Ввести «№», нажать Alt + Enter,
набрать на клавиатуре «п/п»;

3.                     
Сделать текущей В2. Ввести «Наименование медикаментов»

4.                     
Сделать текущей ячейку С2. 

•          
Ввести «Единицы»

•          
Нажать на комбинацию клавиш Alt + Enter

•          
Ввести «измерения»

5.                     
Сделать текущей ячейку  D2

•          
ввести «Остаток»

•          
нажать на комбинацию клавиш Alt (левый) + Enter  ввести  «на начало»

•          
нажать на комбинацию клавиш Alt (левый) + Enter

•          
ввести «месяца»

6.                     
Сделать текущей ячейку Е2 

•          
ввести «Приход»

7.                     
Сделать текущей ячейку F2

•          
ввести «Расход»

8.                      
Произвести щелчок на ячейку D2

•          
произвести щелчок на ПРАВКА из зоны меню

•          
произвести щелчок на КОПИРОВАТЬ из дополнительного меню

•          
произвести щелчок на ячейку G2

•          
произвести щелчок ПРАВКА из зоны меню

•          
произвести щелчок на ВСТАВИТЬ

•          
щелкнуть мышью на «начало» и удалить это слово

•          
ввести слово «конец»

9.                     
Сделать текущим А3 , ввести «1.»

10.                 
Сделать текущей В3 ввести «Аспирин»

Заполнить таким образом
необходимое количество строк в таблице

11.                 
После заполнения таблицы, делаем расчет столбца «Расход» используя
формулы. Для этого:

•          
сделать текущей ячейку F3

•          
ввести форму для расчета  =D3+E3-G3, нажать Enter,  появится
результат расчета

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

•          
Сделать текущей ячейку  F3

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

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

•          
кнопку мыши отпустить.

В результате
выполненных операций во всех стоках графы РАСХОД появится результат и
таблица будет полностью заполнена.

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

Задание 1.3. Рассчитать общую стоимость
стоматологических услуг.

Стоматологическая
поликлиника

Построение
диаграмм и графиков в Microsoft Excel

Задание 2. 1. Используя данные
таблицы из задания 1.2, составить диаграммы следующих разновидностей:

а) гистограмму по всем
медикаментам;

б) круговые диаграммы (разных
видов) по отдельным медикаментам;

Методические рекомендации для выполнения задания по
построению диаграммы:

1)      Построить диаграмму —
гистограмму:

•      выделить
всю таблицу за исключением заголовка;

•      щёлкнуть
на ВСТАВКА;

•      выбрать
ДИАГРАММА;

•      щёлкнуть
на НА ЭТОМ ЛИСТЕ;

•      натянуть
курсором мыши рамку на рабочем листе, тем самым зарезервировав место для
диаграммы;

шаг 1: в появившемся окне
«Мастер диаграмм» выбрать Далее 

шаг 2: определить тип
диаграммы, через щелчок мышью на Гистограмма и нажать Далее;

шаг 3: выбрать вид
гистограммы под № 8 и нажать Далее; шаг 4: в появившемся окне «Мастер
диаграмм» появится образец, нажать  Далее;

шаг 5: в появившемся окне
«Мастер диаграмм» выбрать Добавить легенду? Да и ввести название диаграммы
«Учёт движения медикаментов» и щёлкнуть на Готово.

2)
Построение круговой диаграммы по отдельным медикаментам различных видов:

•      выделить
отдельную строку по какому-либо медикаменту;

•      щёлкнуть
на ВСТАВКА;

•      выбрать
ДИАГРАММА;

•      щёлкнуть
на НА  НОВОМ ЛИСТЕ; шаг 1: в появившемся окне «Мастер диаграмм» выбрать
Далее; шаг 2: в окне «Мастер диаграмм» выбрать тип диаграммы Круговая и
Далее;

шаг 3: в окне «Мастер
диаграмм» выбрать вид круговой диаграммы из предложенных вариантов и Далее;

шаг 4: в окне «Мастер
диаграмм» отметить щелчком Да (Добавить легенду?), ввести название диаграмм,
название медикамента и щёлкнуть на Готово.

Задание 2.2. Составить график измерения
температуры больного за 10 дней, относительно нормальной температуры.

Например:

График изменения температуры больного ФИО

Задание 2.3. Построение
круговой и столбиковой диаграмм.

 Составить
круговую и столбиковые диаграммы составных частей лекарственного
препарата-драже «Ревит», ориентируясь на следующие данные:

Задание 6. Построить
гистрограмму и проанализировать демографические показатели города N(в промилях)
за несколько лет. Показатели строки

«Естественный прирост» вычислите по формулам.

Работа в Excel.

Создание таблиц в Excel

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

Содержание

• 1 Таблица Excel
• 2 Изменение размеров столбцов и строк
• 3 Объединение ячеек
• 4 Границы ячеек
• 5 Вставка формул
• 6 Видео

Таблица Excel

Рабочее поле Excel, как говорилось ранее, представляет собой таблицу, множество строк, столбцов и ячеек. Каждый столбец здесь имеет буквенное, а каждая строка числовое обозначение, таким образом, каждая ячейка в определенной строке и столбце имеет свои координаты, например левая верхняя ячейка имеет координаты А1 (или В5, как на рисунке).

Таблица Excel и координаты ячеек

Изменение размеров столбцов и строк

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

Объединение ячеек

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

Объединение нескольких ячеек в одну

Границы ячеек

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

Отображение границ ячеек

Вставка формул

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

В первом случае, нужно выделить ячейку, в которой должен будет отражен результат и во вкладке формулы нажимаем «автосумма». Excel автоматически предложит выделением, сумму каких ячеек нужно показать, но мы имеем возможность изменить их, то есть выделить другие, нужные нам, ячейки. После нажатия клавиши “Enter” требуемый результат будет показан.  При автоматическом вводе «автосуммы», Excel суммирует только ряд близлежащих ячеек, но если нужно выделить ячейки, расположенные не в ряд, то сделать это можно кликая мышью по нужным ячейкам при нажатой клавише “Ctrl”.

При ручном способе вставки формул, также выделяем итоговую ячейку и в ней забиваем формулу, т. е. сумму каких ячеек нужно здесь показать. Все формулы начинаем с символа «=». При написании формулы, ячейки можно указывать как буквенно-численные координаты, так и просто указывая на них курсором мыши. Формула может быть записана так: =СУММ(C4;B3;D5;E6), или так: = C4+B3+D5+E6). Таким способом можно находить не только сумму значений ячеек, но и другие функции.

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

Видео

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

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

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

• Что такое структурированные ссылки в Excel?
• Как создать структурированную ссылку в Excel
• Синтаксис ссылки на таблицу Excel
• Структурированные ссылки Excel — примеры формул
• Абсолютные структурированные ссылки в формулах Excel

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

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

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

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

=СУММ(B2:B5)

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

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

Основные возможности структурированных ссылок

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В итоге имеем вот такую ​​формулу:

=MAX(Продажи)

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

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

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

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

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

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

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

Имя таблицы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оператор объединения (запятая)

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

Например, вот как можно суммировать строки данных в Юг и Запад колонки.

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

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

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

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

=Regions[#Totals] Regions[[#All],[West]]

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

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

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

1.

Заключите спецификаторы в квадратные скобки

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

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

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

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

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

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

3. Не используйте кавычки вокруг заголовков столбцов

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

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

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

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

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

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

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

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

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

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

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

СТОЛБЦ( таблица )

СТРОКИ( таблица )

Например, чтобы найти количество столбцов и строк данных в таблице с именем Продажи = 3UMOLNS , используйте следующие формулы: 00000 Продажи)

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

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

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

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

Подсчет пустых и непустых значений в столбце

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

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

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

Бланки:

= countblank (Продажи [Jan])

Не-бланки:

= Counta (Sales [Jan])

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

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

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

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

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

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

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

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

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

=СУММ(B2:B5)

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

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

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

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

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

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

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

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

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

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

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

Текущая префикс для строки идентификатор столбца символом @:

table[@[column]:[column]]

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

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

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

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

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

=СУММЕСЛИ(Продажи[[Товар]:[Товар]], $F$2, Продажи[Янв])

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

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

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

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

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

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

Относительная ссылка диапазона

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

5

таблица , используйте символ @:

[@column1]:[@column2]

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

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

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

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

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

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

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

Как использовать формулу в таблице Excel (4 подходящих примера)

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

Скачать книгу с практиками

Что такое таблица Excel?

Создать формулу и применить к таблице Excel

Еще 3 примера с формулой в таблице Excel

1. Формула СУММЕСЛИ с одним критерием

2. Формула СУММЕСЛИМН с несколькими критериями

3. Формула COUNTIF для подсчета на основе критериев

Вывод

Статьи по Теме

Загрузить учебник

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

Что такое таблица Excel?

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

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

То, как мы используем формулу Excel на типичном листе Excel и в таблице Excel, немного отличается, но не так сложно. Я покажу несколько простых примеров, которых будет достаточно, чтобы научиться этому. Начнем с функции СУММ . Перед этим я сначала познакомлю вас с моим набором данных. Он представляет информацию о продаже: идентификатор заказа, дата, проданная единица и тип продажи компании.

Сначала мы преобразуем наш набор данных в таблицу.

Шаг 1:

⏩ Выберите любую ячейку из набора данных.

⏩ Затем щелкните следующим образом: Вставка > Таблицы > Таблица

Шаг 2:

⏩ Теперь просто нажмите OK здесь.

Смотрите, наш стол готов. Я изменил название таблицы на «Продажи». Для этого-

Шаг 3:

⏩ Нажмите любые данные в таблице и нажмите Дизайн таблицы > Имя таблицы

Теперь подсчитаем общее количество проданных единиц с помощью функции СУММ .

Шаг 4:

⏩ Активировать Ячейка D14 .

⏩ Введите =СУММ(Продажи[

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

⏩ Выберите единиц продано.

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

Шаг 5:

⏩ Наконец, закройте скобки и нажмите кнопку Enter .

Теперь у нас есть общее количество проданных единиц.

Рассчитать общую стоимость:

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

Шаги:

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

Вот наш новый столбец.

Кроме того, вы можете нажать- Главная > Ячейки > Вставить > Вставить столбцы таблицы справа , чтобы добавить столбец.

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

Шаги:

⏩ В ячейке E5 введите приведенную ниже формулу-

=СУММ(Таблица1[@[Цена]:[Стоимость доставки]])

⏩ Нажмите кнопку Введите на клавиатуре.

Теперь посмотрите, мы нашли общие значения для всех строк. Здесь нет необходимости использовать инструмент Fill Handle . Это преимущество таблицы Excel.

Подробнее: Создать таблицу в Excel с помощью ярлыка (8 способов)

3 Дополнительные примеры с формулами в таблице Excel

Давайте рассмотрим еще три примера использования формул в таблице Excel.

1. Формула СУММЕСЛИ с одним критерием

Допустим, мы посчитаем проданные единицы, которые были проданы в онлайне с функцией СУММЕСЛИ . Функция СУММЕСЛИ возвращает сумму ячеек, удовлетворяющих одному условию.

Шаги:

⏩ Введите данную формулу в ячейку D14 –

=СУММЕСЛИ(Продажи1[Тип продажи],"Онлайн",Продажи1[Продано единиц])

⏩ Затем нажмите кнопку Введите .

Вот наш вывод-

Подробнее: Как использовать таблицу Excel с VBA (9 возможных способов)

Аналогичные показания

• Как создать таблицу в Excel (с настройкой)
• Использование вычисляемого поля в сводной таблице Excel (8 способов)
• Вычисленная сумма полей, деленная на количество в сводной таблице
• Как пользоваться таблицей Excel (10 примеров)
• Код Excel VBA для каждой строки в таблице (добавление, перезапись, удаление и т. д.)

2. Формула СУММЕСЛИМН с несколькими критериями

Здесь мы будем считать единицы с несколькими критериями. Мы будем считать только те единицы, которые являются продуктами питания и продаются в магазине, используя функцию СУММЕСЛИМН . Функция СУММЕСЛИМН возвращает сумму ячеек, удовлетворяющих нескольким условиям.

Шаги:

⏩ При активации Cell D14 напишите данную формулу-

=СУММЕСЛИМН(Продажи2[Проданная единица],Продажи2[Категория],"Еда",Продажи2[Тип продажи],"Магазин")

⏩ Позже просто нажмите кнопку Enter .

Мы закончили операцию.

Подробнее: Существует ли функция ТАБЛИЦА в Excel?

3. Формула COUNTIF для подсчета на основе критериев

В нашем самом последнем примере мы будем подсчитывать онлайн-заказы с помощью функции СЧЁТЕСЛИ .