Обзор полезных функций и приемов в Excel
-
Upload
tatyana-soroka -
Category
Documents
-
view
1.238 -
download
6
description
Transcript of Обзор полезных функций и приемов в Excel
![Page 1: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/1.jpg)
Обзор полезных функций и приемовОбзор полезных функций и приемов
28 февраля 2011, Селиванов Ф.
ExcelExcel
![Page 2: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/2.jpg)
Цели
Обзор продвинутых функций и приемов Excel
Примеры их практического применения
Ресурсы для самостоятельного изучения
К ПЛАНУ
![Page 3: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/3.jpg)
ПланЦели
Инструменты Правка Функции Форматирование Работа с данными Ресурсы по Excel
![Page 4: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/4.jpg)
Инструменты
Пользовательские панели инструментов
Защита данных
Закрепление ячеек
Сочетания клавиш
К ПЛАНУ
![Page 5: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/5.jpg)
Инструменты 1 из 4 – Пользовательские панели инструм-в
Что это
Персонализ-й набор комманд и\или меню в виде кнопок
Чем полезно
Экономит время при выполнении типичных операций
Оптимизирует рабочее пространство
Как воспользоваться
1. Home Customize…
2. Перейти на закладку Commands
3. Перетащить необходимые кнопки\меню на
новую панель инструментов
К ПЛАНУ
![Page 6: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/6.jpg)
Инструменты3 из 4 - Закрепление ячеек
Что это
Блокировка определенной области от прокрутки
Чем полезно
Экономия времени при работе с большими таблицами, в которых необходимо
постоянно видеть заголовки колонок \ столбцов
Как воспользоваться
1. Перейти на необходимый лист книги
2. Выбрать необходимую ячейку. Будет заблокированы
все яычейки, находящиеся слева и сверху от нее.
3. View Freeze Panes
4. Для снятия блока выбрать
View Unfreeze Panes
К ПЛАНУ
![Page 7: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/7.jpg)
Инструменты4 из 4 - Сочетания клавиш
К ПЛАНУ
Клавиша Shift Ctrl Alt Shift + Ctrl
F1 Вызов справки О чем справкаВставить лист с
диаграммой
F2 Редактировать ячейкуРедактировать
примечаниеСохранить как
F3 Paste Name Formula Вставить функциюПрисвоить имя
диапазону
Создать имена диапазонов, используя
соседние ячейки
F4 Повторить последнее действие
Найти еще... Закрыть окно Выйти из Excel Find Previous
F5 Перейти к... Найти...Восстановить размер
окна
F6 Перейти к следующей книге
Перейти к предыдущей книге
F7 Проверка правописания
Двигать окно
F8 режим выделения ячеек
добавить к выделению
Изменить размеры окна
Список макросов
F9 Пересчет книги Пересчет листа Свернуть Книгу
F10 Войти в меню Контекстное меню Развернуть окно
C Копировать в Буфер
… … … … … …
подробная таблица двойной клик на иконке Microsoft Excel
Worksheet
![Page 8: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/8.jpg)
ПланЦели Инструменты
Правка Функции Форматирование Работа с данными Ресурсы по Excel
![Page 9: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/9.jpg)
Правка
Автоматическая замена данных
Специальная вставка
К ПЛАНУ
![Page 10: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/10.jpg)
Правка1 из 5 - Автоматическая замена данных
Что это
Автоматический поиск и замена данных
Чем полезно
Экономия времени на замену данных (напр. #N\A на « »)
Как воспользоваться
1. Edit Replace… (или нажать одновременно Ctrl+H )
2. В поле Find what: ввести искомое значение или текст; в меню Format… задать
форматирование искомых данных (необязательно)
3. В поле Replace with: ввести значение, на которое необходимо сделать замену; в меню
Format… задать форматирование заменяемых данных
4. Нажать Replace для поштучной замены
5. Нажать Replace All для замены всех искомых
значений в области
К ПЛАНУ
![Page 11: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/11.jpg)
Правка2 из 5 - Специальная вставка
Что это
Выборочная вставка данных, атрибутов копируемой области либо их сочетания
Чем полезно
Позволяет копировать формат, данные, комментарии, проверку данных; выполнять математические
операции с значениями копируемой и вставочной областей; транспонирование данных
Как воспользоваться
1. Выделить ячейку или диапазон для копирования
2. Edit Copy / Cut
3. Выделить ячейку или диапазон, куда будет
осуществляться копирование
4. Edit Paste Special…
5. Выбрать необходимый атрибут\действие для
копирования или выполнения операции (см. след. слайд)
5. Поставить галочку Skip Blanks для
пропуска пустых ячеек (опционально)
5. Поставить галочку Transpose для транспонирования
копируемого диапазона (опционально)К ПЛАНУ
![Page 12: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/12.jpg)
Правка5 из 5 - Специальная вставка
Paste
All Вставка всех данных и атрибутов ячейки или диапазона
Formulas Вставка только формул
Values Вставка только значений
Formats Вставка только форматов
Comments Вставка только комментариев
Validation Вставка только проверки вводимых значений
All except borders Вставка всех данных и атрибутов ячейки или диапазона, кроме границ
Column widths Вставка ширины колонок
Formulas and number formats
Вставка только формул и форматов чисел
Values and number formats
Вставка только значений и форматов чисел
Operation
None Нет
Add Сложить коп-ое значение с ячейкой \диап-м, в которую\ый делается копирование
Subtract Отнять -- \\ --
Multiply Умножить -- \\ --
Divide Разделить -- \\ --
Paste link Вставить ссылку на копируемую ячейку \ диапазон К ПЛАНУ
![Page 13: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/13.jpg)
ПланЦели Инструменты Правка
Функции Форматирование Работа с данными Ресурсы по Excel
![Page 14: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/14.jpg)
Функции
Абсолютная ссылка
Авто подстановка значений (vlookup, ВПР)
Логические и инфо функции
Текстовые функции
К ПЛАНУ
![Page 15: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/15.jpg)
Функции1 из 6 – Абсолютная ссылка
Что это
Ссылка, у которой ячейка, на которую она ссылается, не меняется при автозаполнении
Чем полезно
Можно варьировать результат автозаполнения ячеек формулами
Как воспользоваться
При написании в формуле ссылки на ячейку \ диапазон поставить знак $
- перед идентификатором столбца для блокировки ссылки только по ячейкам данного
столбца
- перед идентификатором строки для блокировки ссылки только ячейкам данной
Пусть ячейке В1 прописана формула “=А1”.
- тогда, при автозаполнении А1 вниз по столбцу А, получим, что В2=А2, В3=А3 и т.д.
- при автозаполнении В1 вправо по строке 1, получим, что С1=В1, D1=C1 и т.д.
Если формула будет =$A$1, то
- при автозаполнении по строкам и\или столбцам, ячейки В2, В3 ... Вn, C1, D1 и т.д.
всегда будут ссылаться на ячейку A1
К ПЛАНУ
![Page 16: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/16.jpg)
Функции2 из 6 – Функция VLOOKUP (ВПР)
Что это
Формула, позволяющие осуществлять автоматическую подстановку данных из таблицы.
Функция ищет значение заданной ячейки Y(x;n) (x – буква колонки, n – номер строки) таблици А в
крайней левой колонке таблици В. Если значение в таблице В равно значению Y(x;n), то функция
возвращает значение К-той ячейки в данной строке (К – номер колонки в таблице В, из которой
берется результат).
Для правильной работы таблица В должна быть отсортирована в возрастающем порядке по Для правильной работы таблица В должна быть отсортирована в возрастающем порядке по
колонке, в которой будет производиться поиск!колонке, в которой будет производиться поиск!
Чем полезно
Автоматизация поиска данных и
автозаполнения таблиц
К ПЛАНУ
![Page 17: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/17.jpg)
Функции3 из 6 – Функция VLOOKUP (ВПР)
Как воспользоваться
1. Отсортировать таблицу В по столбцу, в которой будет производиться поиск (см. Работа с
данными Сортировка)
2. Сделать столбец таблици В, в котором будет производиться поиск, в крайним левым
в массиве поиска
3. Перейти в таблицу А
4. Insert Function… VLOOKUP (или ВПР)
5. Lookup_value – ввести искомую ячейку из
таблици А
6. Table_array – выделить массив таблици В,
в котором будет происводиться поиск.
7. Col_index_num – поставить число от 1 до N.
Число определяет порядковый номер столбца,
из которого будет браться результат
8. Range_lookup – поставить True для поиска
точного соответствия. False – для поиска
ближайшего значения к искомому.К ПЛАНУ
подробный пример двойной клик на иконке Microsoft Word
Document
![Page 18: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/18.jpg)
Функции4 из 6 – Логические и инфо функции
К ПЛАНУ
Что это
Функции, делающие определенный логический тест и возвращающие значения TRUE или FALSE, либо
выполняющие действие на основании теста
Как воспользоваться
IF(логический тест; значение, если TRUE; значение, если FALSE) – выполнение операций на основании
логического теста - ЕСЛИ
AND(аргумент1; аргумент2; ...) – возвращает TRUE, если все аргументы = TRUE - И
OR(аргумент1; аргумент2; ...) – возвращает TRUE, если хотя бы один из аргументов = TRUE - ИЛИ
ISERROR – возвращает TRUE, если результат функции – ошибка - ЕОШИБКА
ISNA - возвращает TRUE, если результат функции - #N/A - ЕНД
ISEVEN - возвращает TRUE, если число в ячейке четное - ЕЧЁТН
ISODD - возвращает TRUE, если число в ячейке нечетное - ЕНЕЧЁТ
ISBLANK - возвращает TRUE, если ячейка пустая - ЕПУСТО
ISNUMBER - возвращает TRUE, если в ячейке числовой формат данных - ЕЧИСЛО
ISTEXT - возвращает TRUE, если в ячейке - ЕТЕКСТ
![Page 19: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/19.jpg)
Функции5 из 6 – Текстовые функции
К ПЛАНУ
Что это
Функции, работающие с данными ячейки как с текстовыми строками. Позполяют осуществлять поиск
заданных значений в текстовых строках, складывать текст-е строки, вырезать фрагменты текста и т.д.
Как воспользоваться
CONCATENATE – складывает заданные текстовые строки - СЦЕПИТЬ
TRIM – возвращает текст заданной ячейки без пробелов - СЖПРОБЕЛЫ
FIND(текст; ячейка поиска; номер начального символа поиска) – возвращает номер символа в
строке поиска, с которого начинается искомый текст. Чувствителен к регистру. - НАЙТИ
LEN – возвращает длину текстовой строки в ячейке - ДЛСТР
LEFT(ячейка; количество символов) – возвр-т заданное количество символов текст. Строки слева -
ЛЕВСИМВ
RIGHT – аналогична LEFT; возвращает правые символы - ПРАВСИМВ
MID(ячейка; номер начального символа; количество символов) – возвращает заданное кол-во
символов, начиная с заданного символа текстовой строки - ПСТР
PROPER – делает первые буквы каждого слова текстовой строки ячейки заглавными -ПРОПНАЧ
![Page 20: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/20.jpg)
ПланЦели Инструменты Правка Функции
Форматирование Работа с данными Ресурсы по Excel
![Page 21: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/21.jpg)
Форматирование1 из 1 – Условное форматирование
Что это
Автоматическое изменение шрифта, заливки и границ, в зависимости от условия – значение ячейки,
либо формулы. Позволяет задать до 3 условий
Как воспользоваться
1. Выделить ячейку либо диапазон
2. Format Conditional formatting
3. Поставить Cell Value is, если форматирование
ячейки должно зависеть от ее же значения
4. Задать условие (значение больше, меньше,
равно и т.д.)
5. Или выбрать Formula Is… для задания условия
формулой
5. Нажать Add... для добавления нового условия
К ПЛАНУ
![Page 22: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/22.jpg)
ПланЦели Инструменты Правка Функции Форматирование
Работа с данными Ресурсы по Excel
![Page 23: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/23.jpg)
Работа с данными
Группировка столбцов\строк
Сортировка
Вычисление промежуточных значений
Сводные таблицы
Отображение условий автофильтра
Выпадающий список
Разбиение одной колонки на несколько
К ПЛАНУ
![Page 24: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/24.jpg)
Работа с данными1 из 7 – Группировка столбцов\строк
Что это
Группировка строк либо столбцов, которые часто необходимо делать скрытыми
Чем полезно
Доплонительное удобство при работе с большими таблицами. Промежуточные, либо
вспомогательные данные можно скрывать\отображать по нажатии специальных кнопок
Как воспользоваться
1. Выделить строки либо столбцы,
которые необходимо скрыть
2. Data Group and outline Group…
3. Для снятия группировки выделить
необходимые строки\столбцы и выбрать
Data Group and outline Ungroup…
К ПЛАНУ
![Page 25: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/25.jpg)
Работа с данными1 из 7 – Группировка столбцов\строк
![Page 26: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/26.jpg)
Работа с данными3 из 7 – Сводные таблицы
К ПЛАНУ
подробный пример двойной клик на иконке Microsoft Word
Document
Что это
Инструмент, позволяющий из данных, представленных списком с заголовками, строить таблици и диаграмы с заданными параметрами
Чем полезно
Один из мощнейших инструментов excel – автоматизированное построение таблиц и графиков в нужном формате.
Как воспользоваться
См. подробный пример ниже
![Page 27: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/27.jpg)
Работа с данными6 из 7 – Выпадающий список
Что это
При клике на ячейку появляется список значений для ввода
Чем полезно
Упрощение ввода повторяющихся значений
Контроль вводимых значений
Как воспользоваться
1. Выделите ячейки, в которых должен
быть раскрывающийся список
2. Data Validation
3. В поле Source выделите ячейки с данными,
которые должны попасть в выпадающий список
4. Поставьте галочку Ignore blank для пропуска
пустых значений в списке
5. Нажмите Ок
К ПЛАНУ
подробный пример двойной клик на иконке Microsoft Word
Document
![Page 28: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/28.jpg)
Работа с данными7 из 7 – Разбиение текста на ячейки
Что это
Разбиение текстовой строки в одном столбце на несколько
Чем полезно
Экономия времени. Напр. разбить ФИО из одной колонки отдельно на имя, отчество и фамилию
Как воспользоваться
1. Выделить столбец
2. Data Text to Columns…
3. Выбрать:
Delimited – для разбиения
по знакам препинания
Fixed width – для разбиения
по длине
4. Выбрать необходимые разделители,
либо длину разделения
5. Нажать Finish
К ПЛАНУ Microsoft Word
Document
подробный пример двойной клик на иконке
![Page 29: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/29.jpg)
ПланЦели Инструменты Правка Функции Форматирование Работа с данными
Ресурс ы по Ex lce
![Page 30: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/30.jpg)
Ресурсы по Excel
1. Встроенный Help
Help Microsoft Power Point Help
2. Онлайн
www.planetaexcel.ru
msexcel.ru
www.firststeps.ru
www.office.microsoft.com/ru-ru/
training/
3. Книги и коллеги :)
К ПЛАНУ
![Page 31: Обзор полезных функций и приемов в Excel](https://reader033.fdocument.pub/reader033/viewer/2022061114/54623308af79598a3d8b4dae/html5/thumbnails/31.jpg)