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. Диапазон и вычисления в нем).

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

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

Первая в списке стоит Лиза. В сумме у неё получается

∑(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:

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

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

И завершающий штрих:

Шаг 9. Вводим названия диапазонов:

Вообще-то названия диапазонов лучше вводить сразу.

Теперь вы сможете:

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