Занятие 2. Расширенный курс Excel

Оглавление

1.        СОЗДАНИЕ ЗАМЕТОК ...................................................................................................................................... 1

2.        ФОРМУЛЫ В EXCEL. ........................................................................................................................................ 2

3.        ЗАКРЕПЛЕНИЕ АДРЕСА ЯЧЕЙКИ В ФОРМУЛЕ. АБСОЛЮТНЫЙ АДРЕС ЯЧЕЙКИ ($) ........... 3

4.        ФУНКЦИЯ РАНГ ................................................................................................................................................ 4

5.        ФОРМАТЫ ДАННЫХ ....................................................................................................................................... 5

6.        УСЛОВНОЕ ФОРМАТИРОВАНИЕ. ............................................................................................................... 7

7.        СОЗДАНИЕ И ФОРМАТИРОВАНИЕ ТАБЛИЦЫ. ФИЛЬТРАЦИЯ И СРЕЗЫ В EXCEL ............... 12

8.        РАБОТА С ФИГУРАМИ, ДОБАВЛЕНИЕ РИСУНКОВ И СХЕМЫ SMARTART В EXCEL .............. 17

9.        КОПИРОВАНИЕ И ПЕРЕНОС СТРОК ТАБЛИЦЫ, СКРЫТИЕ СТРОК И СТОЛБЦОВ. НАЙТИ И

ЗАМЕНИТЬ В EXCEL ............................................................................................................................................... 19

 

 

1. Создание заметок

Бывает, что не только вы работаете с проектом или отчетом в Excel. Иногда вы можете видеть или, наоборот, оставлять заметки в разного вида отчетах. 

Чтобы сделать заметку к ячейке надо:

1.       Становимся в ячейку, щёлкаем правой кнопкой мыши и выбираем команду Создать заметку. Появляется окно, где автоматически проставлено имя собственника заметки. Здесь мы можем написать какую-нибудь заметку, например, "Максим, проверь это число". В ячейке появляется красная метка в правом верхнем углу. Если окно большое или маленькое, мы можем изменить размер окна. Этот комментарий появится при наведении мышкой на эту ячейку.

2.       [Shift + F2] - горячие клавиши для создания заметки.

3.       Чтобы удалить заметку, в контекстном меню ячейки выбираем команду Удалить заметку.

4.       Для работы с разными режимами отображения заметок можно воспользоваться вкладкой Рецензирование.

2. Формулы в Excel. 

Концепция построения формул

Введём в ячейку B4 формулу для подсчета прибыли (продажи минус затраты) за январь месяц:

•      ставим занак =

•      щёлкаем B2

•      ставим знак -

•      щёлкаем B3

•      нажимаем Enter

•      в B4 мы видим результат 20, а встроке формул =B2-B3

------------------------------------------------------------------------------------------

Подсчитаем общее количество продаж (сумму) за 6 месяцев:

1  способ:

•      переходим в нужную ячейку, начинаем набирать = су и в выпадающем списке выбираем функцию СУММ (или кнопку суммы на ленте)   • выделяем диапазон суммирования и нажимаем Enter

2  способ:

•      выделяем ячейки с данными, а также пустую ячейку, где надо получить результат:

•      нажимаем кнопку автосуммы и получаем правильный результат.

3  способ:

•      Используем горячие клавиши [Alt + =] - сумма

---------------------------------------------------------------------------------

Подсчитаем среднее количество продаж за 6 месяцев:

1  способ:

•      набираем = , щёлкаем H2, далее /6 и нажимаем Enter

2  способ:

•      начинаем набирать = ср и в выпадающем списке выбираем функцию СРЗНАЧ (или в верхнем меню выбираем нужную функцию):

•      выделяем диапазон нужных чисел и нажимаем Enter

Дале, используя функцию Автозаполнения, заполним прибыль по всем месяцам, итоги и среднее:

Программа автоматически считает результат, меняя в формуле номер строки при копировании формулы вниз и имя столбца при копировании формулы вправо:

ЗАДАНИЕ 1: Оформите таблицу 1_Формулы.xlsx на листе Лист1 в соответствии с образцом на листе Образец. Постройте диаграммы.

3.  Закрепление адреса ячейки в формуле. Абсолютный адрес ячейки

($) 

В столбце F нам надо пересчитать зарплату, увеличив её на 4,5%, а точнее на процент, указанный в ячейке H1.

Нам необходимо закрепить ячейку H1 в нашей формуле с помощью значка $ перед номером строки, чтобы при копировании формулы вниз не менялся номер строки. Мы делаем это с помощью клавиши F4.

Формула в ячейке F2 будет выглядеть следующим образом: =E2+E2*H$1

Скопируем формулу в остальные ячейки с помощью маркера автозаполнения. Получим следующий результат:

Весь набор функций Excel расположен во вкладке ФОРМУЛЫ, где они разделены по разделам:

финансовые, текстовые, дата\время, математические и другие функции. 

4. Функция РАНГ

В таблице 2_Расчет повышения зарплаты.xlsx создадим ещё один столбец Рейтинг:

•      в G1 напишем РЕЙТИНГ (или места по баллам, например)

•      в G3 введем формулу: = ра и выберем функцию РАНГ

•      появится подсказка РАНГ(число; ссылка; [порядок])

•      щёлкаем F2, ставим ;

•      затем можно выделить весь столбец F и нажать Enter. Затем заполнить этой формулой остальные ячейки. У нас получится:

ЗАДАНИЕ 2:

Добавьте первую строку и напишите в A1 РАСЧЁТ ПОВЫШЕНИЯ ЗАРАБОТНОЙ ПЛАТЫ.

Далее выделяем нужный диапазон до конца таблицы и нажимаем кнопку Объединить и поместить в Центре. Пробуем разместить надписи в шапке вертикально. И оформить таблицу по образцу (рамки, выравнивание, заливка).

5. Форматы данных

При копировании через строку формул содержимого ячейки I18 в B3 мы получаем число в Общем формате, такое бывает:

Откроем список форматов и выберем Числовой. Затем уберём два знака после запятой,

Теперь давайте выделим столбец С и поработаем с телефонными номерами. Преобразуем эти числа в более привычный для нас вид. 

Горячие клавиши: [Ctrl+1] - откроем панель Формат ячеек (или через ленту). Далее выбираем Дополнительный и Номер телефона. Нажимаем ОК.

Далее выделим колонку F с заработной платой. Здесь у на тоже числа.  Проставим рубли. И уберём копейки.

Далее выделим колонку D с датой. В Excel есть множество формат дат.  Нажмём [Ctrl+1]. Выберем полный формат даты

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

Выделим столбец F с зарплатой и рассмотрим первую функцию Правила выделения ячеек \ Больше...:

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

Можно выбрать Пользовательский формат и настроить его по нашему желанию.

Установленное правило остаётся активным для всей колонки. Если мы изменим зарплату в другой ячейке под наше условие, то она станет выделенной. (попробуем)

Чтобы удалить Условное форматирование, выделяем столбец F, Условное форматирование - Удалить правила - Удалить правила из выделенных ячеек:

Рассмотрим, что ещё есть в Условном форматировании:

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

или Цветовую шкалу. Применение цветного градиента к диапазону ячеек. Оттенок цвета зависит от относительной величины значения по сравнению с другими ячейками в диапазоне:

Ну и последнее, это Наборы значков:

И ещё, если мы хотим отобразить только, например, Гистограммы без чисел, то мы идём Условное форматирование - Гистограммы - Другие правила  и ставим галочку Показать только столбец:

 

********************************************************************************

7. Создание и форматирование таблицы. Фильтрация и срезы в Excel

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

Для работы в этом режиме данные не должны содержать пустых строк и столбцов.

Чтобы форматировать наши данные как таблицу есть 3 способа. Предварительно надо выделить всю таблицу Ctrl+A или встать в любую ячейку и далее:

•      На ленте Главная - Форматировать как таблицу

•

•      На ленте Вставка - Таблица Горячие клавиши Ctrl+T

В верхнем меню появилась новая вкладка Конструктор.

Рассмотрим некоторые преимущества такого форматирования в режиме Таблица:

•      Автоматически появилась функция Фильтра в заголовках столбцов

•      Раскрыв список Стили таблиц, мы можем выбрать другой дизайн - стиль таблицы (пробуем)

•      На вкладке Параметры стилей таблиц мы можем выделить сплошным цветом Первый или Последний столбец, поставив галочки или Убрать чередование строк или поставить Чередующиеся столбцы. Можно убрать и функцию Фильтра. (пробуем)

•      Добавим в конец новую строку (Иванов Иван) и новый столбец (в I1 - Новая зарплата). Новые строки и новые столбцы автоматически берут стиль таблицы. 

•      Теперь поработаем с формулами в этом режиме. В K1 напишем 3,5%.  

•      В ячейке I2 подсчитаем новую зарплату по известной нам уже формуле и не забудем заморозить ячейку K$1. В результате формула сама распространилась на все ячейки - тоже очень удобно.

•      Далее, поставим галочку Строка итогов.  Excel автоматически показывает сумму в конце нашей таблицы (тоже удобно!). 

 

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

•      Срезы в таблице.

Теперь, самое интересное в работе с такими таблицами, что сделает вас более уникальным пользователем

Excel.

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

Мы уже говорили, что можно использовать функцию Фильтра в заголовках столбцов или использовать функцию Вставить срез, которую мы видим на ленте Конструктор таблиц. 

Нажмём на эту иконку. Открылось окно Вставка срезов - это наши столбцы. Здесь мы можем выбрать те параметры, которые мы хотим отслеживать. Например, мы хотим отслеживать Отделы и Статус - поставили галочки и жмём Ок. У нас появилось 2 окна!  

Перемести их вправо от таблицы, чтобы лучше было видно. 

У нас появилась новая вкладка Срез. Здесь мы можем задать цвет (стиль) каждого среза.

Теперь самое полезное!!! Например, мы хотим, чтобы в таблице отображались сотрудники, которые работают полный рабочий день. Жмём на кнопку Полный день в срезе Статус и результат видим в таблице:

Теперь посмотрим сотрудников, работающих по Контракту (Жмём на кнопку Контракт в срезе Статус):

Итоги подсчитываются на основе видимой области.

Чтобы видеть и тех, и других, жмём на соответствующие кнопки вместе с Ctrl:

Теперь поработаем с отделами.  Сначала посмотрим на Отдел Закупок и мы увидим, что здесь работают сотрудники все на полную ставку. А на Логистики работают и те, и другие, и третьи:

 

Можно добавить ещё один параметр: идём в Конструктор - Вставить срез и, например, выбираем Здание.

Так в чём же здесь основное отличие от простого фильтра, который мы видим вверху нв каждом столбце?

Дело в том, что работая со срезами, мы контролируем все эти параметры и у нас всё перед глазами. Поэтому Срезы очень удобно использовать в массивных таблицах. 

Если 1-2 столбца, то удобно использовать встроенный фильтр.

=======================================================================================

8. Работа с фигурами, добавление рисунков и схемы SmartArt в Excel

Откроем Вставка - Иллюстрации - Фигуры, выберем прямоугольник и нарисуем его под таблицей. Появилась вкладка Формат фигуры. Порисуем немного:

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

Далее вставляем рисунок (например логотип) из файла:

Удаляем фон из логотипа:

В Excel есть и другие параметры редактирования картинок: обрезать, добавить контраст, эффекты, стили и др.

Откроем Вставка - Иллюстрации - SmartArt

Выберем в разделе Иерархия первую фигуру. Размер текста автоматически подгоняется под фигуру.

Можно добавлять фигуры к этой схеме,  менять стиль и др.:

===============================================================================

9. Копирование и перенос строк таблицы, скрытие строк и столбцов. Найти и заменить в Excel

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

1.  Вставка пустой строки в основную таблицу, не затрагивая таблицу справа.

Например, после 3-ей строки (Иванова) нам надо вставить ещё одного сотрудника. Если мы с вами вставим пустую строку, то появятся разрывы в таблице справа.

Давайте попробуем по-другому. 

Выделяем в строке те ячейки, перед которыми надо вставить  пустую строку и в контекстном меню выбираем команду Вставить - Строки таблицы выше (в обычной таблице Вставить - Ячейки со сдвигом вниз).

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

2.  Поменять местами две строки:

Например, нам надо поменять местами 2 строки: 5-ую и 6-ую.

Есть простой способ сделать это!

•       выделяем ячейки 5-ой строки (мы не хотим тревожить таблицу справа!)

•       берёмся за нижний край выделенной области и перетаскиваем этот диапазон на строку 6 (в обычной таблице перетаскиваем с нажатой клавишей Shift).

Можно перемещать ячейки строки со сдвигом в любое место и любое количество!

Например, выделим ячейки в 5, 6, 7 строках и вставим их перед 15.

3.  Быстрый метод копирования данных или таблицы.

Это вы уже знаете, но повторим: выделяем ячейку, диапазон или всю таблицу и за нижнюю границу перемещаем объект в нужное место. Если мы при этом будем держать нажатой клавишу Ctrl, то мы получим копию объекта.

4.  Скрытие строк и столбцов.

Чаще всего надо скрыть строки или столбцы перед печатью или временно скрыть по какой-то другой причине.

Выделяем, например, столбцы E и F и в контекстном меню выбираем команду Скрыть. Здесь и при печати мы их не увидим.

Чтобы показать скрытые столбцы: выделяем два столбца, между которыми они скрыты, и щёлкаем правой кнопкой мыши и выбираем Отобразить. Или двойной щелчок вверху на границе этих двух столбцов.

Можно скрывать и отдельно стоящие столбцы, если при выделении зажать Ctrl.

Аналогично можно скрывать и строки.

Если у вас в таблице множество скрытых строк и столбцов, то показать всё можно так:

•       выделить весь лист (щёлкнув по углу таблицы)

•       затем правой кнопкой мыши, например, на столбце A - Отобразить, и на строке 1 - Отобразить. 5. Поиск и Замена 

Заменим Новосибирска на Хабаровск:

•       на Главной нажимаем на значке поиска (горячие клавиши [Ctrl+F]) и выбираем Заменить

 

•       Откроется окно: пишем что и на что заменить:

 

•       Нажимаем Заменить все и получаем сообщение сколько ячеек поменяли значение.

Примечание: если мы выделим, например, столбец, то замена будет в нём.

Задание: заменить Неполный день на Контракт. При этом замененные ячейки выделить жёлтым цветом. А те, которые были раньше Контракт не выделять.