Всем привет.
Есть таблица из 1000 строк по типу такой:
| наименование | цвет | длина | кол-во |
| баклажан | фиолетовый | 22см | 7 |
| баклажан | красный | 20см | 4 |
нужно, чтобы она превращалась в такую :
| наименование | цвет | длина | кол-во |
| баклажан | фиолетовый | 22см | 1 |
| баклажан | фиолетовый | 22см | 1 |
| баклажан | фиолетовый | 22см | 1 |
| баклажан | фиолетовый | 22см | 1 |
| баклажан | фиолетовый | 22см | 1 |
| баклажан | фиолетовый | 22см | 1 |
| баклажан | фиолетовый | 22см | 1 |
| баклажан | красный | 20см | 1 |
| баклажан | красный | 20см | 1 |
| баклажан | красный | 20см | 1 |
| баклажан | красный | 20см | 1 |
как сделать это встроенными функциями — не додумался.
Если поможете буду чрезмерно благодарен.
Заранее спасибо.
|
Как каждую ячейку из столбца размножить на 5? |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
|
1 / 1 / 0 Регистрация: 28.01.2016 Сообщений: 22 |
|
|
1 |
|
Размножить строки согласно числу в ячейке26.06.2017, 12:14. Показов 19553. Ответов 9
Всем здравствуйте
0 |
|
2640 / 1697 / 694 Регистрация: 04.09.2015 Сообщений: 3,367 |
|
|
26.06.2017, 16:56 |
2 |
|
Код =ИНДЕКС(A$2:A$7;ПОИСКПОЗ(СТРОКА(A1)-1;СУММЕСЛИ(СМЕЩ($G$1;;;СТРОКА(A$1:A$7));">0")))
0 |
|
1 / 1 / 0 Регистрация: 28.01.2016 Сообщений: 22 |
|
|
27.06.2017, 10:58 [ТС] |
3 |
|
Спасибо за помощь. Решение формулой — высший пилотаж, не представлял что так можно Код =ИНДЕКС(A$2:A$25;ПОИСКПОЗ(СТРОКА(A1)-1;СУММЕСЛИ(СМЕЩ($Y$1;;;СТРОКА(A$1:A$25));">0")))
0 |
|
2640 / 1697 / 694 Регистрация: 04.09.2015 Сообщений: 3,367 |
|
|
27.06.2017, 11:16 |
4 |
|
Данная формула есть формула массива, ввод таких формул делается тремя клавишами Ctrl+Shift+Enter
0 |
|
1 / 1 / 0 Регистрация: 28.01.2016 Сообщений: 22 |
|
|
27.06.2017, 12:30 [ТС] |
5 |
|
Сработало, спасибо за подсказку, но почему то размножает только до 20-й строки, а дальше сплошь #ССЫЛКА. В чем может быть причина?
0 |
|
2640 / 1697 / 694 Регистрация: 04.09.2015 Сообщений: 3,367 |
|
|
27.06.2017, 13:00 |
6 |
|
В вашем последнем примере сумма в столбце Y равна 6. Это значит, что формула заполнит 6 строк значениями, а ниже будет ошибка #ССЫЛКА.
размножает только до 20-й строки, а дальше сплошь #ССЫЛКА возможно в реальном файле сумма в Y равна 20
0 |
|
1 / 1 / 0 Регистрация: 28.01.2016 Сообщений: 22 |
|
|
27.06.2017, 13:48 [ТС] |
7 |
|
К сожалению не помогло. Может ли быть другая причина? Независимо от того какие значения я выставляю в столбце Y, дальше 20-й просто везде дублируется 21-я строка
0 |
|
2640 / 1697 / 694 Регистрация: 04.09.2015 Сообщений: 3,367 |
|
|
27.06.2017, 14:32 |
8 |
|
А диапазоны надо править Код =ИНДЕКС(A$2:A$99;ПОИСКПОЗ(СТРОКА(A1)-1;СУММЕСЛИ(СМЕЩ($Y$1;;;СТРОКА(A$1:A$98));">0")))
0 |
|
1 / 1 / 0 Регистрация: 28.01.2016 Сообщений: 22 |
|
|
27.06.2017, 16:12 [ТС] |
9 |
|
Теперь все работает еще раз спасибо Как с помощью ИНДЕКС данные типа
0 |
|
2640 / 1697 / 694 Регистрация: 04.09.2015 Сообщений: 3,367 |
|
|
27.06.2017, 20:30 |
10 |
|
Srfbc, это новый вопрос, а значит должна быть новая тема.
0 |
Работая с таблицами Excel, иногда возникает необходимость в распределении информации из одного столбца по нескольким отмеченным строкам. Чтобы не делать этого вручную, можно воспользоваться встроенными инструментами самой программы. То же самое касается размножения функций, формул. При их автоматическом размножении на требуемое количество строк, можно быстро получить точный результат вычислений.
Содержание
- Распределение данных из одного столбца по отдельным строчкам
- Размножение формул
- Сортировка данных по столбцам и строкам
- Заключение
Распределение данных из одного столбца по отдельным строчкам
В Excel существует отдельная команда, с помощью которой можно распределить информацию, собранную в одном столбце, по отдельным строчкам.
Для того чтобы распределить данные, необходимо выполнить несколько простых действий:
- Перейти во вкладку “EXCEL”, которая находится на главной странице инструментов.
- Найти блок с инструментами “Таблица”, кликнуть по нему левой кнопкой мыши.
- Из открывшегося меню выбрать параметр “Размножить столбец по строкам”.
- После этого должно открыться окно с настройками выбранного действия. В первом свободном поле нужно выбрать из предложенного списка тот столбец, который необходимо размножить.
- Когда столбик будет выбран, нужно определиться с типом разделителя. Это может быть точка, запятая, точка с запятой, пробел, перенос текста на другую строку. При желании можно выбрать собственный символ для разделения.
После выполнения всех описанных выше действий, будет создан новый рабочий лист, на котором будет построена новая таблица из множества строчек, в которых будут распределены данные из выбранного столбца.
Важно! Иногда случаются ситуации, когда действие размножения столбцов из основной рабочей таблицы необходимо отметить. В этом случае можно отменить действие через комбинацию клавиш “CTRL+Z” или же кликнуть по значку отмены совершенного действия над основной панелью с инструментами.
Размножение формул
Чаще при работе в Excel случаются ситуации, когда необходимо размножить одну формулу сразу же на несколько столбцов, чтобы получить требуемый результат в соседних ячейках. Сделать это можно вручную. Однако такой способ отнимет слишком много времени. Чтобы автоматизировать процесс, можно воспользоваться двумя способами. С помощью мышки:
- Выделить самую верхнюю ячейку из таблицы, в которой находится формула (с помощью ЛКМ).
- Направить курсор на крайний правый угол ячейки, чтобы появилось изображение черного крестика.
- Нажать ЛКМ по появившемуся значку, стянуть мышку на требуемое количество ячеек вниз.
После этого в выделенных ячейках появятся результаты по установленной для первой клетке формуле.
Важно! Размножение формулы или определенной функции по всему столбцу с помощью мышки возможно только в том случае, если все клетки ниже будут заполнены. Если одна из ячеек не будет иметь информации внутри, расчет будет окончен на ней.
Если столбец состоит из сотен-тысяч клеток, и некоторые из них являются незаполненными, можно автоматизировать процесс расчета. Для этого необходимо выполнить несколько действий:
- Отметить первую клетку столбца нажатием ЛКМ.
- Прокрутить колесиком до конца столбца на страницы.
- Найти последнюю клетку, зажать клавишу “Shift”, кликнуть по данной ячейке.
Требуемый диапазон будет выделен.
Сортировка данных по столбцам и строкам
Достаточно часто случаются ситуации, когда после автоматического заполнения рабочей таблицы данными, они распределяются хаотично. Чтобы пользователю было удобно работать в дальнейшем, необходимо отсортировать данные по строкам, столбцам. При этом в качестве распределителя можно установить значение по шрифту, по убыванию или возрастанию, по цвету, по алфавиту или же комбинировать данные параметры между собой. Процесс сортировки данных с помощью встроенных инструментов Excel:
- Кликнуть по любому месту рабочей таблицы ПКМ.
- Из появившегося контекстного меню выбрать параметр – “Сортировка”.
- Напротив выбранного параметра появится несколько вариантов сортировки данных.
Еще один способ выбора параметра сортировки информации – через основную панель с инструментами. На ней нужно найти вкладку “Данные”, под ней выбрать пункт “Сортировка”. Процесс сортировки таблицы по отдельному столбцу:
- В первую очередь необходимо выделить диапазон данных из одного столбца.
- На панели задач отобразится значок с выбором вариантов сортировки информации. После нажатия по нему откроется список возможных вариантов сортировки.
Если изначально было выделено несколько столбцов из страницы, после нажатия по значку сортировки на панели задач откроется окно с настройками данного действия. Из предложенных вариантов необходимо выбрать параметр “автоматически расширить выделенный диапазон”. Если же не сделать этого, данные в первом столбце будут отсортированы, но общая структура таблицы нарушится. Процесс сортировки строк:
- В окне с настройками сортировки необходимо перейти во вкладку “Параметры”.
- Из открывшегося окна выбрать параметр “Столбцы диапазона”.
- Для сохранения настроек нажать на кнопку “ОК”.
Параметры, которые изначально установлены в настройках сортировки, не позволяют распределить данные по рабочей таблице случайным образом. Для этого необходимо использовать функцию СЛЧИС.
Заключение
Процедура размножения столбцов по строкам достаточно специфичная, из-за чего не каждый пользователь знает, как ее осуществить. Однако, ознакомившись с представленной выше инструкцией, это можно сделать очень быстро. Дополнительно к этому рекомендуется потренироваться с размножением функций и формул, за счет чего получится экономить большое количество времени при различных расчетах в больших диапазонах ячеек.
Оцените качество статьи. Нам важно ваше мнение:
Данная команда разносит значения ячеек указанного столбца таблицы по отдельным строкам:
Чтобы воспользоваться данной командой выделите таблицу, перейдите во вкладку «ЁXCEL» Главного меню, нажмите кнопку «Таблицы» и выберите команду «Размножить столбец по строкам»:
В открывшемся диалоговом окне укажите столбец, ячейки которого необходимо размножить. Выберите знак — разделитель, по которому следует разделять данные (можно указать свой) и нажмите 
:
Программа создаст новый лист в книге и сформирует на нем таблицу с размноженными по строкам ячейками указанного столбца:
Для отмены операции нажмите кнопку «Отмены последнего действия» 
на панели быстрого доступа или сочетание клавиш «Ctrl+Z» — вставленный лист со сформированной таблицей будет удален.
Добавить комментарий
Если ваша работа требует, чтобы вы практически жили в таблицах Excel, возможно, вы ищете способы упростить повторяющиеся задачи. Например, всякий раз, когда вы работаете с данными, которые необходимо регулярно обновлять, вам часто нужно добавлять несколько строк.
В этом руководстве мы расскажем, как вставить несколько строк в Excel за несколько простых и быстрых шагов. Выберите тот, который лучше всего подходит для вас.
Как вставить несколько строк, перетащив выделенную границу
Это самый быстрый способ вставить несколько пустых строк в лист Excel. Это просто и не требует использования каких-либо сочетаний клавиш.
- Выберите строку, ниже или выше которой вы хотите ввести пустые строки.
- Наведите указатель мыши на выделенную границу.
- Нажмите Shift, и вы увидите, что указатель изменится на точку со стрелками вверх и вниз. Перетащите выделение вниз на количество строк, которые вы хотите добавить.
Если вам не нравится идея несколько щелчков правой кнопкой мыши пока вы работаете с листом Excel, вы можете использовать два сочетания клавиш.
Сначала выберите количество строк, равное количеству пустых строк, которые вы хотите ввести. Используйте Shift + Пробел, чтобы выбрать все столбцы для выбранных строк, или выберите строки, используя номера строк слева.
Затем используйте любой из двух ярлыков:
- Alt + I и Alt + R: нажмите Alt + I, удерживая клавишу Alt, нажмите R.
- Ctrl + Plus: вам нужно использовать знак плюса на цифровой клавиатуре. Если вы хотите использовать знак плюса на основной клавиатуре, используйте Ctrl + Shift + Plus.
Метод быстрого доступа тоже масштабируемый. Нажатие F4 на листе Excel повторяет предыдущее действие. Вы можете использовать один из приведенных выше ярлыков, чтобы добавить, например, 10 строк, а затем нажать F4 10 раз, чтобы добавить 100 пустых строк.
Как вставить несколько скопированных строк с помощью сочетаний клавиш
Если вы хотите скопировать или вырезать несколько строк вместо добавления пустых строк, используйте сочетание клавиш Alt + I + E.
- Начните с копирования или вырезания строк.
- Выберите строку выше строки, которые вы хотите вставить, нажав номер строки слева.
- Нажмите Alt + I + E и выберите Сдвиг ячеек вниз.
Вы также можете использовать следующие сочетания клавиш вместо Alt + I + E (процесс одинаков, независимо от того, какой ярлык вы используете):
- Ctrl + Shift + =
- Control + Plus (знак плюса должен быть на цифровой клавиатуре)
Как вставить несколько строк с помощью параметра вставки
Чтобы использовать этот метод, все, что вам нужно сделать, это выбрать количество строк, которые вы хотите добавить, щелкнуть правой кнопкой мыши по выделенному фрагменту и выбрать «Вставить».
Если в ваших данных много столбцов, нажмите Shift + Пробел, чтобы выбрать все столбцы для выбранных строк, или выберите строки, используя номера строк в левой части листа.
Когда вы выбираете «Вставить», Excel вставляет строки над первой выбранной вами строкой.
Если вы хотите ввести относительно большее количество пустых строк, вы можете выбрать диапазон, а затем использовать параметр «Вставить». Например, вы хотите ввести 500 строк.
- Выберите диапазон в формате «начальная строка: последняя строка». Предполагая, что вы хотите ввести 500 строк после строки 5, выберите диапазон 5: 504, указав этот диапазон в поле имени ячейки.
- Щелкните правой кнопкой мыши и выберите Вставить.
Как вставить несколько строк с помощью макроса Excel
Если вы работаете с большим набором данных на листе, который предполагается использовать регулярно, вы можете автоматизировать вставку строк с помощью макросов. Вам не нужно быть экспертом, хотя знание основ VBA может дать вам много возможностей при использовании Excel.
Перед тем, как начать процесс, проверьте, есть ли на ленте Excel вверху вкладка с названием «Разработчик». Если это не так, перейдите в «Файлы»> «Параметры»> «Настроить ленту» и установите переключатель рядом с «Разработчик».
- Перейдите на вкладку Разработчик и выберите Visual Basic.
- Найдите меню «Вставка», когда откроется редактор Visual Basic, и выберите «Модуль».
- Введите в редакторе следующий код:
Подложка InsertRowsAtCursor ()
Answer = InputBox («Сколько строк нужно вставить? (Максимум 100 строк)»)
NumLines = Int (Val (ответ))
Если NumLines> 100, то
NumLines = 100
Конец, если
Если NumLines = 0, то
Перейти к концу
Конец, если
Делать
Selection.EntireRow.Insert
Счетчик = Счетчик + 1
Цикл пока счетчик
EndInsertLines:
Конец подписки
- Закройте редактор кода.
- На верхней ленте выберите «Разработчик»> «Макросы» или просто нажмите Alt + F8. Выберите только что созданный макрос и выберите Параметры. Установите сочетание клавиш для макроса, но убедитесь, что это не существующая комбинация клавиш, которую вы часто используете, например Ctrl + C. В этом случае мы использовали Ctrl + Shift + T. Когда вы закончите, нажмите OK, чтобы сохранить изменения и выйти.
- Вернитесь на рабочий лист и выберите ячейку, ниже которой вы хотите ввести строки.
- Используйте ярлык, который вы установили для макроса (в данном случае Ctrl + Shift + T). Вы увидите всплывающее окно на листе с просьбой ввести количество строк, которые вы хотите добавить. Например, предположим, вы хотите ввести пять строк. Введите 5 и нажмите Enter.
- Это добавит 5 строк ниже ячейку, которую вы выбрали.
Каждый раз, когда вы хотите вставить несколько строк в Excel, просто используйте ярлык, введите количество строк, и все готово.
Вставка нескольких строк в Excel
если ты часто использую Excel, стоит узнать, как можно сэкономить время на повторяющихся механических задачах, таких как вставка строк. Поскольку есть несколько способов вставить несколько строк в Excel, все зависит от того, какой метод вам удобнее. Если вы добавили много пустых строк в набор данных, есть несколько способов удалить пустые строки.
Работая с таблицами Excel, иногда возникает необходимость в распределении информации из одного столбца по нескольким отмеченным строкам. Чтобы не делать этого вручную, можно воспользоваться встроенными инструментами самой программы. То же самое касается размножения функций, формул. При их автоматическом размножении на требуемое количество строк, можно быстро получить точный результат вычислений.
Распределение данных из одного столбца по отдельным строчкам
В Excel существует отдельная команда, с помощью которой можно распределить информацию, собранную в одном столбце, по отдельным строчкам.
Для того чтобы распределить данные, необходимо выполнить несколько простых действий:
- Перейти во вкладку “EXCEL”, которая находится на главной странице инструментов.
- Найти блок с инструментами “Таблица”, кликнуть по нему левой кнопкой мыши.
- Из открывшегося меню выбрать параметр “Размножить столбец по строкам”.
- После этого должно открыться окно с настройками выбранного действия. В первом свободном поле нужно выбрать из предложенного списка тот столбец, который необходимо размножить.
- Когда столбик будет выбран, нужно определиться с типом разделителя. Это может быть точка, запятая, точка с запятой, пробел, перенос текста на другую строку. При желании можно выбрать собственный символ для разделения.
После выполнения всех описанных выше действий, будет создан новый рабочий лист, на котором будет построена новая таблица из множества строчек, в которых будут распределены данные из выбранного столбца.
Важно! Иногда случаются ситуации, когда действие размножения столбцов из основной рабочей таблицы необходимо отметить. В этом случае можно отменить действие через комбинацию клавиш “CTRL+Z” или же кликнуть по значку отмены совершенного действия над основной панелью с инструментами.
Размножение формул
Чаще при работе в Excel случаются ситуации, когда необходимо размножить одну формулу сразу же на несколько столбцов, чтобы получить требуемый результат в соседних ячейках. Сделать это можно вручную. Однако такой способ отнимет слишком много времени. Чтобы автоматизировать процесс, можно воспользоваться двумя способами. С помощью мышки:
- Выделить самую верхнюю ячейку из таблицы, в которой находится формула (с помощью ЛКМ).
- Направить курсор на крайний правый угол ячейки, чтобы появилось изображение черного крестика.
- Нажать ЛКМ по появившемуся значку, стянуть мышку на требуемое количество ячеек вниз.
После этого в выделенных ячейках появятся результаты по установленной для первой клетке формуле.
Важно! Размножение формулы или определенной функции по всему столбцу с помощью мышки возможно только в том случае, если все клетки ниже будут заполнены. Если одна из ячеек не будет иметь информации внутри, расчет будет окончен на ней.
Если столбец состоит из сотен-тысяч клеток, и некоторые из них являются незаполненными, можно автоматизировать процесс расчета. Для этого необходимо выполнить несколько действий:
- Отметить первую клетку столбца нажатием ЛКМ.
- Прокрутить колесиком до конца столбца на страницы.
- Найти последнюю клетку, зажать клавишу “Shift”, кликнуть по данной ячейке.
Требуемый диапазон будет выделен.
Сортировка данных по столбцам и строкам
Достаточно часто случаются ситуации, когда после автоматического заполнения рабочей таблицы данными, они распределяются хаотично. Чтобы пользователю было удобно работать в дальнейшем, необходимо отсортировать данные по строкам, столбцам. При этом в качестве распределителя можно установить значение по шрифту, по убыванию или возрастанию, по цвету, по алфавиту или же комбинировать данные параметры между собой. Процесс сортировки данных с помощью встроенных инструментов Excel:
- Кликнуть по любому месту рабочей таблицы ПКМ.
- Из появившегося контекстного меню выбрать параметр – “Сортировка”.
- Напротив выбранного параметра появится несколько вариантов сортировки данных.
Еще один способ выбора параметра сортировки информации – через основную панель с инструментами. На ней нужно найти вкладку “Данные”, под ней выбрать пункт “Сортировка”. Процесс сортировки таблицы по отдельному столбцу:
- В первую очередь необходимо выделить диапазон данных из одного столбца.
- На панели задач отобразится значок с выбором вариантов сортировки информации. После нажатия по нему откроется список возможных вариантов сортировки.
Если изначально было выделено несколько столбцов из страницы, после нажатия по значку сортировки на панели задач откроется окно с настройками данного действия. Из предложенных вариантов необходимо выбрать параметр “автоматически расширить выделенный диапазон”. Если же не сделать этого, данные в первом столбце будут отсортированы, но общая структура таблицы нарушится. Процесс сортировки строк:
- В окне с настройками сортировки необходимо перейти во вкладку “Параметры”.
- Из открывшегося окна выбрать параметр “Столбцы диапазона”.
- Для сохранения настроек нажать на кнопку “ОК”.
Параметры, которые изначально установлены в настройках сортировки, не позволяют распределить данные по рабочей таблице случайным образом. Для этого необходимо использовать функцию СЛЧИС.
Процедура размножения столбцов по строкам достаточно специфичная, из-за чего не каждый пользователь знает, как ее осуществить. Однако, ознакомившись с представленной выше инструкцией, это можно сделать очень быстро. Дополнительно к этому рекомендуется потренироваться с размножением функций и формул, за счет чего получится экономить большое количество времени при различных расчетах в больших диапазонах ячеек.