Excel 11. Транспонирование

Для начала подготовим таблицу для работы. Возьмите таблицу из предшествующего занятия и откройте её.

По окончании этого занятия вы можете:

1. Работать со специальной вставкой из буфера обмена «Транспонирование»
2. Поменять сразу ширину нескольких столбцов
3. Вводить формулы суммирования и произведения с внедрением относительных и абсолютных адресов ячеек

1. Транспонирование данных

Шаг 1. Выделяем спектр А2:В2 и копируем его в буфер обмена:

Шаг 2. Создаем новейший лист с именованием «Отчет»:

Шаг 3. Вводим в ячейку В2 «Цены на курсы».

Шаг 4. В ячейку В3 вставляем содержимое буфера обмена специальной вставкой «Транспонирование» (лента Основная → группа установок Буфер обмена → клавиша выпадающего меню → команда Транспонирование):

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

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

В итоге мы получили такую картину:

Согласитесь, что транспонирование − красивая вещь!

2. Заполняем таблицу отчета

Шаг 1. В ячейке В5 вводим «Количество проведенных курсов»

Шаг 2. В спектре А5:А10 вводим имена менеджеров курсов:

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

Шаг 3. Выделяем столбцы, ширину которых нужно прирастить:

Шаг 4. Подводим курсор к границе имени крайнего выделенного столбца, пока курсор не воспримет вид двойной стрелки, жмем ЛМ и тащим на право:

Очень не увлекайтесь – по другому улетите за границы рабочего окна.

Шаг 5. Делаем Автоподбор высоты (выделяем всю таблицу → лента Основная → группа установок Ячейки с клавиша выпадающего меню Формат → команда Автоподбор высоты):

Всё, таблица стала комфортной для работы!

3. Формулы в таблице отчета

Шаг 1. Суммируем количество проведенных курсов (делаем активной ячейку В11 → лента Основная → группа установок Редактирование → команда Автосумма):

Шаг 2. Распространяем формулу Автосуммы на надлежащие ячейки (подводим курсор к зеленоватому квадратику в правом нижнем углу ячейки В11, пока курсор не воспримет вид темного крестика → жмем ЛМ → перетаскиваем курсор на подходящую ячейку):

Шаг 3. Подсчитываем выручку по любому виду курса, другими словами умножаем количество определенных проведенных курсов на стоимость курса (в ячейку В12 вводим символ «=» → набираем формулу =В11*В4):

При всем этом не забываем, что имена ячеек вводятся в британском регистре. Можно просто отметить вычисляемые ячейки (Excel 9. Формулы).

Шаг 4. Распространяем формулу «=В11*В4» на надлежащие ячейки (подводим курсор к зеленоватому квадратику в правом нижнем углу ячейки В12, пока курсор не воспримет вид темного крестика → жмем ЛМ → перетаскиваем курсор на подходящую ячейку):

Шаг 5. Покажем формулы, которые участвуют в вычислении (лента Формулы → группа установок Зависимости формул → команда Показать формулы):

Адреса ячеек относительные, другими словами производится правило: создать в данной ячейке, как в предшествующей, с соблюдением всех направлений относительно адресов (Excel 10. Спектр и вычисления в нем).

Отменяем режим «Показать формулы», щелкнув по команде «Показать формулы».

Сейчас займемся менеджерами. А сколько средств сделал любой менеджер на продаже курсов наивным пользователям?

1-ая в перечне стоит Лиза. В сумме у неё выходит

∑(5 Оригами+4 Суми-ё+23 Эмбру+7 Росписи+10 Бисероплетения)

=B4*B6+C4*C6+D4*D6+E4*E6+F4*F6

Шаг 6. Подсчитываем количество курсов, организованных каждым менеджером (в ячейку G6 вводим символ «=» → отмечаем попеременно ячейки, при всем этом вводим надлежащие знаки «*» и «+»):

Совершенно-то я схитрила. Я просто скопировала формулу, которую записала выше в рамочке, и вставила в ячейку G6. Попытайтесь повторить мои деяния. Время от времени таковой метод весьма упрощает жизнь.

Если мы скопируем формулу вниз, то получим такую картину:

Сделали мы все верно, но адреса ячеек относительные, потому в ячейке G7 перемножаются курсы, проведенные Аней, на ячейки пятой строки, а там, не считая текста и пустых ячеек ничего нет.

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

Шаг 7. Двойной щелчок ЛМ по ячейке с формулой, формула стала активной. По очереди подводим курсор к адресу ячейки, щелкаем ЛМ и надавливает на клавиатуре F4:

1. F4 – на клавиатуре компа
2. F4+ Fn – на клавиатуре ноутбука

Шаг 8. Копируем формулу по списку менеджеров либо спектру G6: G10:

Если назначим режим «Показать формулы», то увидим:

Адресок ячеек с расценкой курсов не изменяется – это абсолютный адресок ячейки.

Как вставить ссылку и транспонировать функции в Excel

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

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

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

Неувязка приклеивания

Для целей данной статьи представим, что вы желаете переместить столбцы в один лист. Ну так что ты делаешь? Изберите столбцы, нажмите Ctrl + C (Cmd + C на Mac) и изберите пространство предназначения для вставки. Потом нажмите «Вставить», изберите «Особая вставка» и установите флаг перед «Транспонировать».

Но как вы поставите галочку, Paste Link станет сероватым. К счастью, есть некие формулы и приемы, которые посодействуют для вас обойти эту делему. Давайте поглядим на их.

TRANSPOSE — Array Formula

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

Подобные задачи применимы и к остальным формулам массива, и эта поможет для вас решить делему транспонирования ссылок достаточно стремительно.

Скопируйте ячейки и нажмите на верхнюю левую ячейку в области, в которую вы желаете вставить ячейки. Нажмите Ctrl + Alt + V, чтоб открыть окно специальной вставки. Вы также сможете создать это из панели инструментов Excel.

Получив доступ к окну, отметьте «Форматы» под «Вставить», изберите «Транспонировать» в правом нижнем углу и нажмите «ОК». Это действие переносит лишь форматирование, а не значения, и для этого есть две предпосылки. Во-1-х, вы узнаете спектр транспонированных клеток. Во-2-х, вы сохраняете начальный формат ячеек.

Нужно избрать всю область вставки, и вы сможете создать это опосля вставки форматов. Сейчас введите = TRANSPOSE («Начальный спектр») и нажмите Ctrl + Shift + Enter.

Примечание: принципиально надавить Enter вкупе с Ctrl и Shift. В неприятном случае программка не распознает команду верно и автоматом делает фигурные скобки.

Link and Transpose — Ручной способ

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

Изберите ваши ячейки и скопируйте / вставьте их, используя опцию Особая вставка. Сейчас вы не ставите галочку напротив Transpose и оставляете функции под Paste по дефлоту.

Нажмите клавишу Вставить ссылку понизу слева, и ваши данные будут вставлены в виде ссылок.

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

ОФСЕТ Формула

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

Для вас необходимо приготовить числа слева и сверху. К примеру, если есть три строчки, вы будете употреблять 0-2, а если есть два столбца, вы будете употреблять 0-1. Способ — полное количество строк и столбцов минус 1.

Дальше нужно отыскать и найти базисную ячейку. Эта ячейка обязана оставаться постоянной при копировании / вставке, потому вы используете особые знаки для данной ячейки. Допустим, базисная ячейка — B2: для вас необходимо вставить символ бакса, чтоб выделить эту ячейку. В формуле это обязано смотреться так: = OFFSET ($ B $ 2.

Сейчас для вас необходимо найти расстояние (в строчках) меж базисной и мотивированной ячейками. Это число обязано возрастать при перемещении формулы на право. По данной причине перед столбцом функции не обязано быть знака бакса. Заместо этого 1-ая строчка фиксируется со знаком бакса.

К примеру, если функция находится в столбце F, она обязана смотреться последующим образом: = OFFSET ($ B $ 2, F $ 1.

Как и строчки, столбцы также должны возрастать опосля ссылки и транспонирования. Вы также используете символ бакса, чтоб поправить один столбец, но позволяете прирастить строчки. Чтоб прояснить это, идеальнее всего обратиться например, который может смотреться последующим образом: = OFFSET ($ B $ 2, F $ 1, $ E2).

Как Excel в Excel

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

Употребляли ли вы одно из этих приложений транспонирования / связывания для выполнения данной операции в Excel? Вы были удовлетворены результатом? Поделитесь своим опытом в разделе объяснений ниже.

Как транспонировать таблицу в excel 2010?

Работа в Excel часто просит от пользователя определенных особых познаний. Само собой, они приходят с опытом, но что созодать, когда такового опыта мало, и в неких моментах неясно, как поступить? К примеру, часто почти все юзеры недоумевают, когда соображают, что их таблицы в Экселе имеют, мягко говоря, не самый читабельный вид. А именно, часто возникает необходимость поменять ориентацию таблички, поменяв строки на столбцы. К слову, эта функция именуется транспонированием, и в Excel она применяется весьма нередко ведь весьма длительно строить таблицу поновой. Итак, в данном материале я расскажу для вас о том, как транспонировать таблицу в excel 2010.

Метод 1-ый

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

1. Для начала выделите свою таблицу с заблаговременно внесенными в нее данными.
2. Сейчас скопируйте ее в буфер обмена средством композиции кнопок Ctrl+C.
3. Дальше кликните по пустой ячейке, в которой желали бы созидать свою таблицу и нажмите клавишу «Вставить» в меню программки. Покажется контекстная менюшка, где следует избрать строку «Транспонировать».
4. Видите ли, возникла еще одна таблица, в которой столбцы поменялись местами со строками. И все, готовую таблицу скопировать из Excel в Word вы сможете как обычно.

Метод 2-ой

В последующем методе, о котором я расскажу, уже задействуется функция Excel. Вот что необходимо создать: под таблицей с за ранее внесенными данными нарисуйте еще одну. Направьте внимание, что по количеству столбцов и строчек они должны быть равнозначными. Сейчас в поле, куда необходимо ввести формулу, пропишите последующее: =ТРАНСП(А1:Е5), где А1 и Е5 – это границы вашей начальной таблицы с данными. Сейчас нажмите комбинацию [Ctrl]+[Shift]+[Enter]. Готово!

Метод 3-ий

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

Вот так смотрится формула, которую нужно ввести под начальной таблицей: ДВССЫЛ(АДРЕС(СТОЛБЕЦ(А1);СТРОКА(А1))), где в роли А1 выступает ячейка в верхнем левом углу странички. Сейчас, задействовав Маркер наполнения, просто скопируйте формулу на нужное количество столбцов и строчек.

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

Транспонирование VBA (шаг за шагом) | 2 наилучших способа транспонирования в VBA

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

Как транспонировать в VBA?

Переключение строк и столбцов — это один из способов манипулирования данными, который делают практически все юзеры в Excel. Процесс преобразования горизонтальных данных в вертикальные и вертикальных данных в горизонтальные в Excel именуется «транспонированием». Я уверен, что вы должны быть знакомы с транспонированием на обыкновенном листе, в данной статье мы покажем для вас, как употреблять способ транспонирования в кодировке VBA.

Мы можем транспонировать в VBA 2-мя методами.

1. Транспонировать при помощи формулы TRANSPOSE.
2. Транспонировать при помощи специального способа вставки.

При транспонировании мы меняем местами строчки на столбцы и столбцы на строчки. К примеру, если данные находятся в массиве 4 X 3, они стают массивом 3 X 4.

Давайте поглядим на несколько примеров транспонирования столбца в строчку в VBA.

# 1 — Транспонирование VBA с внедрением формулы TRANSPOSE

Подобно тому, как мы используем TRANSPOSE в excel, мы можем употреблять формулу TRANSPOSE и в VBA. У нас нет формулы TRANSPOSE в VBA, потому нам необходимо употреблять класс функции рабочего листа.

Для примера поглядите на изображение данных ниже.

Мы попробуем транспонировать этот массив значений. Сделайте последующие шаги, чтоб транспонировать данные.

Шаг 1. Запустите подпроцедуру

Код:

Шаг 2: Поначалу нам необходимо решить, куда мы собираемся транспонировать данные. В этом я решил переместить из ячейки D1 ​​в H2. Итак, введите код VBA как спектр («D1: H2»). Значение =

Код:

Шаг 3: Сейчас в вышеупомянутом спектре нам необходимо значение спектра от A1 до B5. Чтоб добраться ранее открытого класса «Функция рабочего листа», изберите формулу «Транспонировать».

Шаг 4: В аргументе 1 укажите спектр источника данных, другими словами спектр («A1: D5») .

Код:

Отлично, мы окончили кодирование формулы TRANSPOSE. Сейчас запустите код, чтоб узреть итог в спектре ячеек от D1 до H2.

Как мы лицезрели на изображении выше, он преобразовал спектр ячеек из столбцов в строчки.

# 2 — Транспонирование VBA с внедрением специального способа вставки

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

1-ое, что нам необходимо создать для транспонирования, — это скопировать данные. Потому напишите код как Range («A1: B5»). Скопируйте

Код:

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

Код:

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

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

Игнорируйте все характеристики и изберите крайний параметр, т.е. транспонирование, и сделайте его ИСТИННЫМ.

Код:

Это также перенесет данные, как и в прошлом способе.

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

То, что необходимо уяснить

• Если мы используем функцию рабочего листа TRANSPOSE, нужно непременно высчитать количество строк и столбцов для транспонирования данных. Если у нас есть 5 строк и 3 столбца, то при транспонировании он становится 3 строчками и 5 столбцами.
• Если для вас необходимо такое же форматирование при использовании специальной вставки, вы должны употреблять аргумент типа вставки как «xlPasteFormats»

Вы сможете скачать этот шаблон VBA Transpose Excel тут — Шаблон VBA Transpose Excel