Презентация на тему "Использование функции в формулах"

Содержание

• 
  Слайд 1
  

  Октябрь 2016 Основы работы в Excel Тема 1: Использование функций в формулах

• 
  Слайд 2

  Структура занятия

  Введение в курс Знакомство с функциями рабочего листа Функции, используемые для работы с текстом Функции, используемые для работы с датой и временем Способы суммирования и подсчета данных Способы поиска данных на рабочих листах файла excel Бонусные советы 1 2 3 4 5 6 7

• 
  Слайд 3
  

  Введение в курс Знакомство с функциями рабочего листа Функции, используемые для работы с текстом Функции, используемые для работы с датой и временем Способы суммирования и подсчета данных Способы поиска данных на рабочих листах файла excel Бонусные советы 1 2 3 4 5 6 7

• 
  Слайд 4

  Введение в курсОбласти применения программы Excel

  Работа с числами: бюджетирование, калькуляция затрат, финансовый анализ любых бизнес-процессов); Создание диаграмм (Excel является одним из наиболее простых и эффективных средств визуализации данных); Работа со списками; Работа с текстом; Доступность данных (функционал Excel позволяет выполнять импорт данных из различных источников); Создание графических панелей управления ( создание форм для генераций отчетов и др); Автоматизация трудоемких процессов ( начисление заработной платы, налогов, вычет НДС, выполнение некоторых операций с финансовой отчетностью организации). Ключевой документ Excel – рабочая книга; Размеры рабочего листа (начиная с Excel 2010 – 16384 столбца X 1 048 576 строк);

• 
  Слайд 5

  Введение в курсСправочная система Excel

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

• 
  Слайд 6

  Введение в курсЧисловое форматирование ячеек

  Вкладка «Главная»  Заголовок группы «Число» После клика по значку у заголовка группы «Число» появится следующее окно, которое позволит вам установить как формат числа, так и количество знаков после запятой, разделитель между десятичными разрядами и др.

• 
  Слайд 7

  Введение в курсПрактика: Формат ячеек Excel

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

• 
  Слайд 8

  Введение в курсПрактика: Формат ячеек Excel, решение

  Столбец 1: формат данных (ДД/ММ/ГГГГ); Столбец 2: формат данных (ДД/ММ/ГГ ХХ:ХХ); Столбец 3: формат данных (Числовой); Столбец 5: формат данных (Числовой, 2 знака после запятой) Столбец 7: формат данных (Числовой, использовать десятичный разделитель). Столбец «Таблица»: чтобы заработала формула в соседнем столбце, необходимо сменить формат данных на числовой.

• 
  Слайд 9

  Введение в курсПрактика: Формат ячеек Excel, итог

• 
  Слайд 10

  Введение в курсПрактика:Условное форматирование ячеек

  Необходимо выделить красным количество отметок ниже 4 у студентов. Выделите все оценки  Главная  Условное форматирование  Правила выделения ячеек  Менее чем  4

• 
  Слайд 11
  

  Выделите все оценки  Главная  Условное форматирование  Правила выделения ячеек  Менее чем  4

  Назад

• 
  Слайд 12

  Введение в курсРешение: Условное форматирование ячеек

• 
  Слайд 13

  Введение в курсСпарклайны

  Спарклайн – диаграмма, расположенная в одной ячейке. Спарклайны обычно размещают в нескольких соседних ячейках. Чтобы создать спарклайн, необходимо выделить диапазон данных, по которым вы хотите увидеть график, перейти на окно вкладки «Вставить», и создать спарклайн.

• 
  Слайд 14

  Как создать спарклайн?

  Выделите диапазон данных; Перейдите во вкладку «Вставить», а затем выберите тип интересующего вас спарклайна Далее «выскочит» такое окошко. В верхней строке указывается диапазон данных, по которым строится спарклайн, а в нижнем ячейка, где будет расположен спарклайн Назад

• 
  Слайд 15

  Структура занятия

  Введение в курс Знакомство с функциями рабочего листа Функции, используемые для работы с текстом Функции, используемые для работы с датой и временем Способы суммирования и подсчета данных Способы поиска данных на рабочих листах файла excel Бонусные советы 1 2 3 4 5 6 7

• 
  Слайд 16
  

  Знакомство с функциями рабочего листа5 ключевых элементов, из которых может состоять формула

  Операторы (+, -, *, / и др); Ссылки на ячейки. В данную категорию можно включить именованные ячейки и диапазоны, которые относятся к текущему рабочему листу, ячейкам другого листа текущей книги и ячейкам листа другой рабочей книги; Значения и текстовые строки (прим. 7, 8, «Итоги деятельности» и др.); Функции и аргументы функций (СУММ, СУММЕСЛИ, СТАНДОТКЛОН и др); Скобки (указывают порядок действий в формуле). При вводе и редактировании формул, программа Excel 2013 окрашивает ячейки в разные цвета (в том числе и диапазоны). Это позволяет быстро увидеть ячейки, используемые в формуле.

• 
  Слайд 17
  

  Знакомство с функциями рабочего листаНесколько советов по работе с формулами

  Чтобы увидеть все формулы, которые возможно использовать в программе, необходимо активировать вкладку «Формулы» и и кликнуть на «Вставить функцию». (Либо сочетанием клавиш Shift + F3); При вводе формулы в строке формул, появляется выпадающий список, нажмите клавишуTAB, чтобы программа завершила написание формулы за вас; Если формула слишком длинная или сложна для восприятия, ее можно разбивать сочетанием клавиш Alt+Enter; Длина одной ячейки – 8000 символов; Чтобы отследить ячейки, на которые опирается формула, нажмите F2; Не задавайте жестко закодированных значений: гораздо лучше, если вы сошлетесь на ячейку, где можно заменить значение впоследствии (вкладка «Пример ссылок и имен» (зеленый – до использования ссылки, красный – после использования ссылки). Очевидно, что теперь при изменении ставки НДС достаточно изменить только размер ставки НДС в рабочем листе (Ячейка K1); Использование именованных диапазонов может значительно упростить понимание формул в файле лицами, которые видят документ впервые. Чтобы создать именованнный диапазон нужно:Перейти на вкладку (1)«Формулы», кликнуть на «Менеджер имен», нажать на клавишу (2)«Новое» и (3) заполнить диапазон нужными координатами (либо использовать сочетание клавиш CTRL + F3). Название именованного диапазона пишется без пробелов (вместо пробела используется “_”). Иногда необходимо провести вычисления на активном листе. Нажмите F9 и программа пересчитает все формулы на активном листе.

• 
  Слайд 18

  Несколько советов по работе с формулами

  Вставка функции 1 2 Разбивка функии/текста в ячейке сочетанием клавиш Alt+Enter Назад Создание именованного диапазона 1 2 3 4 5 4 – Название именованного диапазона; 5 – Ссылка на ячейки для именованного диапазона

• 
  Слайд 19

  Абсолютные и относительные ссылки в формулахОбщая информация

  Существует 4 типа ссылок. Отличает их друг от друга знак доллара. Относительные. Ссылка полностью относительна. При коприровании формулы ссыла на ячейку обновляется в соответствии с новыми ячейками. Пример А1; Абсолютные. Ссылка полностью абсолютна. При копировании формулы в другую ячейку ссылка не изменяется. Пример $A$1; Абсолютная в строке. Ссылка частично абсолютна. При копировании формулы изменяется только часть ссылки, относящаяся к столбцу. Та часть, которая относится к строке, остается неизменной. Пример A$1; Абсолютная в столбце. Ссылка частично абсолютна. При копировании формулы изменяется только часть ссылки, относящаяся к строке. Та часть, кторая относится к столбцу, остается неизменной. Пример:$A1.

• 
  Слайд 20

  Откройте вкладку «Абсолютные и относительные ссыл»

  Ячейка A7 при расчете НДСабсолютная; Ячеки, используемые при вычислении суммы относительные; Ячейки, использованные при расчете площади (длина*ширина) абсолютные как в строке, так и в столбце. Назад

• 
  Слайд 21

  Ошибки, которые возвращает программа при использовании формул

  Также вы можете отследить ошибки в формуле при помощи следующего инструмента: Формулы  Зависимости формул  Проверка наличия ошибок  Отследить ошибку.

• 
  Слайд 22

  Структура занятия

  Введение в курс Знакомство с функциями рабочего листа Функции, используемые для работы с текстом Функции, используемые для работы с датой и временем Способы суммирования и подсчета данных Способы поиска данных на рабочих листах файла excel Бонусные советы 1 2 3 4 5 6 7

• 
  Слайд 23

  Ключевые функции и методы для обработки текстовой информации

  СЦЕПИТЬ СЖПРОБЕЛЫ ПЕЧСИМВ ДЛСТР ПОВТОР Добавление символов Изменение регистра Вставка значениями Извлечение символов из строки Замена одного текста другим Поиск в строке Поиск и замена в строке Подсчет символов в ячейке Подсчет слов в ячейке Удаление знаков «минус» 2 забавные вещи, которые стоит знать

• 
  Слайд 24
  

  Функции, используемые для работы с текстом (вкладка «5 функций»)СЦЕПИТЬ

  Объединить несколько ячеек можно несколькими способами.Самый простой способ – используя знак &Синтаксис простой – значение текстовое берется в кавычки, =“Прибыль ”&A1 Функция СЦЕПИТЬ(текстовое значение 1, текстовое значение 2, …., текстовое значение N); Пример использования функций находится на вкладке «Сцепить»; СЖПРОБЕЛЫ(адрес ячейки) Функция удаляет ведущие и замыкающие пробелы, также вместо нескольких пробелов между словами, функция устанавливает один; ПЕЧСИМВ(адрес ячейки) Функция удаляет из строки все непечатаемые символы, которые в импортированном файле были служебными символами; ДЛСТР(адрес ячейки) Функция ДЛСТР определяет количество символов, содержащихся в ячейке; ПОВТОР(“значение для повтора”, количество повторов) Функция ПОВТОР необходима для того, чтобы повторить любую строку текста или символа (первый аргумент функции) заданное количество раз (второй аргумент функции). Назад

• 
  Слайд 25
  

  Функции, используемые для работы с текстом ( вкладка II)Добавление символов

  Чтобы добавить символы в ячейке необходимо: Ссылка на ячейку&ПОВТОР(“*”, 27– ДЛСТР(Ссылка на ячейку))Пример: = А1&ПОВТОР(“*”, 27 – ДЛСТР(А1)); Функция ПРОПИСН() преобразует все символы текста в верхний регистр; Функция СТРОЧН() преобразует весь текст в нижний регистр; Функция ПРОПНАЧ() преобразует первый символ каждого слова в верхний регистр, а все остальные символы в нижний; Вставка значениями. Выделите диапазон скопируйте его (ctrl + C) Выберите место, куда вы хотите вставить таблицу Активируйте меню специальной вставки (CTRL + Alt + V)  Выберите опцию значения. Назад

• 
  Слайд 26
  

  Выделите диапазон скопируйте его (ctrl + C) Выберите место, куда вы хотите вставить таблицу Активируйте меню специальной вставки (CTRL + Alt + V)  Выберите опцию значения.

  Назад

• 
  Слайд 27

  Функции, используемые для работы с текстом ( вкладка II)

  ЛЕВСИМВ(ссылка на ячейку, количество символов для извлечения); ПРАВСИМВ(ссылка на ячейку, количество символов для извлечения); ПСТР(ссылка на ячейку, символ с начала которого нужно извлечь данные, количество символов для извлечения); Есть несколько способов замены текста/чисел в ячейках: Использование диалогового окна Найти и заменить: Главная Редактирование  Найти и выделить  Заменить (CTRL + F). Также данную задачу возможно решить с помощью формул:ПОДСТАВИТЬ(ссылка на текст, заменяемый текст, заменяющий текст) и ЗАМЕНИТЬ(ссылка на текст, номер знака с которого начинается замена, количество заменяемых знаков, заменяющий текст).(Синтакс формул можно увидеть в файле примера); Поиск данных в строке можно выполнить при помощи формул НАЙТИ и ПОИСК.Формула НАЙТИ(ссылка на текст, искомый текст, номер символа с которого начинается поиск в ячейке)ищет вхождение одного текста в другой с учетом регистра;Формула ПОИСК (ссылка на текст, искомый текст, номер символа с которого начинается поиск в ячейке) ищет вхождение одного текста в другой без учета регистра; Результат работы функций НАЙТИ и ПОИСК – номер символа в ячейке, который соответствует требованиям поиска. Назад

• 
  Слайд 28
  

  Подсчет символов в ячейке.Допустим, вам нужно узнать, сколько букв «р» встречается в отдельно взятой ячейке. (откройте файл с примером)=ДЛСТР(A112)-ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A112,"Р",""),"р","")).Функция ПОДСТАВИТЬ заменяет буквы «р» на ничего, чтобы понять, сколько знаков в ячейке станет без интересующего нас символа. Далее мы отнимаем от общего количества символов, количество символов без интересующей нас буквы. И получаем искомое количество. Подсчет слов в ячейке.Выполняется аналогично. =ДЛСТР(A114)-ДЛСТР(ПОДСТАВИТЬ(A114," ",""))+1, однако добавляется единица. В этом случае вместо буквы «р» мы заменяем пробелы (очевидно, что после последнего слова нет пробела, соответственно, и не считается последнее слово). Удаление знаков «минус»1) Использем функцию НАЙТИ, чтобы определить номер позиции символа «-». 2) Номер позиции с символом минус и есть количество всех символов в ячейке. Отнимем единицу, чтобы получить количество символов в ячейке без минуса. Используем функцию ЛЕВСИМВ, чтобы «вытянуть» интересующие нас данные. Назад

• 
  Слайд 29

  2 функции программы при работе с текстом

  Автоматическая разбивка текста на столбцы. Рассмотрим примерна вкладке text to. Нам нужно разбить одну ячейку на три. Вызываем следющую команду: Данные Работа с данными  Текст по столбцам. Разделитель выбираем в виде пробела. Как создать из текста гистограмму:=ПОВТОР(СИМВОЛ(162),B2/1000)(формула находится в файле с примерами на вкладке «Гист).Также необходимо использовать следующий шрифт: Wingdings 2

• 
  Слайд 30
  

  Назад

• 
  Слайд 31

  Структура занятия

  Введение в курс Знакомство с функциями рабочего листа Функции, используемые для работы с текстом Функции, используемые для работы с датой и временем Способы суммирования и подсчета данных Способы поиска данных на рабочих листах файла excel Бонусные советы 1 2 3 4 5 6 7

• 
  Слайд 32

  Функции для работы с датой и временемСамостоятельное изучение

  ДАТА ДАТАЗНАЧ ДЕНЬ ДНЕЙ ДНЕЙ360 ДАТАМЕС КОНЕЦМЕСЯЦА МЕСЯЦ ЧИСТРАБДНИ ТДАТА СЕГОДНЯ ДЕНЬНЕД НОМНЕДЕЛИ РАБДЕНЬ ГОД ДОЛЯГОДА

• 
  Слайд 33

  Структура занятия

  Введение в курс Знакомство с функциями рабочего листа Функции, используемые для работы с текстом Функции, используемые для работы с датой и временем Способы суммирования и подсчета данных Способы поиска данных на рабочих листах файла excel Бонусные советы 1 2 3 4 5 6 7

• 
  Слайд 34

  Способы суммирования и подсчета данныхПрактика

  СУММЕСЛИ СУММЕСЛИМН СЧЕТЕСЛИ СЧЕТЕСЛИМН (диапазон критерия для суммирования, критерий, диапазон суммирования) (диапазон суммирования, диапазон критерия для суммирования, критерий) (диапазон подсчета, критерий подсчета) (диапазон подсчета, критерий подсчета) выполнено суммирование по условию пользователя выполнено суммирование по несколькимусловиям пользователя подсчитаны ячейки по критерию подсчитаны ячейки по критериям Функция Синтаксис Итог СЧИТАТЬПУСТОТЫ (диапазон значений) подсчитаны пустые ячейки СЧЕТЗ (диапазон значений) подсчитаны непустые ячейки

• 
  Слайд 35

  Структура занятия

  Введение в курс Знакомство с функциями рабочего листа Функции, используемые для работы с текстом Функции, используемые для работы с датой и временем Способы суммирования и подсчета данных Способы поиска данных на рабочих листах файла excel Бонусные советы 1 2 3 4 5 6 7

• 
  Слайд 36

  «Горячие клавиши»

• 
  Слайд 37

  Спасибо за внимание!