Конспект-лекции "Транспонирование таблиц Excel".

Урок-лекция:  Транспонирование таблиц

Цель: Учебная - познакомить учащихся с электронной таблицей EXCEL, научить вводить данные в числовой и текстовой информации, правильно записывать формулы, закрепить на примере выполнения практической работы.

Развающие - развитие коммуникативно-технических умений, развитие умения оценивать результаты выполненных действий, развитие умения пользоваться предложенными инструментами.

Воспитательные - воспитывать интерес к учёбе через работу на компьютере.

Тип урока: комбинированный.

Методы: словесно-иллюстративный.

Обеспечение урока: персональный компьютер, мультимедиапроектор, программа Microsoft Excel.

Реализуемые на уроках следующие педагогические технологии:

- технология диалогового обучения;

- визуализация изучаемых объектов с помощью презентационных технологий;

- обучение в сотрудничестве;

- компьютерные технологии;

Должны знать: назначение элементов окна изучаемой электронной таблицы; понятия и определения ячейки, адреса, блока; правила создания, заполнения и сохранения электронной таблицы; методику оформления электронной таблицы;

Должны уметь: вводить информацию и редактировать электронные таблицы; форматировать и оформлять таблицу; сохранять созданную или отредактированную таблицу;

 

 

Три способа как транспонировать таблицу в Excel

Понятие «транспонировать» почти не встречается в работе пользователей ПК. Но тем, кто работает с массивами, будь то матрицы в высшей математике или таблицы в Excel, приходится сталкиваться с этим явлением.

Транспонированием таблицы в Excel называется замена столбцов строками и наоборот. Иными словами – это поворот в двух плоскостях: горизонтальной и вертикальной. Транспонировать таблицы можно тремя способами.

Способ 1. Специальная вставка

Самый простой и универсальный путь. Рассмотрим сразу на примере. Имеем таблицу с ценой некоего товара за штуку и определенным его количеством. Шапка таблицы расположена горизонтально, а данные расположены вертикально соответственно. Стоимость рассчитана по формуле: цена*количество. Для наглядности примера подсветим шапку таблицы зеленым цветом.

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

Чтобы транспонировать таблицу, будем использовать команду СПЕЦИАЛЬНАЯ ВСТАВКА. Действуем по шагам:

1.     Выделяем всю таблицу и копируем ее (CTRL+C).

2.     Ставим курсор в любом месте листа Excel и правой кнопкой вызываем меню.

3.     Кликаем по команде СПЕЦИАЛЬНАЯ ВСТАВКА.

4.     В появившемся окне ставим галочку возле пункта ТРАНСПОНИРОВАТЬ. Остальное оставляем как есть и жмем ОК.

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

Аналогично можно транспонировать только значения, без наименований строк и столбцов. Для этого нужно выделить только массив со значениями и проделать те же действия с командой СПЕЦИАЛЬНАЯ ВСТАВКА.

Способ 2. Функция ТРАНСП в Excel

С появлением СПЕЦИАЛЬНОЙ ВСТАВКИ транспонирование таблицы при помощи команды ТРАНСП почти не используется по причине сложности и большего времени на операцию. Но функция ТРАНСП все же присутствует в Excel, поэтому научимся ею пользоваться.

Снова действует по этапам:

1.     Правильно выделяем диапазон для транспонирования таблицы. В данном примере исходной таблицы имеется 4 столбца и 6 строк. Соответственно мы должны выделить диапазон ячеек в котором будет 6 столбцов и 4 строки. Как показано на рисунке:

2. Сразу заполняем активную ячейку так чтобы не снять выделенную область. Вводим следующую формулу:=ТРАНСП(A1:D6)

3.   Нажимаем CTRL+SHIFT+ENTER. Внимание! Функия ТРАНСП()работает тилько в массиве. Поэтому после ее ввода нужно обязательно нажать комбинацию горячих клавиш CTRL+SHIFT+ENTER для выполнения функции в массиве, а не просто ENTER.

Обратите внимание, что формула не скопировалась. При нажатии на каждую ячейку становится видно, что эта таблица была транспонирована. К тому же все исходное форматирование утеряно. Придется выравнивать и подсвечивать снова. Так же стоит обратить внимание на то что транспонированная таблица привязанная к исходной. Измененные значения исходной таблице автоматически обновляются в транспонированной.

Способ 3. Сводная таблица

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

1.     Создадим сводную таблицу. Для этого выделим исходную таблицу и откроем пункт ВСТАВКА – СВОДНАЯ ТАБЛИЦА.

  Местом, где будет создана сводная таблица, выбираем новый лист.

  В получившемся макете сводных таблиц можем выбрать необходимые пункты и перенести их в нужные поля. Перенесем "продукт" в НАЗВАНИЯ СТОЛБЦОВ, а "цена за шт" в ЗНАЧЕНИЯ.

  Получили сводную таблицу по нужному нам полю. Также команда автоматически подсчитала общий итог.

  Можно убрать галочку у ЦЕНА ЗА ШТ и поставить галочку рядом с ОБЩАЯ СТОИМОСТЬ. И тогда получим сводную таблицу по стоимости товаров, а также опять общий итог.

Обратите внимание, что формула не скопировалась. При нажатии на каждую ячейку становится видно, что эта таблица была транспонирована. К тому же все исходное форматирование утеряно. Придется выравнивать и подсвечивать снова. Так же стоит обратить внимание на то что транспонированная таблица привязанная к исходной. Измененные значения исходной таблице автоматически обновляются в транспонированной.

Способ 3. Сводная таблица

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

1.     Создадим сводную таблицу. Для этого выделим исходную таблицу и откроем пункт ВСТАВКА – СВОДНАЯ ТАБЛИЦА.

  Местом, где будет создана сводная таблица, выбираем новый лист.

  В получившемся макете сводных таблиц можем выбрать необходимые пункты и перенести их в нужные поля. Перенесем "продукт" в НАЗВАНИЯ СТОЛБЦОВ, а "цена за шт" в ЗНАЧЕНИЯ.

  Получили сводную таблицу по нужному нам полю. Также команда автоматически подсчитала общий итог.

  Можно убрать галочку у ЦЕНА ЗА ШТ и поставить галочку рядом с ОБЩАЯ СТОИМОСТЬ. И тогда получим сводную таблицу по стоимости товаров, а также опять общий итог.

Это достаточно своеобразный способ частичного транспонирования, позволяющий заменить столбцы на строки (наоборот – не получится) и дополнительно узнать общую сумму по заданному полю.