Как размножить таблицу в excel
Работая с таблицами Excel, иногда возникает необходимость в распределении информации из одного столбца по нескольким отмеченным строкам. Чтобы не делать этого вручную, можно воспользоваться встроенными инструментами самой программы. То же самое касается размножения функций, формул. При их автоматическом размножении на требуемое количество строк, можно быстро получить точный результат вычислений.
Распределение данных из одного столбца по отдельным строчкам
В Excel существует отдельная команда, с помощью которой можно распределить информацию, собранную в одном столбце, по отдельным строчкам.
Для того чтобы распределить данные, необходимо выполнить несколько простых действий:
- Перейти во вкладку “EXCEL”, которая находится на главной странице инструментов.
- Найти блок с инструментами “Таблица”, кликнуть по нему левой кнопкой мыши.
- Из открывшегося меню выбрать параметр “Размножить столбец по строкам”.
- После этого должно открыться окно с настройками выбранного действия. В первом свободном поле нужно выбрать из предложенного списка тот столбец, который необходимо размножить.
- Когда столбик будет выбран, нужно определиться с типом разделителя. Это может быть точка, запятая, точка с запятой, пробел, перенос текста на другую строку. При желании можно выбрать собственный символ для разделения.
После выполнения всех описанных выше действий, будет создан новый рабочий лист, на котором будет построена новая таблица из множества строчек, в которых будут распределены данные из выбранного столбца.
Важно! Иногда случаются ситуации, когда действие размножения столбцов из основной рабочей таблицы необходимо отметить. В этом случае можно отменить действие через комбинацию клавиш “CTRL+Z” или же кликнуть по значку отмены совершенного действия над основной панелью с инструментами.
Размножение формул
Чаще при работе в Excel случаются ситуации, когда необходимо размножить одну формулу сразу же на несколько столбцов, чтобы получить требуемый результат в соседних ячейках. Сделать это можно вручную. Однако такой способ отнимет слишком много времени. Чтобы автоматизировать процесс, можно воспользоваться двумя способами. С помощью мышки:
После этого в выделенных ячейках появятся результаты по установленной для первой клетке формуле.
Важно! Размножение формулы или определенной функции по всему столбцу с помощью мышки возможно только в том случае, если все клетки ниже будут заполнены. Если одна из ячеек не будет иметь информации внутри, расчет будет окончен на ней.
Если столбец состоит из сотен-тысяч клеток, и некоторые из них являются незаполненными, можно автоматизировать процесс расчета. Для этого необходимо выполнить несколько действий:
- Отметить первую клетку столбца нажатием ЛКМ.
- Прокрутить колесиком до конца столбца на страницы.
- Найти последнюю клетку, зажать клавишу “Shift”, кликнуть по данной ячейке.
Требуемый диапазон будет выделен.
Сортировка данных по столбцам и строкам
Достаточно часто случаются ситуации, когда после автоматического заполнения рабочей таблицы данными, они распределяются хаотично. Чтобы пользователю было удобно работать в дальнейшем, необходимо отсортировать данные по строкам, столбцам. При этом в качестве распределителя можно установить значение по шрифту, по убыванию или возрастанию, по цвету, по алфавиту или же комбинировать данные параметры между собой. Процесс сортировки данных с помощью встроенных инструментов Excel:
- Кликнуть по любому месту рабочей таблицы ПКМ.
- Из появившегося контекстного меню выбрать параметр – “Сортировка”.
- Напротив выбранного параметра появится несколько вариантов сортировки данных.
Еще один способ выбора параметра сортировки информации – через основную панель с инструментами. На ней нужно найти вкладку “Данные”, под ней выбрать пункт “Сортировка”. Процесс сортировки таблицы по отдельному столбцу:
- В первую очередь необходимо выделить диапазон данных из одного столбца.
- На панели задач отобразится значок с выбором вариантов сортировки информации. После нажатия по нему откроется список возможных вариантов сортировки.
Если изначально было выделено несколько столбцов из страницы, после нажатия по значку сортировки на панели задач откроется окно с настройками данного действия. Из предложенных вариантов необходимо выбрать параметр “автоматически расширить выделенный диапазон”. Если же не сделать этого, данные в первом столбце будут отсортированы, но общая структура таблицы нарушится. Процесс сортировки строк:
- В окне с настройками сортировки необходимо перейти во вкладку “Параметры”.
- Из открывшегося окна выбрать параметр “Столбцы диапазона”.
- Для сохранения настроек нажать на кнопку “ОК”.
Параметры, которые изначально установлены в настройках сортировки, не позволяют распределить данные по рабочей таблице случайным образом. Для этого необходимо использовать функцию СЛЧИС.
Заключение
Процедура размножения столбцов по строкам достаточно специфичная, из-за чего не каждый пользователь знает, как ее осуществить. Однако, ознакомившись с представленной выше инструкцией, это можно сделать очень быстро. Дополнительно к этому рекомендуется потренироваться с размножением функций и формул, за счет чего получится экономить большое количество времени при различных расчетах в больших диапазонах ячеек.
Таблицы в редакторе Эксель являются рядом строк и столбцов со связанными сведениями, которые управляются отдельно друг от друга. При работе в Excel с таблицами возможно создать отчеты, проводить расчеты, выстраивать графики и диаграммы, осуществлять сортировку и фильтрацию данных. Те, кто достаточно регулярно работают с Микрософт Эксель, зачастую сталкиваются с задачей правильного копирования сведений таблицы с сохранением форматирования, формул либо их значений. В данной статье рассматриваются способы вставки таблицы в ячейку Эксель.
Как вставить таблицу в ячейку Excel
Использование таблиц в Эксель не представляет особой сложности даже для неопытных пользователей, однако для получения необходимого результата нужно соблюсти алгоритм действий. Вставка таблицы может быть произведена различными методами. Для конкретной цели каждый способ имеет свои достоинства и недостатки, которые будут рассмотрены ниже.
Как вставить таблицу в Excel
Когда у пользователя уже существует заполненный диапазон с данными, можно произвести его преобразование в таблицу:
Когда необходимо создать таблицу с начала, следует:
- Выделить определенный диапазон ячеек, где должен быть расположен элемент.
- Затем аналогичным способом выполняется команда «Вставка» – «Таблицы» – «Таблица» или используется комбинация клавиш «Ctrl+T».
- После этого пользователю останется лишь провести переименование столбцов и заполнение самой таблицы данными.
Для добавления новой строки внизу таблиц нужно начать вводить в нее сведения, редактор самостоятельно добавит элемент в структуру. То же относится и к вставке дополнительного столбца после крайнего с правой стороны.
Чтобы добавить строку либо столбец в середину таблицы, следует выделить ячейку в структуре и кликнуть правой клавишей мышки. Далее в контекстном меню нажать «Вставить» и выбрать один из способов:
- Столбцы таблицы слева.
- Строки таблицы выше.
Важно! На практике применение инструментов «Вставить» либо «Удалить» во время вставки либо удаления диапазонов без окна настроек не рекомендуется. Так как пользователь может запутаться в больших таблицах. Для экономии времени рекомендовано использовать горячие клавиши. Они помогут вызвать окно с настройками вставки и удаления, позволят скорее решить задачу.
Вставка таблицы из Word в ячейку Excel
Чтобы вставить таблицу из редактора Ворд в ячейку Эксель, необходимо:
- Выделить непосредственно таблицу в Ворд, кликнув на иконку слева вверху таблицы.
- Сделать щелчок правой клавишей мыши в каком-либо месте выделенного диапазона и выбрать «Копировать». Для копирования в буфер обмена можно воспользоваться комбинацией кнопок «Ctrl» + «C».
- Открывается новый файл редактора Эксель. На листе Эксель выделяется левый верхний угол диапазона, куда необходимо импортировать выделенную таблицу Ворд. В меню «Главная» в группе «Буфер обмена» нажимается «Вставить». Чтобы вставить таблицу, можно воспользоваться комбинацией кнопок «Ctrl» + «V».
- Чтобы изменить форматирование выделенной таблицы, требуется кликнуть правой клавишей мыши на одну из ячеек. В меню «Параметры вставки» есть такие варианты действий:
- чтобы сохранить форматирование, которое применяется к ячейкам страницы в Эксель, нажимается вариант «Использовать форматы конечных ячеек»;
- чтобы сохранить форматирование таблицы Ворд, выбирается опция «Сохранить исходное форматирование».
В Эксель данные любой из ячеек таблицы Ворд вставляются в отдельный элемент. По завершении вставки сведений необходимо в обязательном порядке осуществить проверку корректности копирования.
Эксель вставляет содержимое ячеек таблицы Ворд в отдельный элемент. По окончании вставки сведений их возможно распространить на остальные ячейки в столбце (к примеру, разделяя имя и фамилию, чтобы они показывались в разных ячейках) при помощи команды «Текст по столбцам».
Вставка таблицы в виде рисунка
Когда необходимо импортировать скопированную таблицу в качестве обыкновенной картинки, выполняется это таким способом:
- После копирования требуемой таблицы необходимо войти в контекстное меню выбранной для вставки ячейки. Затем выбрать пункт «Рисунок» в опциях «Специальной вставки».
- Благодаря этому можно получить продублированную в форме изображения таблицу, которая можно перемещать. Кроме того, допустимо переворачивать ее и изменять размеры. Однако редактировать сведения и менять их вид уже не удастся.
Важно! По завершении вставки сведений иногда требуется их очистка, чтобы использовать функции вычислений Эксель. К примеру, в ячейках бывают лишние пробелы, числа могут иметь вид текста, а не числовых значений, с которыми выполняются вычисления, а даты иногда отображаются ненадлежащим образом.
Заключение
Эксель предлагает пользователям достаточный выбор способов вставки таблицы в ячейку. Выделив небольшое количество времени на изучение разных методов осуществления такой задачи, можно сэкономить время при последующей работе в редакторе.
В Microsoft Excel есть много инструментов для сборки данных из нескольких таблиц (с разных листов или из разных файлов): прямые ссылки, функция ДВССЫЛ (INDIRECT) , надстройки Power Query и Power Pivot и т.д. С этой стороны баррикад всё выглядит неплохо.
Но если вы нарвётесь на обратную задачу - разнесения данных из одной таблицы на разные листы - то всё будет гораздо печальнее. На сегодняшний момент цивилизованных встроенных инструментов для такого разделения данных в арсенале Excel, к сожалению, нет. Так что придется задействовать макрос на Visual Basic, либо воспольоваться связкой макрорекордер+Power Query с небольшой "доработкой напильником" после.
Давайте подробно рассмотрим, как это можно реализовать.
Постановка задачи
Имеем в качестве исходных данных вот такую таблицу размером больше 5000 строк по продажам:
Задача: разнести данные из этой таблицы по городам на отдельные листы этой книги. Т.е. на выходе нужно получить на каждом листе только те строки из таблицы, где продажа была в соответствующем городе:
Подготовка
Чтобы не усложнять код макроса и сделать его максимально простым для понимания, выполним пару подготовительных действий.
Во-первых, создадим отдельную таблицу-справочник, где в единственном столбце будут перечислены все города, для которых нужно создать отдельные листы. Само-собой, в этом справочнике могут быть не все города, присутствующие в исходных данных, а только те, по которым нам нужны отчеты. Проще всего создать такую таблицу, используя команду Данные - Удалить дубликаты (Data - Remove duplicates) для копии столбца Город или функцию УНИК (UNIQUE) - если у вас последняя версия Excel 365.
Поскольку новые листы в Excel по умолчанию создаются перед (левее) текущего (предыдущего), то имеет смысл также отсортировать города в этом справочнике по убыванию (от Я до А) - тогда после создания листы-города расположатся по алфавиту.
Во-вторых, преобразуем обе таблицы в динамические ("умные"), чтобы с ними было проще работать. Используем команду Главная - Форматировать как таблицу (Home - Format as Table) или сочетание клавиш Ctrl + T . На появившейся вкладке Конструктор (Design) назовём их таблПродажи и таблГорода, соответственно:
Способ 1. Макрос для деления по листам
На вкладке Разработчик (Developer) нажмите на кнопку Visual Basic или используйте сочетание клавиш Alt + F11 . В открывшемся окне редактора макросов вставьте новый пустой модуль через меню Insert - Module и скопируйте туда следующий код:
Здесь с помощью цикла For Each . Next реализован проход по ячейкам справочника таблГорода, где для каждого города происходит его фильтрация (метод AutoFilter) в исходной таблице продаж и затем копирование результатов на новый созданный лист. Попутно созданный лист переименовывается в то же имя города и на нем включается автоподбор ширины столбцов для красоты.
Запустить созданный макрос в Excel можно на вкладке Разработчик кнопкой Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 .
Способ 2. Создаем множественные запросы в Power Query
У предыдущего способа, при всей его компактности и простоте, есть существенный недостаток - созданные макросом листы не обновляются при изменениях в исходной таблице продаж. Если обновление "на лету" необходимо, то придется использовать связку VBA+Power Query, а точнее - создавать с помощью макроса не просто листы со статическими данными, а обновляемые запросы Power Query.
Макрос в этом случае частично похож на предыдущий (в нём тоже есть цикл For Each . Next для перебора городов в справочнике), но внутри цикла будет уже не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:
После его запуска мы увидим те же листы по городам, но формировать их будут уже созданные запросы Power Query:
При любых изменениях в исходных данных достаточно будет обновить соответствующую таблицу правой кнопкой мыши - команда Обновить (Refresh) или обновить сразу все города оптом, используя кнопку Обновить всё на вкладке Данные (Data - Refresh All) .
Итак, таблица в Excel – это прямоугольная область листа, в которой каждая строка представляет собой набор данных, а в ячейке на пересечении данной строки и каждого столбца находится единица данных. Каждому столбцу присваивается уникальное имя. Столбцы таблицы называются полями, а строки – записями. В таблице не может быть записей, в которых нет данных ни в одном поле.
Если на листе Excel выбран прямоугольный диапазон ячеек, его легко преобразовать в таблицу, и, наоборот, с записями таблицы можно работать как с обычным диапазоном ячеек.
В таблицах можно использовать функции и формулы Excel , в частности, можно вставить строку (запись), в которой подсчитываются общие или промежуточные итоги.
Создание таблицы
- Выделить любую ячейку, содержащую данные, которые должны будут войти в таблицу.
- В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table].
- Появится диалоговое окно, в котором Excel автоматически предложит границы диапазона данных для таблицы
Если этот диапазон нужно изменить, достаточно выделить нужный диапазон данных курсором.
Присвоение имени таблице
По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя.
- Выделить ячейку таблицы.
- На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы нажать клавишу Enter.
Требования к именам таблиц аналогичны требованиям к именованным диапазонам.
Форматирование таблиц
В созданной таблице можно изменить цвет, шрифт, вид границ и прочее. С помощью форматирования можно добиться того, что информация будет легче восприниматься. Эти изменения можно выполнить самостоятельно или выбрать готовый стиль оформления.
- Выделить ячейку таблицы.
- На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].
Вычисления в таблицах
К таблице можно добавить дополнительную, итоговую строку, в которой будут размещены результаты выполнения различных функций, примененных к данным некоторых или всех полей.
Порядок действия следующий:
- На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].
- В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию.
Для ввода в конец таблицы новых записей следует выделить строку итогов и воспользоваться правой кнопкой мыши. Появится контекстное меню, в нем нужно выбрать пункт Вставить. Если в появившуюся новую строку ввести данные, они будут автоматически участвовать в пересчете итогов.
Для того чтобы сократить время на добавление строк в таблицу, можно отключить строку итогов и довалять новые данные с новой строки. В этом случае таблица будет автоматически расширять свой диапазон.
Для проведения расчетов и помещения результатов в новое поле, достаточно ввести формулу в одну ячейку этого поля. Excel автоматически размножит ее по всем ячейкам данного поля. Если правильно установлены параметры Excel , при вводе формулы в нее записываются не адреса ячейки, а имена полей.
Если вместо имени поля на экране в формулах указаны адреса ячеек, необходимо изменить настройку:
- Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
- В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
- OK.
Преобразование таблицы в обычный диапазон
При работе с таблицами, наряду с преимуществами, действует ряд ограничений: нельзя объединять ячейки, нельзя добавлять промежуточные итоги и т.д. Если расчеты в таблице завершены и нужны только данные из нее и, возможно, оформление, таблицу можно быстро преобразовать в обычный диапазон данных.
Для этого необходимо выполнить следующие шаги:
Читайте также: