Excel работа с таблицами: Создание и ведение таблиц Excel

Содержание

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

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

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

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

Выделить любую ячейку, содержащую данные, которые должны будут войти в таблицу.
В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table].

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

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

ОК.

Присвоение имени таблице

По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя.

Выделить ячейку таблицы.
На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле
Имя таблицы нажать клавишу Enter.

Требования к именам таблиц аналогичны требованиям к именованным диапазонам.

Форматирование таблиц

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

Выделить ячейку таблицы.
На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].

Вычисления в таблицах

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

На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].

В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию.

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

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

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

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

Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
OK.

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

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

Для этого необходимо выполнить следующие шаги:

На вкладке Конструктор [Design] выбрать группу Сервис [Tools].
Выбрать вкладку Преобразовать в диапазон [Convert to Range].

Нажать на кнопку Да [Yes].

Создание связи между двумя таблицами в Excel

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

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

Все таблицы в книге указываются в списках полей сводной таблицы и Power View.

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

Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.

Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01. 01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.

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

В окне Управление связями нажмите кнопку Создать.

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

Дополнительные сведения о связях между таблицами в Excel

Примечания о связях

Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных связи таблиц могут быть типа «один к одному» (у каждого пассажира есть один посадочный талон) или «один ко многим» (в каждом рейсе много пассажиров), но не «многие ко многим». Связи «многие ко многим» приводят к ошибкам циклической зависимости, таким как «Обнаружена циклическая зависимость». Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью «многие ко многим» или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением «один ко многим», но между первой и последней образуется отношение «многие ко многим»). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.

Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

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

Запустите надстройку Power Pivot в Microsoft Excel и откройте окно Power Pivot.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Введите свои учетные данные Майкрософт и нажмите Sign in (Вход). Откроется окно предварительного просмотра данных.
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите кнопку Далее.
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.
В разделе Type (Тип) нажмите Data Данные).
В разделе Price (Цена) нажмите Free (Бесплатно).
Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите кнопку Далее.
Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
В списке полей, в разделе «Могут потребоваться связи между таблицами» нажмите Создать.
В поле «Связанная таблица» выберите On_Time_Performance, а в поле «Связанный столбец (первичный ключ)» — FlightDate.
В поле «Таблица» выберитеBasicCalendarUS, а в поле «Столбец (чужой)» — DateKey. Нажмите ОК для создания связи.
Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.

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

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

Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
В главной таблице нажмите Сортировка по столбцу.
В поле «Сортировать» выберите MonthInCalendar.
В поле «По» выберите MonthOfYear.

Сводная таблица теперь сортирует каждую комбинацию «месяц и год» (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.

«Могут потребоваться связи между таблицами»

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

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

Шаг 1.

Определите, какие таблицы указать в связи

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

Примечание: Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение «Могут потребоваться связи между таблицами». Наиболее вероятной причиной является то, что вы столкнулись со связью «многие ко многим». Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей «один ко многим» между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.

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

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

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

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

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

Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.

К началу страницы

Работа с таблицами в Excel

Вначале пару слов о файле Excel. Файл Excel называется книгой. В рамках книги (файла) есть листы Excel. Как в обычной бумажной книге есть листы, так и в файле Excel придумали: открываешь книгу, а там есть листы и их можно переворачивать. В целом удобно, наглядно и понятно. Каждый лист в отдельности – это здоровенная таблица. После создания файла или листа, эта исходная таблица пустая. Затем в неё вносятся некоторые данные. Это как детская мозаика – есть пустое полотно, которое заполняется нужным узором из фишек. Только в Excel вместо полотна используется пустой лист, а вместо фишек – данные, которые вносятся в ячейки таблицы.

Заполненные ячейки представляют собой диапазон данных. Сплошной диапазон данных, за пределами, которого отсутствует информация обычно и называется таблицей. У каждой ячейки есть свои координаты, то есть адрес, который представляет собой название столбца и строки, на пересечении которых и находится данная ячейка. Столбцы обычно обозначаются буквами, строки – числами. Ячейка A1 – это самая верхняя и самая левая ячейка, В1 – самая верхняя и вторая слева, C1 – самая верхняя и третья слева, A2 – вторая сверху и самая левая, A3 – третья слева и самая левая и так далее. Всё это пока бесполезная и всем очевидная информация. Но надо же с чего-то начать. Идём дальше.

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

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

Рассказывать про таблицы словами можно очень долго и толку от этого никакого не будет. Поэтому переходим к наглядным примерам. Допустим, нам нужно проанализировать состояние товарных запасов на складе. То есть требуется определить, какие товары нужно заказать у поставщика, а каких товаров наоборот слишком много и их нужно побыстрее продать, чтобы позже не пришлось уничтожать из-за окончания сроков годности. На данном примере я покажу, как легко можно получить ценную информацию из большого массива данных и на её основе принять грамотные рациональные решения, которые принесут большую пользу (то есть много денег). Исходная информация – это, конечно, остатки товара на складе. Однако темпы продажи у разных товаров различные, поэтому данные только о количестве товара на складе не будут достаточно информативными. Один товар продается по 10 штук в месяц, другой – по 100 штук в месяц. Очевидно, что одинаковое количество товара на складе будет означать разный период продаж, на который хватит остатков. Поэтому для анализа нужно привлечь данные о темпах продаж. Обычно в этой роли выступают средние продажи за прошлый период. Их и возьмём.

Исходная таблица будет имеет вид.

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

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

Из такой таблицы уже можно делать некоторые выводы. Предположим, время, которое требуется на доставку товара на склад – 1 месяц. Теперь смотрим внимательно на последний столбец. Запас по некоторым товарам составляет менее 1 месяца, это значит, что в случае размещения заказа прямо сейчас дефицита уже не избежать, так как товар продадут раньше, чем успеют привезти новую партию. Нужно срочно делать новый заказ, а также не допускать подобной ситуации в дальнейшем, потому что дефицит товара – это недополученная прибыль и душевное расстройство клиентов.

Также видно, что запас по некоторым товарам существенно больше одного месяца. Это может означать, что в таком большом запасе необоснованно заморожены деньги. Замороженные денежные средства – это тоже зло (нужно брать кредиты и платить проценты).

Таким образом, в таблице можно выявить товары, которые следует быстрее продать, и товары, которые нужно срочно заказать у поставщика. Этот пример упрощён, в реальности объём данных и количество условий и ограничений в десятки и сотни раз больше. Ассортимент может состоять из тысяч позиций. Поэтому, даже если мы рассчитаем последний столбец, который показывает время, на которые хватит запаса, то у нас может уйти неделя на изучение состояние склада. А пока пройдёт неделя, ситуация поменяется и нужно будет снова обновлять данные. В общем, эта таблица хотя и отражает полезную информацию, но непригодна для оперативного использования. Для более точной, наглядной и быстрой работы в таблицу нужно внести дополнения. Одна из моих любимых функций Excel – условное форматирование. Её суть заключается в том, что при наступлении некоторого условия ячейка меняет свой формат (шрифт, цвет и проч.). Данная функция в автоматическом режиме обозначает критически важные значения, то есть товары, с которыми нужно срочно что-то сделать. Понятно, что все настройки и расчеты зависят от конкретной задачи. Нас, допустим, в первую очередь интересует недопущение дефицита, то есть максимально возможное наличие всего ассортимента на складе. Для этого нужно внимательно контролировать запасы, то есть не допускать, чтобы их уровень уменьшился ниже, чем 1 месяц продаж (время на пополнение).

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

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

Видно, что срочно нужно размещать заказ на 6 позиций. Причем 3 из них уже попадают в дефицит, заказ размещен слишком поздно. Но лучше, поздно, чем никогда. Яркость красок соответствует уровню опасности.

Представим теперь, что товар возится от разных поставщиков и заказы нужно планировать заранее, скажем, за 2 недели до отправки заявки. Поэтому когда запас товара меньше, чем 2 месяца продаж, но еще не достиг 1,5 месяца, нам нужно об этом знать. Легко!

Зелененькие ячейки – это и есть товары, которые следует заказать в ближайшее время.

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

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

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

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

Езепов Дмитрий
statanaliz.info

Перепечатка и перепостинг статьи вместе с этим текстом, указанием автора, и ссылки на первоисточник — приветствуются!

Глава 7 Работа с данными таблицы Excel

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

Сортировка данных:

1. Выделить данные в таблице.

2. Меню Данные Сортировка

3. В окне Сортировка диапазона указать столбец, по которому будет производиться сортировка, затем ее способ (по возрастанию или по убыванию).

Фильтрация данных:

Фильтрация это скрытие ненужных данных и показ нужных.

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

1. Укажите ячейки в фильтруемом списке.

2. Меню Данные Фильтр Автофильтр.

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

4. Выберите значение в списке.

5. Повторите шаги 3 и 4 для введения дополнительных ограничений значений в других столбцах.

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

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

Анализ данных

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

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

Чтобы запустить пакет анализа:

1. В меню Сервис выберите команду Анализ данных.

2. В списке Инструменты анализа выберите нужную строку.

3. Введите входной и выходной диапазоны, затем выберите необходимые параметры.

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

Создание диаграммы

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

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

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

С

оздание диаграммы:

1. Выделите ячейки, содержащие данные, которые должны быть отражены на диаграмме.

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

3. Меню Вставка Диаграмма.

4. Следуйте инструкциям Мастера.

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

Изменение диаграммы:

1. Выберите изменяемую диаграмму.

2. В меню Диаграмма выберите нужную строку, например Диапазон данных.

3. В окне редактирования внесите нужные изменения.

Вычисления

Автосуммирование

Сумму значений выделенных ячеек можно ввести автоматически с помощью кнопки S Автосуммирование.

При нажатии кнопки Автосуммирование автоматически предлагается формула вычисления, например = СУММ(E5:E10). Чтобы принять формулу, нажмите клавишу Enter.

Синтаксис формулы

Синтаксисом формул называется порядок, в котором вычисляются значения и задается последовательность вычислений. Формула должна начинаться со знака равенства (=), за которым следует набор вычисляемых величин. В следующем примере представлена формула, вычисляющая разность между числами 5 и 1. Результат выполнения отобразится в ячейке, в которой указана формула.

Ссылки на ячейку

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

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

Функции

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

1. Меню Вставка Функции f(x).

2. Следуйте за Мастером функций.

Помните, что Excel посчитает все числа, но правильность расчетов зависит от вашего знания математики и правильности задания формул.

Сводная таблица

Сводная таблица создается с помощью Мастера сводных таблиц, используемого для размещения и объединения анализируемых данных:

1. Меню Данные Сводная таблица.

2. Идите по шагам Мастера.

Подведение итогов в сводной таблице производится с помощью итоговой функции (например, «Сумма», «Кол-во значений» или «Среднее»).

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

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

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

Продолжим Назад

5 сервисов для работы с таблицами Эксель онлайн

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

Excel Online

Excel Online — веб-версия настольного приложения из пакета Microsoft Office. Она бесплатно предоставляет пользователям основные функции программы для работы с таблицами и данными.

По сравнению с настольной версией, в Excel Online отсутствует поддержка пользовательских макросов и ограничены возможности сохранения документов. По умолчанию файл скачивается на компьютер в формате XLSX, который стал стандартом после 2007 года. Также вы можете сохранить его в формате ODS (OpenDocument). Однако скачать документ в формате PDF или XLS (стандарт Excel до 2007 года), к сожалению, нельзя.

Впрочем, ограничение на выбор формата легко обойти при наличии настольной версии Excel. Например, вы можете скачать файл из веб-приложения с расширением XLSX, затем открыть его в программе на компьютере и пересохранить в PDF.

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

Есть у Excel Online и преимущества. Главное из них — возможность совместной работы над документом. Вы можете создать файл и открыть к нему доступ на чтение и редактирование любым пользователям, например, членам вашей команды. Чтобы это сделать, пройдите по пути «Файл» — «Поделиться» и еще раз «Поделиться».

Как и Word Online, Excel Online имеет два режима совместной работы:

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

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

В настройках общего доступа также есть функции установки срока действия ссылки и шифрования, однако для их использования нужен премиум-аккаунт OneDrive. В России его пока приобрести нельзя.

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

Файлы, созданные в Excel Online, по умолчанию сохраняются в облаке OneDrive. Доступ в него есть у каждого пользователя, имеющего аккаунт Майкрософт. В бесплатной версии OneDrive у вас будет 5 ГБ дискового пространства. Этого объёма достаточно для хранения миллионов таблиц.

Ещё один способ поделиться таблицей, созданной в Excel Online, — вставить её на сайт с помощью HTML-кода. Чтобы воспользоваться этой возможностью, пройдите по пути «Файл» — «Поделиться» — «Внедрить». Затем нажмите на кнопку «Создать». В окне предварительного просмотра, которое откроется после этого, можно выбрать, что из таблицы должно отображаться на сайте после вставки кода на страницу.

Все созданные документы размещены на главной странице сервиса Excel Online. Они размещены на трех вкладках:

«Последние» — недавно открытые документы.
«Закреплённые» — документы, рядом с названиями которых вы нажали на кнопку «Добавить к закреплённым».
«Общие» — документы других владельцев, к которым вам открыли доступ.

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

Для редактирования таблиц на смартфоне также можно использовать мобильное приложение Excel. У него есть версии для Android и iOS. После установки авторизуйтесь в приложении под тем же аккаунтом, которым вы пользовались в веб-версии, и вам будут доступны все файлы, созданные в Excel Online. Покупка Office 365 не требуется.

Excel Online на Яндекс.Диске

Веб-версия приложения Excel доступна также пользователям Яндекс.Диска — облачного хранилища от Яндекс. Чтобы в нее войти:

Откройте сайт Яндекса и авторизуйтесь в своей учётной записи.
Перейдите в интерфейс Яндекс.Диска.
Нажмите на кнопку «Создать» и выберите тип «Таблицу».

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

С точки зрения функциональности эта версия ничем не отличается от Excel Online на OneDrive. Единственная разница — документы, созданные здесь, по умолчанию сохраняются на Яндекс.Диске. Ими также можно делиться с другими пользователями при помощи публичных ссылок.

Google Sheets (Таблицы)

Google Sheets — главная альтернатива браузерной версии Excel. Гугловское веб-приложение предлагает заметно больше возможностей для работы с таблицами и данными, чем аналог от Майкрософт. Например, кроме предустановленных четырёх сотен функций, здесь доступно создание собственных скриптов. Кроме того, функциональность Google Sheets расширяется за счёт плагинов.

Ещё один плюс Google Sheets — тесная связь с другими гугловскими приложениями: Google Docs, Gmail, Google Translate, Google Forms, Google Sites, Google Calendar, Google Contacts, Google Groups и Google Maps. С помощью Google Apps Script (специальная версия JavaScript, предназначенная для работы с сервисами Google) можно автоматизировать различные процессы, например, настроить массовую рассылку писем, автоматически сохранять все вложения на Google Drive или добавлять напоминания в календарь.

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

В Google Sheets, как и в рассмотренных аналогах, есть возможность совместной работы над документами. Вы можете пригласить других пользователей по электронной почте или создать и разослать им публичную ссылку. Возможные права доступа — только просмотр, комментирование или редактирование.

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

Файл, созданный в Google Sheets, можно сохранить в форматах XLSX, ODS, PDF, в виде веб-страницы HTML, CSV и TSV. Это тоже делает сервис заметно более гибким, чем Excel Online.

Документы, созданные в Google Sheets, хранятся в облаке Google Drive. На главной странице веб-приложения есть их список, отсортированный по дате просмотра. Эти же файлы можно просматривать и редактировать через бесплатное мобильное приложение для Android и iOS.

Zoho Sheet

Zoho Sheet — еще одно онлайн-средство для просмотра и простого редактирования файлов Excel. Сервис поддерживает форматы XLS, XLSX ODS, CSV и TSV. Вы можете загрузить в него файл из памяти компьютера или импортировать по ссылке с других веб-ресурсов.

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

Полноценной заменой настольной версии Excel редактор Zoho не станет, но составить таблицу, добавить формулы и поработать с данными в нём можно.

Основные возможности редактора:

Совместная работа над документами.
Настройки блокировки ячеек и сохранение истории изменений для контроля внесённых правок.
Инструменты для автоматического преобразования данных в диаграммы и таблицы.
Более 350 функций для обработки данных.
Инструменты для очистки данных.
Поддержка макросов для автоматизации процессов.
Поддержка 23 языков, в том числе русского.

У Zoho Sheet также есть мобильные приложения для Android и iOS. При использовании одного аккаунта данные между веб-версией и данными на смартфоне синхронизируются. Это позволяет, например, создать документ в браузере на компьютере, а затем быстро внести в него правки с мобильного девайса.

Созданные в Zoho Street документы сохраняются на сервере в аккаунте пользователя. Загрузить их на компьютер можно в форматах XLS, XLSX, ODS, CS, TSV, HTML и PDF.

OnlyOffice

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

После авторизации вы попадёте на главный экран сервиса, откуда запускаются онлайн-версии приложений. Для работы с таблицами запустите OnlyOffice Spreadsheets.

Визуально редактор OnlyOffice Spreadsheets напоминает Эксель Онлайн, поэтому вам не придется тратить время на его освоение. С точки зрения функциональности эти веб-приложения тоже очень близки друг к другу.

OnlyOffice предлагает следующие возможности для работы с таблицами в браузере:

Редактирование и форматирование ячеек.
Формулы для выполнения расчётов.
Формирование таблиц и диаграмм из данных.
Сортировка и группировка данных.
Совместная работа над документом.

Конкурентное преимущество OnlyOffice перед Excel Online — поддержка макросов для автоматизации пользовательских действий. Создавать новые алгоритмы можно на вкладке «Плагины». Здесь же собраны инструменты для подсветки кода, редактирования изображений, подбора синонимов, перевода текста и вставки видео с YouTube.

Однако веб-версия OnlyOffice не может использоваться для открытия файлов с компьютера. Сервис поддерживает только те, что были созданы в нем. Вы можете хранить документы на «облаке» или скачать на свои устройства в форматах XLSX, ODS, CSV, OTS, PDF.

Приложение OnlyOffice работает и на смартфонах на базе Android и iOS. При авторизации под тем же аккаунтом, что и в веб-версии, данные синхронизируются.

Помимо бесплатного редактора таблиц, OnlyOffice имеет коммерческую версию облачной платформы для организаций. Для тестирования её возможностей предоставляется пробный период на 180 дней. Стоимость подписки зависит от количества сотрудников. В среднем месячная лицензия на одного пользователя обходится в 4.25 евро. При годовой подписке экономия составит 40%, при покупке тарифа на 3 года — 60%.

Некоммерческим организациям и учебным заведениям OnlyOffice предоставляет бесплатный доступ к платным функциям. Для этого нужно зарегистрироваться, выбрать баннер, который будет размещён на главной странице сайта организации, и заполнить форму. Подробности — на этой странице.

Облачная платформа OnlyOffice не предназначена для домашнего использования, так как слишком сложна и довольно дорого стоит. Но если вы ищите онлайн-решение для организации, она станет отличной заменой Excel Online.

Как работать с таблицами в программе Excel | ITMaster

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

Основные понятия и функции

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

В меню входят разделы, свойственные всем компонентам пакета Майкрософт Офис: главная, вставка, разметка страницы, вид, рецензирование. Есть и вкладки, свойственные только этой программе: формулы и данные.

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

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

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

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

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

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

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

Это далеко не все преимущества экселевских документов. Само создание вычисляемых полей в Excel предельно простое. Вот нужные шаги для получения такого объекта:

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

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

Вычисления с помощью формул

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

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

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

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

Стандартные задачи при работе с таблицами и атрибутивными данными—ArcGIS Pro

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

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

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


Задача или рабочий процесс	Где получить более подробную информацию	Доступные инструменты геообработки
Создание новой таблицы	Создание наборов данных в базе геоданных	Создать таблицу Создать представление таблицы
Создание слоя из таблицы с данными координат x,y	Добавление данных координат X,Y в качестве слоя	XY Таблица в точки Копировать объекты
Импорт, копирование и преобразование источников табличных данных	Импорт данных Копирование наборов классов объектов, классов объектов и таблиц в базу геоданных Работа с файлами Microsoft Excel в ArcGIS Pro	Обзор группы инструментов Excel Таблица в таблицу Таблица в базу геоданных Таблица в dBASE Копировать объекты Копировать
Добавление и редактирование полей, доменов и подтипов	Обзор полей Создание, изменение и удаление полей слоя или таблицы Просмотр и редактирование полей, доменов и подтипов	Обзор группы инструментов Поля Обзор группы инструментов Домены Обзор группы инструментов Подтипы Добавить атрибуты геометрии
Отображение таблиц	Открытие табличных данных Сортировка записей в таблице Фильтрация данных в таблицах Скрытие столбцов таблицы Закрепление столбцов таблицы	Обзор группы инструментов Слои и представления таблицы Создать таблицу запроса Создать представление таблицы
Работа с выделенным поднабором	Интерактивный выбор записей в таблице Просмотр всех или только выбранных записей Подсветка записей в выделенном наборе
Редактирование атрибутивных значений	Редактирование активной таблицы Редактирование атрибутов объектов Добавление данных к существующей таблице Перейти к номеру строки Найти и заменить
Вычисление значений полей	Основы вычислений в полях Примеры вычислений полей	Вычислить поле Добавить атрибуты геометрии
Создание таких отношений между таблицами, как соединения, связи и классы отношений	Обзор соединений и связей Просмотр связанных данных Автоматический выбор связанных записей Поиск ближайшего объекта Поиск объектов, пересекающих другие объекты Поиск объектов внутри полигона	Обзор группы инструментов Соединения и связи Пространственное соединение
Геокодирование таблицы адресов	Что включается в результаты геообработки? Геокодирование таблицы адресов	Обзор набора инструментов Геокодирование Геокодировать адреса Стандартизировать адреса
Экспорт табличных данных	Экспорт данных Экспорт таблиц	Копировать строки Таблица в dBASE Таблица в таблицу Таблица в базу геоданных Экспортировать XML-документ рабочей области

Отзыв по этому разделу?

Обзор таблиц Excel — служба поддержки Office

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

Узнайте об элементах таблицы Excel

Таблица может включать в себя следующие элементы:

Строка заголовка По умолчанию таблица имеет строку заголовка.Для каждого столбца таблицы включена фильтрация в строке заголовка, чтобы вы могли быстро фильтровать или сортировать данные таблицы. Для получения дополнительной информации см. Фильтрация данных или Сортировка данных.
Вы можете отключить строку заголовка в таблице. Дополнительные сведения см. В разделе Включение и отключение заголовков таблиц Excel.
Строки с полосами Альтернативная заливка или полосатость в строках помогает лучше различать данные.
Вычисляемые столбцы Введя формулу в одну ячейку в столбце таблицы, вы можете создать вычисляемый столбец, в котором эта формула мгновенно применяется ко всем другим ячейкам в этом столбце таблицы. Дополнительные сведения см. В разделе Использование вычисляемых столбцов в таблице Excel.
Строка итогов После добавления итоговой строки в таблицу Excel предоставляет раскрывающийся список Автосумма для выбора таких функций, как СУММ, СРЕДНЕЕ и т. Д.Когда вы выбираете одну из этих опций, таблица автоматически преобразует их в функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, которая игнорирует строки, которые были скрыты с помощью фильтра по умолчанию. Если вы хотите включить в свои вычисления скрытые строки, вы можете изменить аргументы функции ПРОМЕЖУТОЧНЫЙ ИТОГ.
Для получения дополнительной информации также см. Суммирование данных в таблице Excel.
Ручка изменения размера Ручка изменения размера в правом нижнем углу таблицы позволяет перетаскивать таблицу до нужного размера.
Информацию о других способах изменения размера таблицы см. В разделе Изменение размера таблицы путем добавления строк и столбцов.

Создать таблицу

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

Чтобы быстро создать таблицу в Excel, сделайте следующее:

Выберите ячейку или диапазон данных.
Выберите Home > Format as Table .
Выберите стиль стола.
В диалоговом окне Форматировать как таблицу установите флажок рядом с Моя таблица как заголовки , если вы хотите, чтобы первая строка диапазона была строкой заголовка, а затем нажмите ОК .

Также посмотрите видео о создании таблицы в Excel.

Эффективная работа с табличными данными

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

Использование структурированных ссылок Вместо использования ссылок на ячейки, таких как A1 и R1C1, вы можете использовать структурированные ссылки, которые ссылаются на имена таблиц в формуле.Дополнительные сведения см. В разделе Использование структурированных ссылок с таблицами Excel.
Обеспечение целостности данных Вы можете использовать встроенную функцию проверки данных в Excel. Например, вы можете разрешить только числа или даты в столбце таблицы. Дополнительные сведения о том, как обеспечить целостность данных, см. В разделе Применение проверки данных к ячейкам.

Экспорт таблицы Excel на сайт SharePoint

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

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. Также

Отформатируйте таблицу Excel

Проблемы совместимости таблиц Excel

Форматирование таблицы Excel — служба поддержки Office

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

Вы можете дополнительно настроить форматирование таблицы, выбрав параметры быстрых стилей для элементов таблицы, таких как Заголовок и Итоговые строки , Первые и Последние столбцы , Строки с полосами и Столбцы , а также Автофильтрация .

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

Выберите стиль стола

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

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

Примечания:

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

Важно:

После создания пользовательские стили таблиц доступны в галерее «Стили таблиц» в разделе «Пользовательский».
Пользовательские стили таблиц хранятся только в текущей книге и недоступны в других книгах.

Создать собственный стиль таблицы

Выберите любую ячейку в таблице, которую вы хотите использовать для создания собственного стиля.
На вкладке Home щелкните Format as Table или разверните галерею Table Styles на вкладке Table Tools > Design (вкладка Table на Mac).
Щелкните Новый стиль таблицы , чтобы открыть диалоговое окно Новый стиль таблицы .
В поле Имя введите имя для нового стиля таблицы.
В поле Элемент таблицы выполните одно из следующих действий:
- Чтобы отформатировать элемент, щелкните элемент, затем щелкните Формат , а затем выберите нужные параметры форматирования на вкладках Font , Border или Fill .
- Чтобы удалить существующее форматирование из элемента, щелкните элемент, а затем нажмите Очистить .
В Preview вы можете увидеть, как внесенные вами изменения форматирования влияют на таблицу.
Чтобы использовать новый стиль таблицы в качестве стиля таблицы по умолчанию в текущей книге, установите флажок Установить как стиль таблицы по умолчанию для этого документа .

Удалить пользовательский стиль таблицы

Выберите любую ячейку в таблице, из которой вы хотите удалить пользовательский стиль таблицы.
На вкладке Home щелкните Format as Table или разверните галерею Table Styles на вкладке Table Tools > Design (вкладка Table на Mac).
В Custom щелкните правой кнопкой мыши стиль таблицы, который вы хотите удалить, а затем выберите Удалить в контекстном меню.
Примечание: Все таблицы в текущей книге, которые используют этот стиль таблицы, будут отображаться в формате таблицы по умолчанию.

Выберите любую ячейку в таблице, из которой вы хотите удалить текущий стиль таблицы.
На вкладке Home щелкните Format as Table или разверните галерею Table Styles на вкладке Table Tools > Design (вкладка Table на Mac).
Щелкните Очистить .
Таблица будет отображаться в формате таблицы по умолчанию.

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

Есть несколько вариантов стиля таблицы, которые можно включать и выключать.Чтобы применить любой из этих вариантов:

Выберите любую ячейку в таблице.
Перейдите к Инструменты таблицы > Дизайн или вкладка Таблица на Mac и в группе Параметры стиля таблицы установите или снимите отметку с любого из следующего:
- Строка заголовка — Применить или удалить форматирование из первой строки в таблице.
- Строка итогов — Быстро добавляйте в таблицу функции ПРОМЕЖУТОЧНЫЙ ИТОГ, такие как СУММ, СРЕДНЕЕ, СЧЁТ, МИН / МАКС, из раскрывающегося списка. Функции ПРОМЕЖУТОЧНЫЙ ИТОГО позволяют включать или игнорировать скрытые строки в вычислениях.
- Первый столбец — Применить или удалить форматирование первого столбца в таблице.
- Последний столбец — Применить или удалить форматирование последнего столбца в таблице.
- Строки с полосами — Отображение четных и нечетных строк с чередованием затенения для облегчения чтения.
- Чередующиеся столбцы — Отображение четных и нечетных столбцов с чередованием затенения для удобства чтения.
- Кнопка фильтра — Включение и выключение автофильтра.

В Excel в Интернете вы можете применить параметры стиля таблицы для форматирования элементов таблицы.

Выберите параметры стиля таблицы для форматирования элементов таблицы

Выберите любую ячейку в таблице.
На вкладке Table Design в разделе Style Options установите или снимите отметку с любого из следующего:
- Строка заголовка — Применить или удалить форматирование из первой строки в таблице.
- Строка итогов — Быстро добавляйте в таблицу функции ПРОМЕЖУТОЧНЫЙ ИТОГ, такие как СУММ, СРЕДНЕЕ, СЧЁТ, МИН / МАКС, из раскрывающегося списка. Функции ПРОМЕЖУТОЧНЫЙ ИТОГО позволяют включать или игнорировать скрытые строки в вычислениях.
- Строки с полосами — Отображение четных и нечетных строк с чередованием затенения для облегчения чтения.
- Первый столбец — Применить или удалить форматирование первого столбца в таблице.
- Последний столбец — Применить или удалить форматирование последнего столбца в таблице.
- Чередующиеся столбцы — Отображение четных и нечетных столбцов с чередованием затенения для удобства чтения.
- Кнопка фильтра — Включение и выключение автофильтра.

Включение и отключение заголовков таблиц Excel

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

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

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

Примечания:

Снимки экрана в этой статье были сделаны в Excel 2016. Если у вас другая версия, ваше представление может немного отличаться, но, если не указано иное, функциональность остается той же.
Строку заголовка таблицы не следует путать с заголовками столбцов рабочего листа или заголовками для печатных страниц. Дополнительные сведения см. В разделе Печать строк с заголовками столбцов вверху каждой страницы.
Когда вы выключаете строку заголовка, автофильтр отключается, и все примененные фильтры удаляются из таблицы.
Когда вы добавляете новый столбец, когда заголовки таблицы не отображаются, имя нового заголовка таблицы не может быть определено путем заполнения ряда, основанного на значении заголовка таблицы, который непосредственно примыкает к левому краю нового столбца.Это работает только при отображении заголовков таблиц. Вместо этого добавляется заголовок таблицы по умолчанию, который можно изменить при отображении заголовков таблицы.
Хотя можно ссылаться на заголовки таблиц, которые отключены в формулах, вы не можете ссылаться на них, выбирая их. Ссылки в таблицах на скрытый заголовок таблицы возвращают нулевые (0) значения, но они остаются неизменными и возвращают значения заголовка таблицы, когда заголовок таблицы отображается снова.Все другие ссылки на лист (например, ссылки стиля A1 или RC) на заголовок таблицы корректируются, когда заголовок таблицы отключен, и может привести к тому, что формулы будут возвращать неожиданные результаты.

Показать или скрыть строку заголовка

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

Примечания:

Снимки экрана в этой статье были сделаны в Excel 2016. Если у вас другая версия, ваше представление может немного отличаться, но, если не указано иное, функциональность остается той же.
Строку заголовка таблицы не следует путать с заголовками столбцов рабочего листа или заголовками для печатных страниц.Дополнительные сведения см. В разделе Печать строк с заголовками столбцов вверху каждой страницы.
Когда вы выключаете строку заголовка, автофильтр отключается, и все примененные фильтры удаляются из таблицы.
Когда вы добавляете новый столбец, когда заголовки таблицы не отображаются, имя нового заголовка таблицы не может быть определено путем заполнения ряда, основанного на значении заголовка таблицы, который непосредственно примыкает к левому краю нового столбца.Это работает только при отображении заголовков таблиц. Вместо этого добавляется заголовок таблицы по умолчанию, который можно изменить при отображении заголовков таблицы.
Хотя можно ссылаться на заголовки таблиц, которые отключены в формулах, вы не можете ссылаться на них, выбирая их. Ссылки в таблицах на скрытый заголовок таблицы возвращают нулевые (0) значения, но они остаются неизменными и возвращают значения заголовка таблицы, когда заголовок таблицы отображается снова.Все другие ссылки на лист (например, ссылки стиля A1 или RC) на заголовок таблицы корректируются, когда заголовок таблицы отключен, и может привести к тому, что формулы будут возвращать неожиданные результаты.

Показать или скрыть строку заголовка

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

Показать или скрыть строку заголовка

Щелкните в любом месте таблицы.
На вкладке Home на ленте щелкните стрелку вниз рядом с Table и выберите Toggle Header Row .
-ИЛИ-
Щелкните вкладку Table Design > Style Options > Header Row .

Нужна дополнительная помощь?

См. Также

Обзор таблиц Excel

Видео: создание таблицы Excel

Создать или удалить таблицу Excel

Отформатируйте таблицу Excel

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

Фильтровать данные в диапазоне или таблице

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

Преобразование таблицы в диапазон

12 причин, почему вам следует использовать таблицы Excel

Что такое таблица Excel и как ее создать?

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

Как распознать таблицу Excel?

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

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

Это супер просто!

Выберите набор данных, перейдите на вкладку «Вставка» и нажмите «Таблица» (или просто нажмите сочетание клавиш «CTRL + T»).Появится следующее диалоговое окно:

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

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

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

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

Причина № 1: таблицы очень легко форматируются

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

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

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

Причина № 2: Заголовки таблицы остаются видимыми даже при прокрутке вниз

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

Это до тех пор, пока ваш выбор находится в таблице, но A, B , Буквы C будут отображаться как обычно, если ваш выбор отсутствует в таблице.

Причина № 3: Фильтры добавлены к вашим данным

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

Причина № 4: Таблицы автоматически расширяются при добавлении новых строк или столбцов

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

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

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

Причина № 5: Таблицы автоматически именуют диапазоны

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

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

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

Большим преимуществом этого является то, что вы можете поделиться своей книгой с кем угодно, и они быстро поймут ваши формулы.= SUM (MyTable [Number of items]) сразу сообщает вам, что вы берете сумму всего количества элементов, и это намного понятнее, чем = SUM (Sheet1! H3: h27)

Для каждого столбца следующие имена созданы:

MyTable [Country] можно использовать где угодно для обозначения всех данных в столбце «Страна» таблицы с названием «MyTable». Это не включает заголовок, только данные.
MyTable [@Country] выглядит примерно так же, но действительно отличается.Когда есть этот символ «@», имя означает в основном «в той же строке». Поэтому, если моя формула содержит MyTable [@Country], для каждой строки будет использоваться соответствующая страна (в той же строке), а не весь список стран каждый раз.
MyTable [[# All]; [Country]] будет обозначать тот же столбец, что и MyTable [Country], но с заголовком.

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

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

Причина № 6: Вы можете автоматически добавлять итоги в таблицы

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

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

Эти итоги потрясающие:

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

Полезный совет : когда вы работаете с таблицей, нажатие клавиши Tab в правой конечной ячейке столбца приведет вас к левой ячейке следующей строки, так что вы останетесь в таблице.Когда вы дойдете до последней ячейки в правом нижнем углу таблицы, нажав клавишу TAB. добавит новую строку в таблицу. Это также работает с PowerPoint и таблицами Word. Поэтому, когда у вас есть строка итогов, используйте вкладку в нижней правой ячейке, чтобы добавить новую строку, не перезаписывая строку итогов.

Причина № 7: Когда таблицы автоматически расширяются, они также расширяются … их формат

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

Причина № 8: Таблицы автоматически перетаскивают формулы вниз

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

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

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

Причина № 9: Вы можете создавать динамические диаграммы

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

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

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

Теперь мы добавляем новую строку данных с покупкой за 2018 год. Просто обновив сводную диаграмму (на вкладке «Анализ», «Обновить»), диаграмма автоматически корректируется и добавляет новый колонка за 2018 год.Круто, правда?

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

Причина № 10: Таблицы автоматически корректируют именованные диапазоны.

Каждый раз, когда вы обновляете таблицу новыми или дополнительными данными, имена изменяются таким образом, чтобы любая формула, сводная таблица или сводная диаграмма, относящиеся к столбцу, оставались актуальными. Например, имя MyTable [Country] будет автоматически изменяться с учетом любой новой строки, добавляемой в вашу таблицу.Это очень полезно, если вы использовали это имя в формулах.

Причина № 11: вы можете добавить формы для облегчения ввода новых данных

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

Формы выглядят следующим образом:

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

Есть только одна проблема: формы по умолчанию недоступны в Excel. Так как же их получить?

Вам нужно добавить кнопку Форма на ленту.

Для этого выполните следующие действия:

Перейдите в меню «Файл / Параметры» и затем «Настройте ленту»
Выберите «Все команды» в верхнем левом раскрывающемся списке.
Выберите «Все вкладки» «в правом верхнем раскрывающемся списке
На вкладке» Таблицы «создайте» Новую группу «с помощью кнопки в правом нижнем углу окна.Это место, куда будет добавлена кнопка «Форма».
В левом списке найдите «Форма»
Нажмите «Добавить», а затем подтвердите с помощью ok

Теперь вы должны увидеть кнопку «Форма» в местоположение, которое вы определили:

Причина № 12: вы можете использовать срезы и временные шкалы для фильтрации ваших данных и диаграмм.

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

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

Чтобы вставить слайсер или временную шкалу, выберите диаграмму и перейдите на вкладку «Анализ». Щелкните слайсер или временную шкалу и выберите поля, в которых вы хотите разрешить пользователю вашей книги фильтровать. Например, здесь мы хотим позволить пользователям визуализировать суммы для любой страны и за любой квартал. Поэтому мы добавляем слайсер (для стран) и временную шкалу (для даты покупки).Результат выглядит следующим образом:

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

Заключение : Если это непонятно, вам обязательно стоит использовать таблицы Excel каждый раз, когда это возможно!

Хотите стать мастером Excel? Попробуйте нашу надстройку Power-user, предлагающую множество мощных функций для PowerPoint и Excel, которые сэкономят вам огромное количество времени!

Таблицы Excel | Exceljet

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

1. Быстрое создание таблицы

Вы можете создать таблицу Excel менее чем за 10 секунд. Сначала удалите пустые строки и убедитесь, что все столбцы имеют уникальное имя, затем поместите курсор в любом месте данных и используйте сочетание клавиш Control + T. Когда вы нажмете OK, Excel создаст таблицу.

2.Переход непосредственно к таблицам

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

3. В таблицах есть специальные ярлыки

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

Видео: горячие клавиши для таблиц Excel

4. Перетаскивание безболезненно

Таблицы

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

Примечание: вы должны выбрать всю строку или столбец .Для столбцов это включает заголовок.

5. Заголовки таблиц остаются видимыми

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

6. Таблицы расширяются автоматически

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

7. Итоги без формул

Все таблицы могут отображать дополнительную строку итогов. Строку итогов можно легко настроить для выполнения таких операций, как SUM и COUNT, без ввода формулы. Когда таблица фильтруется, эти итоги будут автоматически вычисляться только для видимых строк.Вы можете включать и выключать строку итогов с помощью сочетания клавиш Ctrl + Shift + T.

8. Переименовать таблицу в любое время

Всем таблицам автоматически присваивается общее имя, например Table1, Table2 и т. Д. Однако вы можете переименовать таблицу в любое время. Выберите любую ячейку в таблице и введите новое имя в меню «Работа с таблицами».

9. Автоматическое заполнение формул

Таблицы

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

10. Автоматическое изменение формул

Эта же функция также обрабатывает изменения формул. Если вы вносите изменения в формулу в любом месте вычисляемого столбца, формула обновляется во всем столбце. На приведенном ниже экране ставка налога была изменена на 7% за один шаг.

11.Удобочитаемые формулы

В таблицах используется специальный синтаксис формул для ссылки на части таблицы по имени. Эта функция называется «структурированными ссылками». Например, чтобы СУММИРОВАТЬ столбец «Сумма» в таблице «Заказы», вы можете использовать следующую формулу:

12. Простые динамические диапазоны

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

  = СТРОКИ (Свойства)
= МАКС (Свойства)
= MIN (Свойства)

13. Введите структурированные ссылки с помощью мыши

Простой способ ввести структурированные ссылки в формулы — использовать мышь для выбора части таблицы. Excel автоматически введет структурированную ссылку за вас.На приведенном ниже экране столбец цен был выбран после ввода = MAX (

14. Введите структурированные ссылки, набрав

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

Используйте клавиши со стрелками для выбора и клавишу TAB для подтверждения. Чтобы ввести имя столбца, введите открывающую квадратную скобку ([) после имени таблицы, следуя той же процедуре — введите несколько букв, выберите с помощью клавиш со стрелками и используйте TAB для подтверждения.

Видео: Введение в структурированные ссылки и таблицы

15. Проверьте структурированные ссылки по формуле

Вы можете быстро проверить структурированную ссылку с помощью строки формул. Например, следующая формула выберет данные в столбце «Адрес» в таблице «Свойства», показанной выше:

И эта формула выберет заголовки таблицы:

Видео: Как запросить таблицу с формулами

Видео: Как использовать СУММЕСЛИМН с таблицей

16.Изменение форматирования таблицы одним щелчком мыши

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

17. Удалите все форматирование

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

Совет: вы можете использовать этот стиль, чтобы удалить все форматирование таблицы перед преобразованием таблицы обратно в нормальный диапазон.

18. Переопределить локальное форматирование

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

19. Установите стиль таблицы по умолчанию

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

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

20. Используйте таблицу со сводной таблицей

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

Видео: используйте таблицу для следующей сводной таблицы

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

Таблицы

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

Видео: Как построить простую динамическую диаграмму

22. Добавить слайсер на стол

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

В таблице ниже есть слайсер для отдела:

23. Избавьтесь от стола

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

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

Работа с таблицами Excel для чайников: пошаговая инструкция

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

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

Взаимодействие с другими людьми

Создание таблицы в Excel: манекен

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

Посмотрите внимательно на рабочий лист настольного процессора:

Это набор ячеек в столбцах и строках.По сути, это стол. Столбцы отмечены буквами. Строки обозначены цифрами. Нет границ.

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

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

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

Чтобы выбрать строку, щелкните номер, которым она обозначена.

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

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

Как изменить размер ячеек

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

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

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

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

Чтобы привести строки к их исходным границам, откройте меню инструментов: «HOME» — «Format» и выберите «AutoFit Row Height».

Этот метод не работает для столбцов. Нажмите «Формат» — «Автоподбор ширины ряда». Запомните это число. Выберите любую ячейку в столбце, размер которой должен вернуться к исходному. Снова нажмите «Формат» — «Ширина столбца» и введите значение, предложенное программой (как правило, это 8,43 — количество знаков в шрифте Calibri, размер 11 pt).ОК.

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

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

Щелкните правой кнопкой мыши по ячейке и в раскрывающемся меню выберите «Вставить» (или нажмите комбинацию горячих клавиш CTRL + SHIFT + «=»).

Выберите «Весь столбец» и нажмите ОК.

Подсказка. Чтобы быстро вставить новый столбец, выберите столбец в нужном месте и нажмите CTRL + SHIFT + PLUS.

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

Пошаговое создание таблицы с формулами

Заполните заголовок вручную, указав заголовки столбцов. Заполните строки, введя свои данные. Применяйте полученные знания на практике: расширяйте границы столбцов, регулируйте высоту строк.
Для заполнения столбца «Сумма» установите курсор в его первую ячейку.Введите «=». Таким образом мы информируем Excel: здесь будет формула. Выделите ячейку B2 (с первой ценой). Введите символ умножения (*). Выделите ячейку C2 (с количеством). Нажмите Ввод.
При наведении указателя мыши на ячейку, содержащую формулу, в ее правом нижнем углу появится крестик. Он указывает на маркер автозаполнения. Захватите его левой кнопкой мыши и перетащите в конец столбца. Формула будет скопирована в каждую ячейку.
Обозначьте границы вашего стола.Выберите диапазон, содержащий ваши данные. Нажмите кнопку «ГЛАВНАЯ» — «Граница» (на главной странице в меню «Шрифт»). И нажмите «Все границы».

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

Меню «Шрифт» позволяет форматировать данные в таблице Excel так, как если бы вы это делали в Word.

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

Взаимодействие с другими людьми

Создание таблицы в Excel: пошаговая инструкция

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

Построим умную (динамическую) таблицу:

Перейдите на вкладку «ВСТАВИТЬ» — инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL + T).
Откроется диалоговое окно, в котором необходимо ввести диапазон данных. Установите флажок для таблицы с заголовками. Щелкните ОК. Ничего страшного, если вы не войдете в правильный диапазон с первой попытки. Умный стол гибкий, динамичный.

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

Теперь введите свои данные в готовый фреймворк. Если вам нужен дополнительный столбец, поместите курсор в ячейку заголовка. Сделайте запись и нажмите ENTER. Ассортимент расширится автоматически.

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

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

С выходом новых версий программы работа с таблицами в Excel стала более интересной и динамичной.После формирования умной таблицы на электронной таблице становится доступен инструмент «ИНСТРУМЕНТЫ ТАБЛИЦЫ» — «ДИЗАЙН».

Здесь вы можете назвать таблицу или изменить ее размер.

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

Динамические электронные таблицы

MS Excel открывают огромные возможности. Начнем с базовых навыков ввода и автозаполнения данных:

Выберите ячейку, щелкнув по ней левой кнопкой мыши.Введите текстовое / числовое значение. Нажмите Ввод. Если вам нужно изменить значение, снова поместите курсор в ячейку и введите новые данные.
При повторном вводе значения Excel распознает его. Вам нужно будет всего лишь ввести несколько символов и нажать Enter.
В интеллектуальной таблице, чтобы применить формулу ко всему столбцу, введите ее в первую ячейку. Программа автоматически скопирует формулу в другие ячейки.
Для подсчета итогов выделите столбец, содержащий значения плюс пустую ячейку для будущего итога, и нажмите кнопку «Сумма» (группа инструментов «Редактирование» на вкладке «ГЛАВНАЯ», либо нажмите комбинацию горячих клавиш ALT + » знак равно

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

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

Работа с таблицами в Microsoft Excel

В этой статье мы узнаем о:

Какие столы
Создание таблиц
Изменить размер таблиц

Что такое стол?

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

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

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

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

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

Далее нажмите Вставить → Таблица:

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

Снимите выделение с ранее выбранного диапазона ячеек, и вы увидите, что этот диапазон ячеек был преобразован в таблицу:

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

Изменение размера таблицы

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

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

Щелкните и перетащите этот маркер вниз, пока не увидите зеленый контур таблицы с отсутствующей строкой (33):

Отпустите курсор, и строка, включая ее данные, будет включена в таблицу:

Повторите этот процесс, но перетащите маркер изменения размера вправо, чтобы включить столбец G:

Теперь столбец G и его существующие данные будут включены в таблицу: