Итак, у меня есть следующее требование:
Лист 1
xxxx
YYYY
ZZZZ
Лист 2
1994 xxx
1995 xxx
1996 xxx
1994 YYY
1995 YYY
1996 YYY
1994 ZZZ
1995 ZZZ
1996 ZZZ
По сути, мне нужно скопировать названия компаний с листа 1 и вставить их напротив каждого заданного года, и повторить это для почти 600 компаний.
Есть ли какая-нибудь формула или коды VBA, чтобы это сделать? Я был бы очень признателен за любую помощь
решение1
Выберите список компаний и используйте Данные>Получить и преобразовать данные>Из таблицы/диапазона.
Это откроет Power Query Editor. Вы увидите что-то вроде этого:
Использовать домашнюю страницу>Закрыть и загрузить
Теперь мы собираемся уделить немного времени созданию динамического списка лет между начальным и конечным годом. Это будет полезно, если вы захотите изменить начало и конец позже.
Введите начальный и конечный год в отдельные ячейки рабочей книги.
Выберите начальный год, затем введите имя ячейки, например, start_year, в поле «Имя»:
После того, как вы дали ячейке имя, выберите ее и используйте Данные>Получить и преобразовать данные>Из таблицы/диапазона.
После того как откроется редактор Power Query с одной ячейкой и заголовком столбца Column1, щелкните правой кнопкой мыши эту ячейку и выберите «Детализация»:
Если развернуть панель «Запросы» слева, вы увидите следующее:
Используйте «Главная»>«Закрыть и загрузить в» и выберите «Только создать подключение».
Повторите это для последнего года, чтобы получилось следующее:
Снова «Закрыть и загрузить в» и «Только создать соединение».
Теперь в Excel нажмите Данные>Запросы и соединения. Вы должны увидеть это:
У меня написано «Загружено 50 строк», потому что я создал фиктивные данные с 50 названиями компаний. У вас может быть по-другому.
Щелкните правой кнопкой мыши запрос Table1 и выберите «Изменить». Это снова откроет редактор Power Query.
Теперь перейдите в Home>Advanced Editor. Это должно выглядеть так:
Мы добавим код для создания списка лет.
Добавьте эту строку ко второй строке:
years = List.Numbers(start_year,end_year),
Так:
Теперь нажмите «Готово». Будет казаться, что ничего не изменилось, но не волнуйтесь.
Используйте «Добавить столбец»> «Пользовательский столбец» и настройте его следующим образом:
Когда вы нажмете «ОК», вы увидите это:
Нажмите двойную стрелку в верхней части столбца года и нажмите «Расширить до новых строк».
Теперь вы увидите, что у вас есть строка для каждой комбинации компании и года:
Используйте команду «Главная»>«Закрыть и загрузить», чтобы вернуть файл в рабочую книгу.
Если список ваших компаний или начальный и/или конечный год изменятся, вы можете просто изменить исходные данные и обновить этот запрос.
решение2
Это очень легко сделать. Есть много способов, но следующий — самый простой, который подходит к тому, что вы только что описали.
Подсчитайте, сколько всего существует названий компаний. (Просто нажмите Ctrl-Down Arrowна столбец с ними и посмотрите, какая строка последняя. Используйте первую строку с одной из них, чтобы узнать, сколько всего их существует. Допустим, что это 587 для этого ответа.)
На другом листе выберите столбец и введите первый год, затем скопируйте его вниз, чтобы заполнить в общей сложности 587 ячеек. Допустим, вы начинаете с A1 и заканчиваете в A587. В ячейке A588 введите формулу =A1+1. Скопируйте ее вниз, чтобы заполнить еще 587 ячеек. Сделайте это, скопировав ячейку, затем выделив ее и остальные 587 ячеек (A588:A1174) и вставив. Теперь у вас все 587 ячеек выделены и заполнены формулой. Скопируйте этот блок и нажмите Ctrl-Down Arrow, затем Down Arrowснова, чтобы перейти к следующей пустой ячейке (A1175).
Теперь вставьте снова и перейдите к следующей пустой ячейке. Продолжайте, пока у вас не будет блока ячеек 587 всех лет, которые вы хотите перечислить. Выделите весь столбец записей (возможно, это было 30 лет, поэтому вы выделяете ячейки A1:A17610). Скопируйте всю кучу и Paste|Special|Valuesизмените все это на постоянный текст.
Скопируйте названия компаний 587 и вставьте их в следующий столбец B1:B587. Затем перейдите в следующую пустую ячейку B588 и введите формулу =B1, затем скопируйте ячейку. Перейдите в столбец A и перейдите в самую последнюю заполненную ячейку или просто используйте , F5 GoToчтобы перейти к ячейке B17610 (последняя ячейка с годами — A17610, а вы в любом случае хотите оказаться в столбце B). Как бы вы это ни сделали, доберитесь до B17610. Нажмите , Shift-Up Arrowчтобы закрыть все эти пустые ячейки (саму себя и вплоть до A588, куда это вас приведет... можно вставить поверх A588, потому что это та же самая формула).
После выделения вставьте. Затем выделите все ячейки столбца B, как вы сделали со столбцом A, затем скопируйте и Paste|Special|Valuesсделайте их также постоянным текстом.
Это выглядит всего лишь как час и куча хлопот. На самом деле, я бы ожидал максимум 2 минуты, даже если вы немного повозитесь. А если нет... как бы это ни выглядело при чтении, в реальной жизни это быстро.
Сделанный.