Как создать excel таблицу девелопмент проекта
Таблицы в Excel представляют собой ряд строк и столбцов со связанными данными, которыми вы управляете независимо друг от друга.
Работая в Excel с таблицами, вы сможете создавать отчеты, делать расчеты, строить графики и диаграммы, сортировать и фильтровать информацию.
Если ваша работа связана с обработкой данных, то навыки работы с таблицами в Эксель помогут вам сильно сэкономить время и повысить эффективность.
Как работать в Excel с таблицами. Пошаговая инструкция
Прежде чем работать с таблицами в Эксель, последуйте рекомендациям по организации данных:
- Данные должны быть организованы в строках и столбцах, причем каждая строка должна содержать информацию об одной записи, например о заказе;
- Первая строка таблицы должна содержать короткие, уникальные заголовки;
- Каждый столбец должен содержать один тип данных, таких как числа, валюта или текст;
- Каждая строка должна содержать данные для одной записи, например, заказа. Если применимо, укажите уникальный идентификатор для каждой строки, например номер заказа;
- В таблице не должно быть пустых строк и абсолютно пустых столбцов.
1. Выделите область ячеек для создания таблицы
Выделите область ячеек, на месте которых вы хотите создать таблицу. Ячейки могут быть как пустыми, так и с информацией.
На вкладке «Вставка» нажмите кнопку «Таблица».
3. Выберите диапазон ячеек
Во всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите «ОК».
4. Таблица готова. Заполняйте данными!
Поздравляю, ваша таблица готова к заполнению! Об основных возможностях в работе с умными таблицами вы узнаете ниже.
Видео урок: как создать простую таблицу в Excel
Форматирование таблицы в Excel
Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке «Конструктор» в разделе «Стили таблиц»:
Если 7-ми стилей вам мало для выбора, тогда, нажав на кнопку, в правом нижнем углу стилей таблиц, раскроются все доступные стили. В дополнении к предустановленным системой стилям, вы можете настроить свой формат.
Помимо цветовой гаммы, в меню «Конструктора» таблиц можно настроить:
- Отображение строки заголовков — включает и отключает заголовки в таблице;
- Строку итогов — включает и отключает строку с суммой значений в колонках;
- Чередующиеся строки — подсвечивает цветом чередующиеся строки;
- Первый столбец — выделяет «жирным» текст в первом столбце с данными;
- Последний столбец — выделяет «жирным» текст в последнем столбце;
- Чередующиеся столбцы — подсвечивает цветом чередующиеся столбцы;
- Кнопка фильтра — добавляет и убирает кнопки фильтра в заголовках столбцов.
Видео урок: как задать формат таблицы
Как добавить строку или столбец в таблице Excel
Даже внутри уже созданной таблицы вы можете добавлять строки или столбцы. Для этого кликните на любой ячейке правой клавишей мыши для вызова всплывающего окна:
- Нажмите правой кнопкой мыши на любой ячейке таблицы, где вы хотите вставить строку или колонку => появится всплывающее окно:
- Выберите пункт «Вставить» и кликните левой клавишей мыши по «Столбцы таблицы слева» если хотите добавить столбец, или «Строки таблицы выше», если хотите вставить строку.
- Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта «Удалить» и выберите «Столбцы таблицы», если хотите удалить столбец или «Строки таблицы», если хотите удалить строку.
Как отсортировать таблицу в Excel
Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки «стрелочку», после чего появится всплывающее окно:
В окне выберите по какому принципу отсортировать данные: «по возрастанию», «по убыванию», «по цвету», «числовым фильтрам».
Видео урок как отсортировать таблицу
Как отфильтровать данные в таблице Excel
Для фильтрации информации в таблице нажмите справа от заголовка колонки «стрелочку», после чего появится всплывающее окно:
- «Текстовый фильтр» отображается когда среди данных колонки есть текстовые значения;
- «Фильтр по цвету» так же как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
- «Числовой фильтр» позволяет отобрать данные по параметрам: «Равно…», «Не равно…», «Больше…», «Больше или равно…», «Меньше…», «Меньше или равно…», «Между…», «Первые 10…», «Выше среднего», «Ниже среднего», а также настроить собственный фильтр.
- Во всплывающем окне, под «Поиском» отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.
Если вы хотите отменить все созданные настройки фильтрации, снова откройте всплывающее окно над нужной колонкой и нажмите «Удалить фильтр из столбца». После этого таблица вернется в исходный вид.
Как посчитать сумму в таблице Excel
Для того чтобы посчитать сумму колонки в конце таблицы, нажмите правой клавишей мыши на любой ячейке и вызовите всплывающее окно:
В списке окна выберите пункт «Таблица» => «Строка итогов»:
Внизу таблица появится промежуточный итог. Нажмите левой клавишей мыши на ячейке с суммой.
В выпадающем меню выберите принцип промежуточного итога: это может быть сумма значений колонки, «среднее», «количество», «количество чисел», «максимум», «минимум» и т.д.
Видео урок: как посчитать сумму в таблице Excel
Как в Excel закрепить шапку таблицы
Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу «вниз» сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.
Для того чтобы закрепить заголовки сделайте следующее:
- Перейдите на вкладку «Вид» в панели инструментов и выберите пункт «Закрепить области»:
- Теперь, прокручивая таблицу, вы не потеряете заголовки и сможете легко сориентироваться где какие данные находятся:
Видео урок: как закрепить шапку таблицы:
Как перевернуть таблицу в Excel
Представим, что у нас есть готовая таблица с данными продаж по менеджерам:
На таблице сверху в строках указаны фамилии продавцов, в колонках месяцы. Для того чтобы перевернуть таблицу и разместить месяцы в строках, а фамилии продавцов нужно:
- Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):
- Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать «Специальная вставка» и нажать на этом пункте левой клавишей мыши:
- В открывшемся окне в разделе «Вставить» выбрать «значения» и поставить галочку в пункте «транспонировать»:
- Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать — это преобразовать полученные данные в таблицу.
Видео урок как перевернуть таблицу:
В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!
Шаблоны управления проектами являются неотъемлемой частью тиражирования успешных проектов. С помощью бесплатных шаблонов Microsoft Excel вы можете превратить ваши простые электронные таблицы в мощные инструменты управления проектами.
В этой статье вы найдете некоторые из самых полезных и бесплатных шаблонов управления проектами Microsoft Excel и отслеживания проектов, которые вы хотите использовать для своего следующего проекта.
В этой статье:
Шаблоны управления проектами Microsoft Excel
Давайте посмотрим на лучшие шаблоны управления проектами Microsoft Excel.
Примечание. Здесь мы рассмотрим как собственные, так и сторонние шаблоны. Чтобы найти предустановленные шаблоны таблиц Excel, откройте Excel и найдите соответствующее ключевое слово на экране « Новый документ». Если вы уже в Excel, перейдите в « Файл»> «Новый», чтобы вызвать поиск по шаблону. Обратитесь к разделу « Управление шаблонами Microsoft Excel » ниже для получения более подробной информации.
Шаблоны временной шкалы проекта Excel
Excel поставляется с несколькими временными шкалами и шаблонами диаграмм Ганта, предоставленными Microsoft, но также включает в себя шаблоны из Vertex42, одного из самых популярных сторонних ресурсов для электронных таблиц.
1. Рабочий план Хронология
Шаблон временной шкалы рабочего плана подходит для базового проекта с несколькими этапами. Когда вы вводите свои данные в таблицу, дорожная карта будет обновляться автоматически.
Этот шаблон поставляется с Microsoft Excel 2016.
2. Диаграмма Ганта с отслеживанием даты
Диаграммы Ганта являются одним из основных в каждом наборе инструментов менеджера проекта. Они помогают вам визуализировать поток ваших задач и отслеживать прогресс.
С помощью этого шаблона вы можете создать полную диаграмму Ганта с минимальными усилиями. Просто введите каждое задание, вместе с описанием, кому оно назначено, процентное значение для обозначения прогресса, дату начала и количество дней до завершения.
Этот шаблон является Microsoft Excel по умолчанию.
3. Сроки и задачи проекта
Если вы хотите интегрировать вехи в базовую временную шкалу, этот шаблон, предоставленный Vertex42, идеален. Он сочетает в себе лучшие элементы диаграммы Ганта, то есть визуализацию потока задач, с вехами, зависшими над временной шкалой. Просто заполните соответствующие таблицы, чтобы заполнить визуальный.
Вы можете найти этот шаблон, выполнив поиск в Excel.
Шаблон плана проекта Excel
План проекта — это документ. которые могут требовать диаграмм Excel, но в остальном составлены в Microsoft Word. Однако для базовых проектов вам может потребоваться только документ Microsoft Excel.
4. Простая диаграмма Ганта
При поиске в хранилище шаблонов Excel шаблонов плана проекта вы в основном найдете различные варианты диаграмм Ганта, включая эту простую диаграмму Ганта из Vertex42. Что отличает его от диаграммы Ганта, приведенной выше, так это включение этапов проекта.
Этот шаблон включен в Microsoft Excel.
5. Шаблон планировщика событий
План проекта действительно не то, что вы обычно составляете в Excel. Но если ваш проект достаточно прост, например, планирование вечеринки, вам нужен твердый одностраничный шаблон, в котором перечислены основные задачи и вы можете определить график и бюджет. Этот шаблон из Office Templates Online — отличное начало.
Шаблон отслеживания проектов Excel
При поиске трекера вы увидите удивительное сочетание личных и деловых шаблонов таблиц Excel для отслеживания. Вы можете сузить область поиска, выбрав категории, связанные с задачей управления проектом, с которой вы работаете.
6. Отслеживание затрат по видам деятельности
Этот шаблон отслеживания может помочь вам получить представление о прямых, косвенных, а также общих и административных затратах на продукт.
7. Шаблон отслеживания проекта
Этот шаблон Vertex42 необходим, если вы работаете с несколькими различными клиентами, проектами и / или результатами. Он объединяет детали проекта, расходы, статусы задач и сроки выполнения.
Шаблоны бизнес планов
В Microsoft Office есть собственная категория для бизнес-планов. Воспользуйтесь предложенным бизнес-поиском и выберите категорию « Бизнес-планы » справа.
Вы найдете следующие шаблоны Microsoft Excel:
- Затраты на запуск
- Контрольный список бизнес-плана
- Контрольный список бизнес-плана с SWOT-анализом
Дополнительные шаблоны бизнес-планов шаблоны бизнес-планов , посмотрите нашу специальную статью.
Поиск онлайн-шаблонов
Не можете найти нужный шаблон управления проектом в Excel? Обратитесь к стороннему онлайн-ресурсу для более широкого выбора шаблонов таблиц Excel. Мы рекомендуем следующие сайты.
Vertex42
На этом сайте есть несколько отличных шаблонов управления проектами для Microsoft Office 2003 и выше. Сайт отмечает, что его шаблоны в основном связаны с планированием проекта. Для более сложных задач может потребоваться Microsoft Project или другое программное обеспечение для управления проектами.
На странице, посвященной управлению проектами , вы найдете список полезных материалов, включая, помимо прочего, следующее:
Каждая страница содержит краткое описание того, что делает шаблон, один или несколько шаблонов, а также дополнительные советы и рекомендации для соответствующего инструмента управления проектами. Это отличный ресурс для начинающих менеджеров проектов.
TidyForm
TidyForm имеет респектабельный выбор шаблонов управления проектами Microsoft Excel. Самые популярные категории перечислены на главной странице. Если вы не можете сразу найти то, что вам нужно, переключитесь в раздел « Бизнес » или попробуйте функцию поиска.
Когда вы прокрутите до конца раздела, вы увидите список популярных категорий и связанных категорий. Это может быть полезно при поиске нужного шаблона.
Мы рекомендуем следующие страницы:
Все еще ищете идеальный шаблон? Возможно, вам придется создавать пользовательские шаблоны Excel. чтобы получить именно то, что вы хотите.
Управление шаблонами Microsoft Excel
Сначала посмотрим, какие шаблоны вы уже установили в Microsoft Excel. Для этой демонстрации мы использовали Excel 2016, но процедура аналогична в Microsoft Office 2013 и Office 2019.
Значения по умолчанию
Когда вы запускаете Microsoft Excel, первое окно, которое вы видите, будет содержать поле поиска для онлайн-шаблонов. Когда вы начинаете с существующей книги, перейдите в Файл> Создать, чтобы перейти к тому же представлению.
Microsoft Excel поставляется с набором предустановленных шаблонов. Они перечислены под полем поиска. Вы можете закрепить избранные, нажав на соответствующий символ в правом нижнем углу списка.
Искать в Интернете больше шаблонов проектов
Самый быстрый способ найти нужный вам шаблон — это поиск. Как только вы начнете поиск, например, для термина « проект» , вы также увидите категории шаблонов, перечисленные рядом с шаблонами, которые соответствуют вашему поиску.
Сузить поиск
Отличная особенность заключается в том, что вы можете сузить область поиска, выбрав несколько категорий. Это поможет вам исключить шаблоны, которые могут соответствовать вашему ключевому слову, но не нужной категории. С другой стороны, вы можете обнаружить, что идеальный шаблон не доступен в Microsoft Excel.
Предварительный просмотр и создание шаблона
При нажатии на шаблон вы увидите предварительный просмотр с кратким описанием того, что предоставляет шаблон. Вы также можете закрепить шаблон из его предварительного просмотра; символ находится в правом верхнем углу.
Чтобы загрузить и использовать шаблон, нажмите кнопку « Создать» , чтобы открыть новую книгу Microsoft Excel с предварительно заполненным шаблоном.
Шаблон готов, установлен, иди
Пока вы заняты этим, просмотрите наш список полезных офисных шаблонов. и запаситесь шаблонами деловых писем. Шаблоны
Мы рассмотрели много советов по управлению проектами и уловок прошлого. Когда вы разберетесь с шаблонами, вы можете рассмотреть дополнительные инструменты и решения. Например, знаете ли вы, что Outlook отлично подходит для управления проектами ? Аналогично, вы можете использовать OneNote для управления проектами. И вы можете интегрировать OneNote с Outlook для управления проектами. ? Возможности безграничны.
Вы можете создать в Excel рабочую книгу, содержащую конечные результаты и требования к проекту. Если у вашего проекта не очень много конечных результатов, попробуйте создать электронную таблицу для каждого из них. Обратите внимание, что в нижней части рабочей книги (см. рис. 1) предусмотрены вкладки пяти рабочих листов с названиями конечных результатов, перечисленных выше.
Для создания нового рабочего листа щелкните правой кнопкой мыши на вкладке любого существующего рабочего листа и в появившемся контекстном меню выберите команду Insert (Вставить). В открывшемся диалоговом окне Insert (Вставка) активизируйте вкладку General (Общие), щелкните мышью на значке Worksheet (Лист), а затем на кнопке ОК. Чтобы переименовать рабочий лист, щелкните на его вкладке правой кнопкой мыши и в появившемся контекстном меню выберите команду Rename (Переименовать).
Вы можете размещать вкладки рабочих листов так, как считаете нужным. Для этого достаточно просто перетащить вкладку с помощью мыши. Excel не накладывает каких-либо ограничений на количество рабочих листов, которые вы можете создать. это могут быть любые данные, например это может быть информация об уроках массажа — единственным ограничением, пожалуй, является лишь объем оперативной памяти вашего компьютера.
Под названием конечного результата на рис. 1 предусмотрены следующие столбцы: номер требования (Number), описание требования (Requirement) и примечаний (Notes), имя лица (или группы), выдвинувшего соответствующее требование (Requestor). Последний столбец играет очень важную роль, поскольку в случае появления тех или иных вопросов относительно конкретного требования или возникновения потребности в изменении этого требования вам может понадобиться обратиться за разъяснениями к лицу (или группе), выдвинувшему соответствующее требование.
Рис. 1. Документирование требований
Кроме того, следует позаботиться о том, чтобы присваиваемые вами номера были уникальными. Например, если вы присвоите номер 001 таким двум первым требованиям конечного результата, как «Подготовить офисы и конференц-залы в здании на Grant St. для подключения к единой компьютерной сети» и «Система VoIP», то впоследствии не сможете определить, о каком именно конечном результате идет речь, если будет рассматриваться требование 001. Для присвоения номеров нашим требованиям мы использовали первые буквы каждого слова в названии соответствующего конечного результата (например, аббревиатуру ONC, что означает Office Network Connections, т.е. «Подготовить офисы и конференц-залы в здании на Grant St. для подключения к единой компьютерной сети»).
Как альтернативный вариант можно было бы пронумеровать все конечные результаты и использовать этот номер в качестве префикса для номера требования. Например, если конечному результату «Подготовить офисы и конференц-залы в здании на Grant St. для подключения к единой компьютерной сети» присвоить номер 003, тогда первому требованию в этом конечном результате можно дать номер 003-001, второму — номер 003-002 и т.д.
После того как вы введете несколько номеров требований (например, ONC-001, ONC-002 и ONC-003 (см. рис 1) в ячейках А10-А12), Excel может облегчить вам задачу повторения подобных номеров. Для начала выделите ячейки А10-А12. Теперь обратите внимание на то, что выделенный диапазон ячеек окаймлен черной рамкой, а в нижнем правом углу последней ячейки появился небольшой черный квадрат. Поместите указатель мыши над этим квадратом и убедитесь, что он принял вид знака «плюс». Щелкните мышью и перетащите указатель вниз по столбцу А. В ходе перетаскивания Excel будет автоматически вставлять требуемые номера в нижележащие ячейки.
Теперь займемся преобразованием в электронную таблицу диапазона ячеек с введенными нами данными, т.е. форматированием этого диапазона. Форматирование таблиц в Excel 2007/2010/2013 выполняется еще проще, чем в предыдущих версиях, и позволяет вам быстро добиться вполне приемлемого результата. (Обратите внимание: описанная ниже команда Format as Table (Форматировать как таблицу) немного напоминает команду Autoformat (Автоформат), которая была предусмотрена в Excel 2003.) В приведенном выше примере (см. рис. 1) мы создали строку с названием таблицы, в которой введена фраза Grant St. Move Project (строка 1). Несколькими строками ниже (строка 9) находится строка заголовков, которая содержит названия столбцов: Number (Номер), Requirement (Требование) и т.д.
Важно понимать разницу между строкой названия и строкой заголовков. Строка названия — это просто название того, что содержит соответствующий рабочий лист. Строка заголовков содержит названия каждого из столбцов, являющихся частью электронной таблицы. В данном случае строкой заголовков для нашей таблицы является строка 9.
Форматирование этой таблицы с целью придания ей более профессионального вида не должно вызывать затруднений. Прежде всего, выделите строку заголовков и всех строк данных, расположенных иод нею, как показано на рис. 2. Если вы еще не завершили процесс создания требований, можно просто выделить пустые строки и отформатировать их до того, как в них будут занесены данные.
Рис. 2. Строки, выделенные подсветкой
Затем на ленте программы Excel активизируйте вкладку Ноmе (Главная) и в группе параметров Styles (Стили) щелкните на значке Format as Table (Форматировать как таблицу). В результате ваших действий на экране появится панель с эскизами предустановленных стилей, которые специально предназначены для быстрого форматирования электронных таблиц (рис. 3).
Рис. 3. Панель с несколькими предустановленными стилями форматирования электронных таблиц
Щелкните мышью на эскизе понравившегося вам стиля форматирования (мы выбрали стиль Table Format Medium 12 (Стиль таблицы средний 12)). На экране появится небольшое диалоговое окно Format as Table (Форматирование таблицы), где будет указан адрес диапазона ячеек, к которому вы намерены применить стиль форматирования (рис. 4).
Рис. 4. Диалоговое окно Format as Table (Форматирование таблицы)
В данном примере этот диапазон начинается в ячейке А9 и заканчивается в ячейке D20 . Обратите внимание на то, что в диалоговом окне Format as Table установлен флажок My Table Has Headers (Таблица с заголовками). Это говорит Excel о том, что первая строка выделенного диапазона (в данном случае строка 9) является строкой заголовков. Одной из причин, почему об этом необходимо сообщить Excel, заключается в том. что при выполнении сортировки данных строку заголовков следует изолировать от собственно данных, чтобы сортировка не распространялась на строку заголовков. Иными словами, это необходимо для того, чтобы слово Requirement (Требование) из ячейки В9 осталось в этой же ячейке в процессе сортировки вместе со всеми перечисленными ниже требованиями.
Щелкните на кнопке ОК, чтобы применить выбранный вами стиль
Рис. 5. Отформатированная электронная таблица
форматирования к выделенному диапазону ячеек (рис. 5). Обратите внимание на то, что отформатированные строки таблицы помечены цветом, отличным от цвета остальных строк рабочего листа (см. рис. 5). Кроме того, в правой части ленты программы Excel появилась вкладка Design (Конструктор). С помощью интерфейсных элементов этой вкладки можно быстро изменить примененный вами стиль форматирования. Обратите внимание, что рядом с заголовком каждого столбца появилась кнопка раскрывающегося списка (рис. 6). Щелкните на этой кнопке, чтобы отобразить панель фильтрации и сортировки элементов таблицы, которые расположены под заголовком соответствующего столбца электронной таблицы.
Рис. 6. Кнопки раскрывающегося списка
Независимо от способа сортировки, на экране отображаются все без исключения данные электронной таблицы. Фильтрация позволяет скрыть определенные записи таблицы, что дает возможность сосредоточиться только на тех записях, которые представляют для вас интерес в данный момент. Например, данные столбца Number (Номер) (столбец А на рис. 6) можно отсортировать в прямой алфавитной последовательности (от А до Z), в обратной алфавитной последовательности (от Z до А) либо по цвету ячейки и цвету шрифта, которым набраны данные столбца. Проще говоря, в Excel можно выполнить сортировку по следующим критериям: столбец, значение, цвет ячейки, цвет шрифта и значок ячейки.
Фильтрация позволяет при необходимости скрыть те или иные строки. Если, например, вы хотите скрыть все пустые строки, снимите флажок (Blanks) (Пустые). Флажок (Blanks) по умолчанию всегда установлен, и если пустые строки включены в критерии фильтрации, то в результате сортировки они сместятся вниз, когда будет выбрана сортировка в прямой алфавитной последовательности (от А до Z), или вверх — при выборе сортировки в обратной алфавитной последовательности (от Z до А).
Если вы хотите произвести сортировку по двум столбцам — например, Number (в столбце А) и Requestor (Лицо или группа, выдвинувшая данное требование), — щелкните на кнопке раскрывающегося списка в столбце А и в появившейся панели выберите команду Sort by Color → Custom Sort (Сортировать по цвету → Пользовательская сортировка). В результате выполнении этой команды на экране появится диалоговое окно Sort (Сортировка). В группах параметров Column (Столбец), Sort On (Сортировка) и Order (Порядок) следует указать критерии сортировки данных.
Рис. 7. Пользовательская сортировка данных
В данном случае в раскрывающемся списке Sort by (Сортировать по) группы Column (Столбец) перечислены заголовки всех столбцов электронной таблицы (рис. 7). Выберите из этого раскрывающегося списка элемент Number. Теперь помимо данных столбца Number необходимо добавить в критерий сортировки данные столбца Requestor. Для этого щелкните мышью на кнопке Add (Добавить уровень), которая находится в верхнем левом углу диалогового окна Sort (Сортировка). Затем из раскрывающегося списка Then by (Затем по) выберите элемент Requestor. Если считаете нужным, укажите другие критерии сортировки в группах параметров Sort On (Сортировать но) и Order (Порядок сортировки) (рис. 8).
Рис. 8. Сортировку данных можно осуществлять сразу по нескольким столбцам электронной таблицы
Таблицы полезны в любой ситуации, но особенно в тех случаях, когда вам требуется быстро отсортировать данные, а процесс создания таблиц в Excel отличается чрезвычайной простотой. Несколько шаблонов, о которых пойдет речь в наших статьях, также можно отформатировать приведенным выше способом. Теперь, когда мы зафиксировали наши требования, можно приступать к рассмотрению остальных элементов формулировки масштаба проекта.
Сводная таблица — это один из наиболее полезных инструментов в Excel. С ее помощью появляются широкие возможности для анализа больших массивов данных и быстрых вычислений.
Видеоурок: Как создать сводную таблицу в Excel
Что такое сводные таблицы в Excel? Пошаговая инструкция
Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.
Представим, что у нас есть таблица с данными продаж по клиентам за год размером в 1000 строчек:
Она содержит данные:
- Даты заказов;
- Регион в котором расположен клиент;
- Тип клиента;
- Клиент;
- Количество продаж;
- Выручка;
- Прибыль.
Теперь, представим, что наш руководитель поставил задачу вычислить:
- Какой объем выручки у региона Север за 2017 год?;
- ТОП пять клиентов по выручке;
- Какое место по выручке занимает клиент Лудников ИП в регионе Восток?
Для поиска ответа на эти вопросы вы можете использовать различные функции и формулы. Но что, если задач по этим данным будет не три, а тридцать? Каждый раз вам придется менять формулы и функции и подстраивать под каждый тип расчета.
Ниже мы разберем, как в решении этих задач нам поможет сводная таблица.
Как сделать сводную таблицу в Excel
Для создания таблицы выполните следующие действия:
- Выделите любую ячейку в таблице с данными;
- Нажмите на вкладку «Вставка» => «Сводная таблица»:
- Во всплывающем диалоговом окне система автоматически определит границы данных, на основе которых вы сможете создать сводную таблицу. Рекомендую при каждом создании убеждаться в том, что система правильно определила границы диапазона данных:
- Таблица или диапазон: Система автоматически определяет границы данных. Они будут корректными при том условии, что в таблице нет пробелов в заголовках и строках. При необходимости вы можете скорректировать диапазон данных.
После нажатия кнопки «ОК» таблица будет создана.
После формирования таблицы, вы не увидите на листе никаких данных. Все что будет доступно, это ее имя и меню для выбора данных к отображению.
Теперь, прежде чем мы приступи к анализу данных, предлагаю разобраться что значит каждое поле и область сводной таблицы.
Области сводной таблицы в Excel
Для эффективной работы со сводными таблицами, важно знать принцип их работы.
Ниже вы узнаете подробней об областях:
- Кэш
- Область «Значения»
- Область «Строки»
- Область «Столбцы»
- Область «Фильтры»
Что такое кэш сводной таблицы
При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.
Когда вы осуществляете вычисления, Excel не обращается каждый раз к исходным данным, а использует информацию из кэша. Эта особенность значительно сокращает количество ресурсов системы, затрачиваемых на обработку и вычисления данных.
Кэш данных увеличивает размер Excel-файла.
Область «Значения»
Область «Значения» включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области «Значения».
На примере выше создана таблица, в которой отражены данные продаж по регионам с разбивкой по месяцам.
Область «Строки»
Заголовки таблицы, размещенные слева от значений, называются строками. В нашем примере это названия регионов. На скриншоте ниже, строки выделены красным цветом:
Область»Столбцы»
Заголовки вверху значений таблицы называются «Столбцы».
На примере ниже красным выделены поля «Столбцы», в нашем случае это значения месяцев.
Область «Фильтры»
Область «Фильтры» используется опционально и позволяет задать уровень детализации данных. Например, мы можем в качестве фильтра указать данные «Тип клиента» — «Продуктовый магазин» и Excel отобразит данные в таблице касающиеся только продуктовых магазинов.
Сводные таблицы в Excel. Примеры
На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:
- Какой объем выручки у региона Север за 2017 год?;
- ТОП пять клиентов по выручке;
- Какое место по выручке занимает клиент Лудников ИП в регионе Восток?
Прежде чем анализировать данные, важно решить каким образом должны выглядеть данные таблицы (какие данные разметить в колонки, строки, значения, фильтры). Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле «Значения». Как только вы представили каким образом вы видите итоговую таблицу — начинайте её создание.
В окне «Поля сводной таблицы» размещены области и поля со значениями для размещения:
Поля создаются на основе значений исходного диапазона данных. Раздел «Области» — это место, где вы размещаете элементы таблицы.
Перенос полей из области в область представляет собой удобный интерфейс, в котором, при перемещении, данные автоматически обновляются.
Теперь, попробуем ответить на вопросы руководителя из начала этой статьи на примерах ниже.
Пример 1. Какой объем выручки у региона Север?
Для вычисления объема продаж региона Север, рекомендую разместить в таблице данные продаж по всем регионам. Для этого нам потребуется:
- создать сводную таблицу и поле «Регион» перенести в область «Строки»;
- поле «Выручка» разместить в области «Значения»
- задать финансовый числовой формат ячейкам со значениями.
Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:
Пример 2. ТОП пять клиентов по продажам
Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:
- переместить поле «Клиент» в область «Строки»;
- поле «Выручка» разместить в области «Значения»;
- задать финансовый числовой формат ячейкам со значениями.
У нас получится следующая таблица:
По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:
- кликните правой кнопкой на любой из строчек с данными выручки;
- перейдите в меню «Сортировка» => «Сортировка по убыванию»:
Как результат мы получим отсортированный список клиентов по объему выручки.
Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?
Для расчета места по объему выручки клиента Лудников ИП в регионе Восток рекомендую сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.
- поместим поле «Регион» в область «Строки»;
- поместим поле «Клиент» в область «Строки» под поле «Регион»;
- зададим финансовый числовой формат ячейкам со значениями.
После перемещения элемента «Регион» и «Клиент» в области «Строки» друг под другом , система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.
В итоге мы получили таблицу, в которой отражены данные выручки клиентов в рамках каждого региона.
Для сортировки данных выполните следующие шаги:
- кликните правой кнопкой на любой из строчек с данными выручки;
- перейдите в меню «Сортировка» => «Сортировка по убыванию»:
В полученной таблице мы можем определить какое место занимает клиент Лудников ИП среди всех клиентов региона Восток.
Существует несколько вариантов для решения этой задачи. Вы можете перенести поле «Регион» в область «Фильтры» и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.
Еще больше полезных приемов в работе со сводными таблицами Excel вы узнаете в практическом курсе « Сводные таблицы в Excel«. Успей зарегистрироваться по ссылке!
Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизиро.
Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизировать этот процесс, ответит наша статья.
Советы по структурированию информации
Перед тем, как создать таблицу в Excel, предлагаем изучить несколько общих правил:
- Сведения организуются по колонкам и рядам. Каждая строка отводится под одну запись.
- Первый ряд отводится под так называемую «шапку», где прописываются заголовки столбцов.
- Нужно придерживаться правила: один столбец – один формат данных (числовой, денежный, текстовый и т.д.).
- В таблице должен содержаться идентификатор записи, т.е. пользователь отводит один столбец под нумерацию строк.
- Структурированные записи не должны содержать пустых колонок и рядов. Допускаются нулевые значения.
Как создать таблицу в Excel вручную
Для организации рабочего процесса пользователь должен знать, как создать таблицу в Экселе. Существуют 2 метода: ручной и автоматический. Пошаговая инструкция, как нарисовать таблицу в Excel вручную:
II способ заключается в ручном рисовании сетки таблицы. В этом случае:
- Выбрать инструмент «Сетка по границе рисунка» при нажатии на пиктограмму «Границы».
- При зажатой левой кнопке мыши (ЛКМ) перетащить указатель по обозначенным линиям, в результате чего появляется сетка. Таблица создается, пока нажата ЛКМ.
Как создать таблицу в Excel автоматически
Опытные пользователи рекомендуют прибегнуть к изучению вопроса, как создать таблицу в Excel автоматически. С использованием встроенного инструментария процесс создания табличной формы происходит в разы быстрее.
Область таблицы
Перед тем, как составить таблицу в Excel, пользователю нужно определить, какой интервал ячеек ему понадобится:
- Выделить требуемый диапазон.
- В MS Excel 2013-2019 на вкладке «Главная» кликнуть на пиктограмму «Форматировать как таблицу».
- При раскрытии выпадающего меню выбрать понравившийся стиль.
Кнопка «Таблица» на панели быстрого доступа
На панели инструментов находится пиктограмма для создания табличного объекта. Чтобы воспользоваться функционалом табличного процессора, пользователь использует следующий алгоритм:
- Активировать интервал ячеек, необходимых для работы.
- Перейти в меню «Вставка».
- Найти пиктограмму «Таблицы»:
- В MS Excel 2007 кликнуть на пиктограмму. В появившемся диалоговом окне отметить или убрать переключатель пункта «Таблица с заголовками». Нажать ОК.
- В MS Excel 2016 нажать пиктограмму и выбрать пункт «Таблица». Указать диапазон ячеек через выделение мышкой или ручное прописывание адресов ячеек. Нажать ОК.
Примечание: для создания объекта используют сочетание клавиш CTRL + T.
4. Для изменения названия столбца перейти на строку формул или дважды кликнуть на объекте с заголовком.
Диапазон ячеек
Работа с числовой информацией подразумевает применение функций, в которых указывается интервал (диапазон ячеек). Под диапазоном справочная литература определяет множество клеток электронной таблицы, в совокупности образующих единый прямоугольник (А1:С9).
Активированная вкладка «Конструктор» раскрывает инструментарий, облегчающий процесс редактирования объекта. Для автоматизации работы пользователь устанавливает переключатели на инструментах «Строка заголовка» и «Строка итогов». Последняя опция позволяет провести расчеты по выбранной формуле. Для этого пользователь раскрывает список посредством нажатия на стрелку в итоговой ячейке и выбирает функцию.
Заполнение данными
Работа со структурированной информацией возможна, если ячейки заполнены текстовой, численной и иной информацией.
- Для заполнения необходимо активировать ячейку и начать вписывать информацию.
- Для редактирования ячейки дважды кликнуть на ней или активировать редактируемую ячейку и нажать F2.
- При раскрытии стрелок в строке заголовка структурированной информации MS Excel можно отфильтровать имеющуюся информацию.
- При выборе стиля форматирования объекта MS Excel автоматически выбрать опцию черезстрочного выделения.
- Вкладка «Конструктор» (блок «Свойства») позволяет изменить имя таблицы.
- Для увеличения диапазона рядов и колонок с последующим наполнением информацией: активировать кнопку «Изменить размер таблицы» на вкладке «Конструктор», новые ячейки автоматически приобретают заданный формат объекта, или выделить последнюю ячейку таблицы со значением перед итоговой строкой и протягивает ее вниз. Итоговая строка останется неизменной. Расчет проводится по мере заполнения объекта.
- В заголовках должны отсутствовать числовые форматы, поскольку при создании таблицы они преобразуются в текст. Если формула содержит в качестве аргумента ссылку на ячейку заголовка, где предполагалось число, то функция может не сработать.
Сводная таблица
Сводка используется для обобщения информации и проведения анализа, не вызывает трудностей при создании и оформлении. Для создания сводной таблицы:
- Структурировать объект и указать сведения.
- Перейти в меню «Вставка» и выбрать пиктограмму: в MS Excel 2007 – «Сводная таблица»; в MS Excel 2013-2019 – «Таблицы – Сводная таблица».
- При появлении окна «Создание сводной таблицы» активировать строку ввода диапазона, устанавливая курсор.
- Выбрать диапазон и нажать ОК.
Примечание: Если сводка должна находиться после создания на этом же листе, пользователь устанавливает переключатель на нужную опцию.
5. При появлении боковой панели для настройки объекта перенести категории в нужные области или включить переключатели («галочки»).
Созданная сводка автоматически подсчитывает итоги по каждому столбцу.
Рекомендуемые сводные таблицы
Поздние версии MS Excel предлагают воспользоваться опцией «Рекомендуемые сводные таблицы». Подобная вариация анализа информации применяется в случаях невозможности правильного подбора полей для строк и столбцов.
Для применения рекомендуемых сводных таблиц:
- Выделить ячейки с введенной информацией.
- При клике на пиктограмму «Таблицы» выбрать пункт «Рекомендуемые сводные таблицы».
- Табличный процессор автоматически анализирует информацию и предлагает оптимальные варианты решения задачи.
- В случае выбора подходящего пункта таблицы и подтверждения через ОК получить сводную таблицу.
Готовые шаблоны в Excel 2016
Табличный процессор MS Excel 2016 при запуске предлагает выбрать оптимальный шаблон для создания таблицы. В офисном пакете представлено ограниченное количество шаблонов. В Интернете пользователь может скачать дополнительные образцы.
Чтобы воспользоваться шаблонами:
Оформление
Экстерьер объекта – важный параметр. Поэтому пользователь изучает не только, как построить таблицу в Excel, но и как акцентировать внимание на конкретном элементе.
Создание заголовка
Дана таблица, нарисованная посредством инструмента «Границы». Для создания заголовка:
Выделить первую строку, кликнув ЛКМ по численному обозначению строки.
На вкладке «Главная» найти инструмент «Вставить».
Активировать пункт «Вставить строки на лист».
После появления пустой строки выделить интервал клеток по ширине таблицы.
Нажать на пиктограмму «Объединить» и выбрать первый пункт.
Задать название в ячейке.
Изменение высоты строки
Обычно высота строки заголовка больше первоначально заданной. Корректировка высоты строки:
- Нажать правой кнопкой мыши (ПКМ) по численному обозначению строки и активировать «Высота строки». В появившемся окне указать величину строки заголовка и нажать ОК.
- Или перевести курсор на границу между первыми двумя строками. При зажатой ЛКМ оттянуть нижнюю границу ряда вниз до определенного уровня.
Выравнивание текста
Если пользователь предполагает расположение текста в ячейке иное, нежели по умолчанию, то пользуется пиктограммами «Выравнивание» относительно горизонтали и вертикали, а также кнопкой «Ориентация». Выбор пунктов выпадающего списка позволит расположить текст по вертикали или диагонали.
Изменение стиля
Изменение размера шрифта, начертания и стиля написания осуществляется вручную. Для этого пользователь пользуется инструментами блока «Шрифт» на вкладке «Главная» или вызывает диалоговое окно «Формат ячеек» через ПКМ.
Пользователь может воспользоваться пиктограммой «Стили». Для этого выбирает диапазон ячеек и применяет понравившийся стиль.
Как вставить новую строку или столбец
Для добавления строк, столбцов и ячеек:
Удаление элементов
Для удаления объектов на листе MS Excel пользователь активирует аналогичную кнопку на панели инструментов, предварительно выделив строку, столбец, ячейку.
Заливка ячеек
Для задания фона ячейки, строки или столбца:
Формат элементов
На панели инструментов находится пиктограмма «Формат». Опция помогает задать размер ячеек, видимость, упорядочить листы и защитить лист.
Формат содержимого
Последний пункт из выпадающего списка «Формат» на панели быстрого доступа позволяет назначить тип данных или числовые форматы, задать параметры внешнего вида и границы объекта, установить фон и защитить лист.
Использование формул в таблицах
Табличный процессор пользуется успехом благодаря возможности применения математических, статистических, логических и т.п. функций.
Ознакомиться с полным списком и вписываемыми аргументами пользователь может, нажав на ссылку «Справка по этой функции».
Для задания формулы:
- активировать ячейку, где будет рассчитываться формула;
- открыть «Мастер формул»;
- написать формулу самостоятельно в строке формул и нажимает Enter;
На панели инструментов находится пиктограмма «Автосумма», которая автоматически подсчитывает сумму столбца. Чтобы воспользоваться инструментом:
Использование графики
Для вставки изображения в ячейку:
- Выделить конкретную ячейку.
- Перейти в меню «Вставка – Иллюстрации – Рисунки» или «Вставка – Рисунок».
- Указать путь к изображению.
- Подтвердить выбор через нажатие на «Вставить».
Инструментарий MS Excel поможет пользователю создать и отформатировать таблицу вручную и автоматически.
Читайте также: