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

46837 21.03.2019 Скачать пример

Постановка задачи

Допустим, что у нас есть книга с большим количеством листов, где на каждом листе находится таблица с данными по сделкам в этом городе:

Давайте будем исходить из следующих соображений:

• Структура и столбцов на всех листах одинаковая.
• Количество строк на всех листах разное.
• Листы могут в будущем добавляться или удаляться.

Наша задача — собрать все данные со всех листов в одну таблицу, чтобы потом с ней работать (фильтровать, сортировать, построить сводную и т.д.) Сделать это можно разными способами, но самыми удобными будут, пожалуй, Power Query и макросы.

Способ 1. Сборка данных с листов с помощью Power Query

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

Шаг 1. Подключаемся к файлу

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

Если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку, то откройте вкладку Power Query, если у вас Excel 2016 или новее, то вкладку Данные (Data). Нажмите кнопку Получить данные / Создать запрос — Из файла — Книга Excel (Get Data / New Query — From file — From Excel) и укажите наш файл с исходными листами:

В появившемся окне Навигатора (Navigator) выберите слева любой лист и нажмите в правом нижнем углу кнопку Преобразовать данные (Transform Data) или Изменить (Edit):

Должно появиться окно редактора запросов Power Query, где отобразятся данные с выбранного листа. Поскольку нам нужен, на самом деле, не один лист, а все, то удалим в правой панели все шаги, кроме первого шага Источник (Source) используя крестик слева от названия шага:

То, что останется после удаления шагов — это список всех объектов, которые Power Query «видит» во внешних файлах, а это:

• листы (Sheet)
• «умные таблицы» (Table)
• именованные диапазоны (Defined Name)
• области печати (Print Area), которые, по сути, являются одним из видов именованного диапазона

Шаг 2. Отбираем нужные листы

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

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

Во-первых, легко можно отфильтровать нужные объекты по типу по столбцу Kind.

Например, если вам нужны только листы:

Во-вторых, если нам нужны только видимые листы, то дополнительно можно отфильтровать ещё по столбцу Hidden.

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

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

В-четвёртых, можно извлечь с каждого листа содержимое любой ячейки (например, А1) и использовать его для отбора. Например, если там нет слова «Товар«, то это не наш лист. Для извлечения нужно будет также добавить вычисляемый столбец с такой конструкцией:

=[Data][Column1]{0}

Здесь:

• [Data] — имя столбца, где в каждой ячейке лежат таблицы с содержимым каждого листа (убийственная формулировка для рядового пользователя Excel, да, я знаю)
• [Column1] — имя столбца на листе, из которого мы хотим извлечь данные
• {0} — номер строки (считая с нуля), откуда мы хотим взять данные

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

Шаг 3. Разворачиваем таблицы

Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data, отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix):

После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:

Останется лишь «навести блеск», а именно:

1. Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home).
2. Переименовать первый столбец в Город двойным щелчком на заголовку.
3. Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар.

Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home — Close & Load):

В будущем, при любых изменениях в исходном файле достаточно будет лишь обновить наш запрос, щелкнув по собранной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl+Alt+F5.

Плюсы такого подхода:

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

Минусы этого способа:

• Собираются только значения, т.е. формулы с исходных листов не сохраняются.
• Названия столбцов должны на всех листах совпадать с точностью до регистра.
• Нельзя выбрать какой именно диапазон берётся с каждого листа — это определяется автоматически (берётся всё, что есть).
• Для обновления нужен Excel 2016 или новее или установленная надстройка Power Query.

Способ 2. Сборка данных с листов макросом на VBA

Похожего результата можно добиться и с помощью более «классического» подохода — макросом на VBA. Для этого на вкладке Разработчик (Developer) нажмите кнпоку Visual Basic или воспользуйтесь сочетанием клавиш Alt+F11. В открывшемся окне добавьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

Sub CollectDataFromAllSheets()
Dim ws As Worksheet

Set wbCurrent = ActiveWorkbook
Workbooks.Add
Set wbReport = ActiveWorkbook

'копируем на итоговый лист шапку таблицы из первого листа
wbCurrent.Worksheets(1).Range(«A1:D1»).Copy Destination:=wbReport.Worksheets(1).Range(«A1»)

'проходим в цикле по всем листам исходного файла
For Each ws In wbCurrent.Worksheets

'определяем номер последней строки на текущем листе и на листе сборки
n = wbReport.Worksheets(1).Range(«A1»).CurrentRegion.Rows.Count

'задаем исходный диапазон, который надо скопировать с каждого листа — любой вариант на выбор:
Set rngData = ws.Range(«A1:D5») 'фиксированный диапазон или
Set rngData = ws.UsedRange 'всё, что есть на листе или
Set rngData = ws.Range(«F5»).CurrentRegion 'область, начиная от ячейки F5 или
Set rngData = ws.Range(«A2», ws.Range(«A2»).SpecialCells(xlCellTypeLastCell)) 'от А2 и до конца листа

'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки
rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1)

Next ws
End Sub

Запустить созданный макрос можно на вкладке Разработчик кнопкой Макросы (Developer — Macros) или с помощью сочетания клавиш Alt+F8. Макрос автоматически создаст новую книгу и скопирует туда нужные вам данные.

Плюсы такого подхода:

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

Минусы этого способа:

• Последовательность столбцов на всех листах должна быть одинаковой, т.к. происходит, по сути, тупое копирование таблиц друг-под-друга.
• Защита от макросов должна быть отключена.
• Быстрого обновления, как это было с Power Query, здесь, к сожалению, не будет. При изменении исходных данных придётся запустить макрос повторно.

Способ 3. Готовый макрос из надстройки PLEX

Если лень возиться с макросами или Power Query, то можно пойти по пути наименьшего сопротивления — использовать готовый макрос (кнопка Собрать) из моей надстройки PLEX для Excel. Это, может, и не спортивно, но зато эффективно:

В общем, выбирайте любой удобный вам вариант и действуйте. Выбор — это всегда хорошо.

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/3/7453/

Программа Excel — основы работы с листами в Exel 2010

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

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

Работа с листами

Когда вы открываете новую книгу Excel, в ней по умолчанию есть три листа. По умолчанию же, они называются Лист 1, Лист 2 и Лист 3. Чтобы систематизировать информацию в книге и облегчить навигацию, вы можете переименовать и даже изменить цвет ярлыка листов. Также вы можете добавить, удалить, передвинуть и скопировать листы.

Чтобы переименовать лист:

1. Кликните правой кнопкой мыши по ярлыку листа, который хотите переименовать. Появится контекстное меню.
2. Выберите Переименовать.
   
3. Текст на ярлычке станет выделен черным. Введите новое имя листа.
   
4. Кликните вне ярлычка. Лист переименован.
   

Чтобы создать новый лист:

Кликните по иконке Вставить лист. Появится новый лист.

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

Чтобы удалить лист:

Листы могут быть удалены из книги, даже если они содержат данные.

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

Чтобы скопировать лист:

1. Кликните правой кнопкой мыши по ярлыку листа, который хотите скопировать. Появится контекстное меню.
2. Выберите Переместить/скопировать.
   
3. Появится диалоговое окно Переместить или скопировать. Отметьте поле Создать копию.
   
4. Нажмите OK. Ваш лист скопирован. Он будет называться, как оригинальный лист, но с номером версии, например «Январь (2)».
   

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

Чтобы переместить лист:

1. Кликните по листу, который хотите переместить. Курсор мыши примет другой вид.
2. Перетаскивайте иконку листа до тех пор, пока черная стрелка не появится в нужном вам месте.
   
3. Отпустите кнопку мыши, лист будет перемещен.
   

Чтобы перекрасить ярлык листа:

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

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

Группировка и разгруппировка листов

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

Чтобы сгруппировать листы:

1. Выделите первый лист, который нужно включить в группу.
2. Нажмите и удерживайте клавишу Ctrl.
3. Кликните по следующему листу, который нужно включить в группу. Продолжайте кликать по нужным листам.
4. Отпустите клавишу Ctrl. Теперь листы сгруппированы.

   Ярлычки сгруппированных листов белые.

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

Вы сможете перегруппировать их.

Чтобы разгруппировать все листы:

1. Кликните правой кнопкой по ярлычкам листов. Появится контекстное меню.
2. Выберите Разгруппировать. Листы будут разгруппированы.

Закрепление областей листа

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

  Как открыть файлы xml, xls и xlsx

Чтобы закрепить строки:

1. Выделите строку под строками, которые хотите закрепить. Например, если нужно закрепить 1 и 2 строки, то выделите 3.
2. Кликните по вкладке Вид.
3. Кликните по команде Закрепить области.

   Появится выпадающее меню.

4. Выберите Закрепить области.
5. Под закрепленными строками появится черная линия. Прокрутите лист вниз, и вы увидите, что закрепленные строки остаются на месте.

Столбцы закрепляются аналогично строкам

Чтобы закрепить столбцы:

1. Выделите столбец справа от тех, которые нужно закрепить. Например, если вы хотите закрепить столбцы A и B, то нужно выделить столбец C.
2. Кликните по вкладке Вид.

3. Кликните по команде Закрепить области. Появится выпадающее меню.
4. Выберите Закрепить области.
5. Справа от закрепленных столбцов появится черная линия.

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

Чтобы открепить области:

1. Кликните по вкладке Вид.
2. Кликните по команде Закрепить области. Появится выпадающее меню.
3. Выберите Снять закрепление областей. Закрепление с областей будет снято, а черная линия исчезнет.

Практикуйте!

1. Откройте существующую Excel 2010 книгу.
2. Создайте новый лист.
3. Измените название листа.
4. Удалите лист.
5. Переместите лист.
6. Скопируйте лист.
7. Попробуйте группировать и разгруппировать несколько листов.
8. Попробуйте закрепить и открепить столбцы и строки.

Источник: http://composs.ru/programma-excel-osnovy-raboty-s-listami-v-excel-2010/

Как в Экселе добавить лист и другие важные действия с листами

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

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

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

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

Аналогично рабочим окнам, только один рабочий лист Эксель может быть активен для работы. Активировать рабочий лист можно так:

• Мышью выбрать вкладку нужного вам рабочего листа. После клика по его ярлыку, лист будет активирован
  Вкладки рабочих листов
• Использовать горячие клавиши Ctrl+PgUp и Ctrl+PgDn для циклического пролистывания вкладок

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

Чтобы увидеть ярлыки, которые не влезли – используйте стрелки прокрутки ярлыков (см. рис.). Еще один способ —  уменьшить длину горизонтальной полосы прокрутки, потянув её границу вправо.

Таким, образом, освободится больше места для отображения вкладок.

Стрелки прокрутки ярлыков

Добавление нового рабочего листа

Часто нужно создать новый лист. Это очень просто делается, и, как всегда, есть несколько способов:

• Кликнуть на ярлыке Новый лист справа от ярлыка последнего рабочего листа. Листик будет добавлен в конец списка
• Кликнуть на вкладке листа правой кнопкой мыши и в контекстном меню выбрать команду Вставить. Новый лист появится перед тем, на который вы кликнули
  Вставка нового листа
• Использовать комбинацию клавиш Shift+F11 Лист будет добавлен перед активной вкладкой

Переименование рабочего листа Эксель

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

Так же, можно нажать правой кнопкой мыши на вкладке и выбрать команду Переименовать. Эффект будет такой же, как и от предыдущего способа.

Переименование листа Эксель

Как скопировать или переместить лист

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

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

Если в документе уже есть лист с таким именем, как у перемещаемого – Excel переименует последний. Например, «Книга1» станет «Книга1(2)».

Скрыть или удалить рабочий лист MS Excel

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

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

Скрыть лист Эксель

Чтобы отобразить скрытый лист – кликните правой кнопкой мыши на любой вкладке и выберите Показать. В появившемся диалоговом окне выберите нужный лист и нажмите Ок. Ярлык листа снова появится в списке.

Показать лист

Если лист вам не нужен и не понадобится в будущем – его лучше удалить. Для этого жмите правой кнопкой мыши на ярлык листа.  В контекстном меню выбирайте команду Удалить. Осторожно, удаление листа нельзя отменить, есть риск случайно удалить нужные данные. Формулы, ссылающиеся на удалённые листы, придётся исправлять вручную.

Действия над несколькими листами

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

1. Кликнем на одном из нужных листов для его активации
2. Зажмем Ctrl и кликаем по остальным листам, которые будут обрабатываться в группе. В адресной строке к названию файла добавится текст [Группа]
3. Выполняем те операции, которые будут общими для всех выделенных листов
4. Когда все работы выполнены — снимем группировку. Для этого нужно кликнуть правой кнопкой мыши на ярлыке любого выделенного листа и в контекстном меню выбрать Разгруппировать.

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

Вот такие операции вы можете совершать над объектом «Рабочий лист».

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

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

Источник: https://officelegko.com/2015/11/12/upravlenie-rabochimi-listami-ms-excel/

Как работать в Excel 2007: создание файла, листы

Программа Microsoft Excel 2007 — одна из двух самых популярных программ пакета Microsoft Office. Самая популярная, конечно, Microsoft Word. Совсем недавно самой последней версией программы был Excel 2003, но теперь появился и 2007, и 2010-й Excel. Рассмотрим, как работать в Excel 2007. Эта программа предназначена для составления таблиц, представления рядов чисел в наглядном виде с помощью диаграмм и графиков. Также программа Microsoft Excel применяется для проведения расчетов и выполнения математических задач самой разной сложности: от упрощения рутинных математических операций до выполнения сложнейших вычислений. Самое главное: все это можно сделать достаточно просто, и при этом особых познаний не требуется.

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

Создаем файл Excel

Нажимаете на кнопку Пуск в левом нижнем углу компьютера, выбираете Все программы, находите Microsoft Office (если он у Вас установлен), нажимаете на него, выбираете Microsoft Office Excel, и запускаете двойным кликом.

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

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

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

Структура файла Excel. Листы

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

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

и выбираете окошке нужную операцию, либо в меню Главная выбираете пункты: Вставить — Вставить лист, Удалить — Удалить лист, Формат — Переименовать лист, Формат — Переместить или скопировать лист.

Переставлять листы местами можно также с помощью перетаскивания закладки листа вправо или влево.

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

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

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

    Подписаться на блог: Дорога к Бизнесу за Компьютером

Источник: http://pro444.ru/azbuka/kak-rabotat-v-excel-2007-sozdanie-fajla-listy.html

Группировка листов в Excel — Microsoft Excel для начинающих

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

В Excel Вы можете работать как с каждым листом по отдельности, так и с несколькими одновременно. Листы могут быть объединены в группы. Любые изменения, внесенные на один лист, применяются ко всей группе.

Как сгруппировать листы в Excel

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

1. Выберите первый лист, который Вы хотите включить в группу.
2. Нажмите и удерживайте клавишу Ctrl на клавиатуре.
3. Выберите следующий лист, затем следующий и т.д. Продолжайте выбирать, пока все необходимые листы не будут включены в группу.
4. Отпустите клавишу Ctrl. Листы будут сгруппированы.

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

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

Как разгруппировать все листы в Excel

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

Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/gruppirovka-listov-v-excel-26.html

Excel. Урок 11. Работа с несколькими листами: однотипные данные, формулы

Скачать файл

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

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

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

1:20 Добавление листа

В рабочей книге три рабочих листа. Добавим новый лист для итога. Можно сделать это нажатием на ярлычок вставки листа (рядом с ярлычками существующих листов).

1:52 Переименование листа

Двойной щелчок по имени листа. Имя листа выделилось, можно вводить новое название. (Другой способ — правый щелчок по имени листа — команда «Переименовать«). Называем листы:

«Итог», «Магазин 3», «Магазин 2», «Магазин 1»

2:50 Перемещение листов

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

«Магазин 1», «Магазин 2», «Магазин 3», «Итог»

3:18 Приемы работы с листами

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

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

4:16 Работа со всеми листами одновременно

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

Выделяем все листы — щелкаем правой кнопкой и даем команду «Выделить все листы». Все ярлычки листов стали светлыми, и активны все 4 листа.

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

• В А1 пишем «Магазин № 1».
• В B1 пишем: «Данные по кварталам».
• 5:23 

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

1. Вводим боковые заголовки в столбце 1:
2. 
3. 
   6:28  Улучшаем внешний вид

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

• 7:11 Добавляем заливку ячеек, чтобы таблица лучше воспринималась.
• 7:35 Очерчиваем границы
• 7:50 

Сейчас все таблицы заполнены одинаковыми данными, и необходимо подредактировать каждый лист. Перемещаемся в область ярлычков. Нажимаем правую кнопку и выбираем команду «Разгруппировать листы«. Ярлычки приняли прежний цвет. Перемещаясь между листами, редактируем каждый из них. В заголовочных строках меняем номер магазина и в итоговом листе «Магазин № 1» заменяем на  «Итог».

8:45 Можно было бы группировать листы несколько иным способом. Выделяем первый лист зажимаем  клавишу Ctrl, щелкаем  на второй лист и третий лист. В данном случае я выделяю не все листы а лишь часть из них (лист «Итог» пока не выделяем).

9:20 в выделенных листах вводим формулы. Здесь пока нет исходных данных но как только появятся числа,  появится результат. В В8 выполняем автосуммирование.

Подстановка данных в автосумму  не сработала, потому что нет чисел, и поэтому получилась пустая скобка. Указываем с помощью выделения с нажатой кнопкой диапазон В5:В6.

1. Выполняю автозаполнение вправо до столбца Е включительно.
2. Прибыль это выручка от продаж минус суммарные затраты.
3. = B4-B8
4. 
5. Заполняем вправо.
6. 10:32
7. Определим эти данные как финансовые (выделим и нажмем на кнопку «Финансовый числовой формат» на ленте) Теперь листы можно разгруппировать и вводить данные и сразу будем получать результат вычисления по формулам.
8. 11:00
9. Данные введены по всем магазинам введены числовые данные и как только они появились сразу же в ячейках, где записаны формулы, появились результаты.
10. 11:20 Переходим на лист «Итог» и подсчитаем сумму по 3-м магазинам по каждому кварталу.

Активируем ячейку  В4 вводим знак «=» (начало формулы) и переходим на ярлычок первого магазина. Щелчок по ячейке В4 на этом листе. В строке формул появляется имя ячейки, которому предшествует название листа

=Магазин 1!:В4.

Отсутствие наглядности при работе с несколькими листами иногда сбивает. Поэтому смотрите на строку формул. Жмем на  знак «+», переходим на следующий лист «Магазин 2» жмем на ячейку B4. Опять жмем на «+», переходим на лист «Магазин 3», Нажимаем на B4 и затем на Enter. Никаких лишних кликов мышью ни в коем случае совершать нельзя!

=’Магазин 1′!B4+’Магазин 2′!B4+’Магазин 3′!B4

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

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

• 14:10
• Результат формулы не помещается в ячейку числа отображаютс #####
• Двойной щелчок по границе между ячейками и формула приобретает удобочитаемый вид.
• Единственное что произошло это сбился формат, и нужно еще раз восстановить цвет ячеек.

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

Источник: http://chainik-town.ru/news/video/excel-ot-prostogo-k-slozhnomu/excel-urok-11-rabota-s-neskolkimi-listami-odnotipnye-dannye-formuly/

Что такое лист в Excel?

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

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

Имена листов

Книга может состоять как из одного (минимальное количество листов в книге), так и из нескольких листов, количество которых определяется в настройках приложения.

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

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

Работа с листами

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

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

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

Надстройка для работы с листами

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

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

Источник: http://macros-vba.ru/nadstrojki/excel/264-chto-takoe-list-v-excel

Работа с листами

По
умолчанию в книге Microsoft Office Excel содержатся три листа, но при
необходимости листы можно добавлять (включая такие типы листов, как
листы диаграмм, листы макросов и листы диалогов) и удалять. Также можно
изменять количество листов по умолчанию для новой книги.Имя
(или заголовок) листа отображается на его ярлычке в нижней части
экрана.

По умолчанию листам присваиваются имена «Лист1», «Лист2» и т.
д., но имя любого листа на более подходящее. ПРИМЕЧАНИЕ.   По умолчанию ярлычки листов отображаются, но если они не видны, убедитесь, что в диалоговом окне Параметры Excel в группе Дополнительно установлен флажок Показывать ярлычки листов (Файл , Параметры Excel).

Чтобы вставить новый лист, выполните одно из следующих действий.

• Чтобы быстро вставить новый лист после существующих листов, щелкните вкладку Вставить лист в нижней части экрана.

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

 СОВЕТ.   Можно также щелкнуть правой кнопкой мыши ярлычок одного из листов и выбрать команду Вставить. На вкладке Общие выделите значок Листи нажмите кнопку ОК.

 ПРИМЕЧАНИЕ.   Чтобы
изменить порядок следования листов в книге, щелкните ярлычок листа,
который необходимо переместить, и перетащите его в необходимое
местоположение.

1. Удерживая нажатой клавишу SHIFT, выберите столько ярлычков листов книги, сколько листов требуется вставить в открытую книгу.

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

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

 СОВЕТ.   Можно также щелкнуть правой кнопкой мыши ярлычки выбранных листов и выбрать команду Вставить. На вкладке Общие выделите значок Листи нажмите кнопку ОК.

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

1. В строке ярлычков листов щелкните правой кнопкой мыши ярлычок, который требуется переименовать, и выберите командуПереименовать.

1. Выделите текущее имя и введите новое.

 СОВЕТ.   При печати листа можно вывести его имя.

Печать имен листа

1. На вкладке Вставка в группе Текст выберите команду Колонтитулы.

1. В представлении Разметка страницы щелкните в том месте, где должно отображаться имя листа.
2. В группе Работа с колонтитулами нажмите кнопку Имя листа .

1. Выделите лист или листы, которые требуется удалить.

ЧТОБЫ ВЫДЕЛИТЬВЫПОЛНИТЕ СЛЕДУЮЩЕЕ

Отдельный лист	Щелкните ярлычок листа. Если ярлычок нужного листа не виден, найдите этот ярлычок с помощью кнопок прокрутки листов, а затем щелкните его.
Два или более смежных листа	Щелкните ярлычок первого листа. Нажав клавишу SHIFT, щелкните ярлычок последнего листа в последовательности листов, которую необходимо выделить.
Два или более несмежных листа	Щелкните ярлычок первого листа. Последовательно щелкните ярлычки необходимых листов, удерживая клавишу CTRL.
Все листы книги	Щелкните правой кнопкой мыши ярлычок какого-либо листа, а затем выберите команду Выделить все листы в контекстном меню.

Совет.     Когда в книге выделено несколько листов, в заголовке рабочего окна появляется надпись [Группа].
Чтобы отменить выделение нескольких листов книги, щелкните любой
невыделенный лист. Если на экране видны только выделенные листы,
наведите указатель на выделенный лист и нажмите правую кнопку мыши.
Затем выберите в контекстном меню команду Разгруппировать листы.

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

 СОВЕТ.   Можно
также щелкнуть правой кнопкой мыши ярлычок листа или любого из
выделенных листов, которые необходимо удалить, и выбрать команду Удалить лист.

Источник: http://veieltab.blogspot.com/p/blog-page_9503.html