Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как создавать новые листы для каждой строки в Excel?

Как создать список всех листов, содержащихся в книге Excel при помощи макрофункции

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

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

  1. Вставьте новый рабочий лист, который должен содержать список.
  2. Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
  3. Введите List в поле Name (Имя).
  4. Введите следующую формулу в поле Диапазон (рисунок 9.1): =Заменить(REFERENCE.WORK.BOOK(1);1;1;REFERENCE(«]»;REFERENCE.WORK.BOOK(1));»»). .
  5. Нажмите OK, чтобы закрыть диалоговое окно «Создать имя».

http://www.kids.ua/

Рисунок 9.1. Использование окна «Создать имя» для создания именованной формулы

Примечание: в формуле используется ПОЛОВИНА. РАБОТА. КНИГА, которая не является одной из стандартных функций рабочего листа. Эта макрофункция была написана в относительно старом формате XLM и предназначена для использования на специальном рабочем листе макроса. Используя аргумент 1, она возвращает массив имен рабочих листов, в котором имя книги появляется перед именем каждого рабочего листа. Функции REPLACE и SEARCH удаляют имя книги, сопровождающее имя листа. Введите следующую формулу в ячейку A1, а затем скопируйте ее вниз по столбцу: =INDEX(List;STRING()).

На рисунке 9.2 показана эта формула для области A1:A10. В книге семь листов, поэтому формула возвращает ошибку #SSLIST!, когда пытается отобразить имя несуществующего листа. Чтобы избежать этого, измените формулу следующим образом: =ESLIOB ERROR(INDEX(List;STRIKE();»») .

Рис. 9.2. Использование формулы для отображения списка имен листов

Рисунок 9.2. Использование формулы для отображения списка имен листов

Список названий листов можно откорректировать, если вы будете добавлять, удалять или переименовывать листы, но это не происходит автоматически. Чтобы принудительно обновить формулы, нажмите Ctrl+Alt+F9. Если вы хотите, чтобы имена листов автоматически обновлялись при пересчете книги, измените именованную формулу следующим образом, чтобы сделать ее меняющейся: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)&Т(ТДАТА()) .

Рис. 9.3. Создание списка гиперссылок

Рис. 9.3 Создание списка гиперссылок

Зачем может понадобиться список названий листов? На рис. 9.3 показано оглавление, созданное с помощью ссылки ГИПЕРССЫЛКА. В ячейке В1 записана следующая формула: =ГИПЕРССЫЛКА(«#»&A1&»!A1″;»Перейти по ссылке») . Щелкнув на гиперссылке, вы активизируете лист и выделяете ячейку А1. К сожалению, Excel не поддерживает гиперссылок, ведущих на лист с диаграммами, поэтому при попытке сослаться на такой лист программа выдаст ошибку.

Читайте так же:
Как случайным образом перемешать строки / столбцы / диапазон ячеек в Excel?

Если вы используете такие методы, вам необходимо сохранить книгу как файл с поддержкой макросов (в формате *.xlsm или *.xls).

Как сделать диаграммы в Excel из данных на двух или более листах

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

Как создать диаграмму из данных на нескольких листах в Excel

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

1. Строим диаграмму на основе данных первого листа

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

Объемная гистограммаИменно объемная гистограмма с накоплением является самым популярным типом, используемых диаграмм

2. Вносим данные со второго листа

Активируйте мини-панель Chart Tools слева, щелкнув созданный график. Затем выберите значок Конструктор и нажмите кнопку Выбрать данные.

Можно также нажать кнопку Фильтры диаграммы . Справа, в самом низу появившегося списка кликнуть Выбрать данные.

В окне Select Data Source (Выбор источника данных) нажмите на ссылку Add (Добавить).

Добавляем новый источник

Добавьте данные со второго листа. Это важный момент, поэтому необходимо соблюдать осторожность.

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

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

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

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

Так выглядит выделение нужных данных для внесения в диаграмму

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

Убедитесь, что ссылки в строках Line name и Values корректны. Нажмите OK .

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

Читайте так же:
Как скрыть кнопку выбора при скрытии строки в Excel?

Как видно из изображения, прикрепленного выше, имя строки связано с ячейкой B1 , где оно прописано. Вместо этого имя можно ввести как текст. Например, второй ряд данных.

На диаграмме будет легенда с названиями рядов. Поэтому лучше всего дать им соответствующие названия.

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

Если у вас что-то не так, как на изображении выше, значит где-то допустили ошибку, и нужно начать сначала

3. При необходимости добавляем еще слои

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

В примере данные разделены на три ряда. Вот как выглядит гистограмма после всех действий:

Готовая гистограмма в несколько слоев

4. Настраиваем и улучшаем гистограмму (по желанию)

При работе в Excel версий 2013 и 2016 название и легенда добавляются автоматически при создании гистограммы. В нашем примере они не были добавлены, поэтому мы сделаем это сами.

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

Можно оставить все как есть и не добавлять дополнительные параметры

Обсуждаются и другие параметры, такие как формат оси и подпись данных.

Составляем диаграммы из итоговых данных в таблице

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

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

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

Вот результат работы:

С итоговой таблицей и работать проще

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

Редактируем диаграммы, созданной из данных на нескольких листах

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

Читайте так же:
Как скопировать гиперссылку только из одной ячейки в другую в Excel?

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

  • Через меню выбора источника данных;
  • Через фильтры;
  • Через формулы рядов данных.

Редактируем через меню Выбор источника данных

Чтобы открыть меню Select Data Source, нажмите на подменю Select Data вкладки Designer.

Как редактировать строку

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

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

Окно редактирования данных в гистограмме

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

Изменяем ряды через Фильтр диаграмм

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

Нажав на Фильтр, можно скрыть линии, которые не должны быть видны на графике.

Нажмите кнопку Change Row (Изменить строку), когда появится строка. Появится окно Select Data Source (Выбор источника данных). В нем делаем необходимые настройки.

Обратите внимание! Наведение курсора мыши на строку выделяет ее для более легкого распознавания.

Меню изменения данных – просто снимаем флажки и изменяем периоды

Редактируем ряд при помощи формулы

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

Любые данные в Excel принимают вид формулы

Каждая формула включает четыре основных элемента:

= RANK([Имя строки], [x-значения], [y-значения], номер ряда).

Наша формула, приведенная в примере, имеет следующее пояснение

  • Имя строки (‘2013’!$B$1) берется из ячейки B1 рабочего листа 2013 .
  • Значения строки (‘2013’!$A$2:$A$5) берутся из ячеек A2:A5 рабочего листа 2013 .
  • Значения столбца (‘2013’!$B$2:$B$5) берутся из ячеек B2:B5 рабочего листа 2013 .
  • Число (1) означает, что выбранный ряд является первым на графике.

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

Читайте так же:
Как создать несколько листов из списка значений ячеек?

Как вставить/добавить новые/пустые строки в Excel?

Наиболее распространенными действиями Excel при работе с таблицами являются добавление и удаление строк, особенно пустых. Рассмотрим методы добавления и удаления пустых строк в таблице.

Вставка одной пустой строки в таблицу Excel

Вы можете добавить новую строку с помощью стандартных инструментов Excel следующим образом: щелкните правой кнопкой мыши на номере строки, над которой вы хотите вставить новую строку, и выберите «Вставить» в контекстном меню

как вставить пустую строку в Excel

В то же время, если какие-либо данные в таблице были отформатированы (изменены шрифты, цвета, заливки ячеек), то после добавления новой строки появляется кнопка Добавить параметры, нажав которую можно выбрать для новой строки один из трех пунктов «Формат как выше», «Формат как ниже», «Очистить формат».

параметры добавления пустой строки

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

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

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

как добавить новые строки в Excel

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

Как вставить/добавить заданное количество пустых/новых строк в определенные места?

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

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

2. Вставьте заданное количество пустых строк как над, так и под строками с искомым значением;

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

Вставка новой строки под каждой второй, третьей, пятой, n-й строкой выбранного диапазона (шаг, задаваемый пользователем).

5. вставка пустой строки при каждом изменении значения в столбце;

Читайте так же:
Как скопировать диаграмму с текстовыми полями в Excel?

6. вставьте пустую строку между заполненными ячейками (после каждой заполненной ячейки в столбце с данным номером);

7. выбор интервалов вставки линии, доступно несколько режимов:

(a) Используемая область рабочего листа — это область активного рабочего листа, включая все ячейки между первой и последней заполненной ячейкой;

B) Используемые интервалы — отличается от предыдущего режима тем, что данные обрабатываются в пределах интервалов, используемых для каждого рабочего листа электронной таблицы;

(в) поле — поле, выбранное пользователем;

D) Диапазоны листов — это диапазоны с одинаковым именем на всех рабочих листах рабочей книги.

* Тщательно выбирайте ассортимент, чтобы добиться желаемого эффекта!

8. выберите необходимые условия для значений ячеек.

Значения числовых величин равны/неравны, больше/меньше, меньше/меньше.

Для текстовых значений: совпадает/не совпадает, содержит/не содержит, начинается/не начинается, заканчивается/не заканчивается

Программа проверяет все ячейки в указанном диапазоне, и если выбранное условие выполняется, программа вставляет нужное количество пустых строк выше/ниже строки (по вашему выбору), содержащей ячейку с указанным значением. Существует ряд условий значений ячеек, которые можно использовать для определения места вставки новых строк, а также выбор интервалов обработки. Для большей гибкости ячейки с нужными значениями можно искать отдельно для текстовых и числовых значений внутри ячеек. Также можно искать как в пустых, так и в непустых ячейках. Этот макрос также позволяет добавить новую строку к каждой n-й строке в выбранном диапазоне. Например, если вы вставите две пустые строки под каждой второй строкой в диапазоне A2:A24, вы получите следующий результат:

Вставка разного количества пустых строк

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

ishodnye dannye dlya vstavki raznogo kolichestva pustyh strok

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

vstavka raznogo kolichestva pustyh strok ukazannogo v zadannom stolbce

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

rezultat vstavki raznogo kolichestva pustyh strok

Удаление пустых строк

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector