Трюк №16. Проверка данных на основе списка на другом листе Excel

При проверке данных легко можно указать правила, которым должны отвечать данные. К сожалению, Excel настаивает, что списки, которые используются при проверке, должны находиться на том же рабочем листе, что и проверяемые данные. К счастью, всегда есть способы уклониться от выполнения этого требования.

В этом трюке мы познакомим вас с двумя способами проверки данных на основе списка на другом листе. Первый из них использует преимущество именованных диапазонов Excel (которые подробнее рассматриваются в главе 3), а во втором применяется вызов функции.

Способ 1. Именованные диапазоны

Возможно, самый простой и быстрый способ преодолеть барьеры, которые воздвигает Excel при проверке данных, — присвоить имя диапазону, где содержится список. Для создания именованного диапазона выделите ячейки, содержащие список, и введите имя в поле имени строки формул. Для выполнения этого примера мы будем предполагать, что диапазону присвоено имя MyRange.

Выделите ячейку, в которой должен будет появиться раскрывающийся список, а затем выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =MyRange . Щелкните на кнопке ОК. Поскольку вы использовали именованный диапазон, ваш список (хотя он и находится на другом листе) теперь можно использовать как список проверки.

Способ 2. Функция ДВССЫЛ

Функция ДВССЫЛ (INDIRECT) позволяет ссылаться на ячейку, содержащую текст, представляющий адрес ячейки. Эту ячейку можно использовать как локальную ссылку, даже если она получает данные из другого листа. Можно применять эту возможность для связи с листом, где расположен список.

Предположим, список находится на листе Sheetl в диапазоне $А$1:$А$8 . Щелкните любую ячейку на другом листе, где должен появиться этот список проверки (список выборки). Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующий код: =INDIRECT(«Sheetl!$А$1:$А$8») , в русской версии Excel =ДВССЫЛ(«Sheetl!$A$1:$A$8») . Удостоверьтесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl должен появиться в раскрывающемся списке проверки.

Если имя листа, на котором расположен список, содержит пробелы, необходимо использовать следующий синтаксис функции ДВССЫЛ (INDIRECT): =INDIRECT(«‘Sheetl’!$А$1:$А$8») , в русской версии Excel =ДВССЫЛ(«‘Sheetl’!$А$1:$А$8») . Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком.

Преимущества и недостатки обоих способов

У именованных диапазонов и функции ДВССЫЛ (INDIRECT) есть преимущества и недостатки. Преимущество использования именованного диапазона заключается в том, что изменение названия листа не повлияет на список проверки. Это подчеркивает недостаток функции ДВССЫЛ (INDIRECT): любое изменение названия листа не будет автоматически в ней отражаться. Преимущество функции ДВССЫЛ (INDIRECT): когда из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF! . В этом недостаток именованного диапазона — если удалить из него ячейки или строки, изменения не повлияют на список проверки.

В. Отображение комментария, если ячейка является текущей.

Используйте вкладку Сообщение для вывода , чтобы отображать комментарий.

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

Excell — как закрыть для пользователей доступ к другим листам

Не единожды видел, как помогали по таким вопросам. Надеюсь и мне кто-то сможет 🙂

Есть простая гугл-ексель таблица. В ней 5 вкладок. Необходимо, что бы другие люди имели возможность смотреть-редактировать только свою вкладку. А я, соответственно, имел доступ ко всем.

Я не прошу за руцю мне что-то настраивать. Буду благодарен, если у кого-то есть под рукой (или возможность написать) как это сделать. Ищу пока такой функционал самостоятельно — но все безуспешно. Все пишут или как ограничить редактирование, или как скрыть в принципе от всех отдельную вкладку.

  1. Введите данные проверки данных
  2. Настроить входное сообщение
  3. Настроить предупреждение об ошибке

Одним из наиболее распространенных способов использования проверки данных является создание раскрывающегося списка.

1) Введите данные проверки данных

Откройте файл Microsoft Excel, выберите ячейки, которые вы хотите ограничить.

Перейдите на вкладку «Дата» в меню ленты.

Под «DatTools» выберите опцию «DatValidation».

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

В случае нашего примера, давайте выберем «Время», чтобы выбрать время, когда мы открыты.

Укажите время начала и время окончания.

2) Настроить входное сообщение

Теперь перейдите на вкладку «Входное сообщение».

Настройте входное сообщение, которое вы хотели бы отображать, когда вводится что-то отличное от того, что было указано.

3) Настроить предупреждение об ошибке

Перейдите на вкладку «Предупреждение об ошибке».

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

В конце нажмите кнопку «ОК».

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

Однако, когда введенное время выходит за пределы диапазона, отображается сообщение об ошибке.

Короче говоря, Datvalidation может отображать сообщение пользователю, сообщая ему, что разрешено, а что нет!

Читать дальше: Как записать макрос для создания кнопки быстрого доступа для нескольких форматов в Word и Excel.

Используем проверку данных

На практике желательно сделать так, чтобы пользователь гарантированно вводил правильное название ковра и его размер. Вы можете реализовать это, используя выпадающий список. Для этого выделите ячейки, в которые пользователь будет вводить свои заказы, например, столбец A или B. Перейдите Data > Data Validation > Data Validation (Данные > Проверка данных > Проверка данных). В появившемся диалоговом окне на вкладке Settings (Параметры) в поле Allow (Тип данных) выберите значение List (Список). Кликните в поле Source (Источник) и выделите ячейки от A2 до A4, в которых содержится список с названиями ковров. Нажмите ОК.

Поиск в Excel и функция ВПР

Таким же способом Вы можете создать выпадающий список для ввода размеров L или M, используя в качестве источника данных диапазон B1:C1.

Теперь, когда пользователи будут выбирать ковер, они смогут указать нужные параметры из раскрывающихся списков. Это гарантирует, что название будет указано без ошибок, так как всегда будет выбрана одна из присутствующих в списке позиций. Кроме этого, если они передумают и решат выбрать другой ковер, то функция ВПР автоматически произведёт пересчет и вернет правильную стоимость.

Функция Excel Промежуточные.итоги()

промежуточные итоги в Excel

Функция «Промежуточные итоги()» используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые сроки.

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами Excel (например, автофильтрами).

Для демонстрации пользы от функции Excel Промежуточные.итоги() рассмотрим урезанную выгрузку из базы мероприятий.

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

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

Для этого можно воспользоваться функцией Excel =Промежуточные.итоги(). Функция =Промежуточные.итоги() делает расчет по отфильтрованным значениям.

В формуле можно задать расчет:

  • Средней – номер 1 или 101 – для выборки
  • Счет значений – номер 3 или 103
  • Сумма — 9 или 109
  • И т.д.

функция промежуточные итоги

  • Если номер функции трехзначный, например, 109, то функция работает как промежуточные итоги, т.е. рассчитывает значения по отфильтрованным параметрам.
  • Если номер однозначный или двузначный 2 или 11, то формула промежуточные.итоги() работает, как стандартная формула по выбранной функции.
Перейдем к нашим мероприятиям:

У нас есть выгрузка из базы по следующим столбцам:

  • Ситуация
  • Действия
  • Кол-во клиентов
  • Объём_Продаж_До
  • Объём_Продаж_План
  • Объём_Продаж_Факт
  • Затраты_План
  • Затраты_Факт

Для оценки ситуации в целом введем стандартную формулу суммирования:

промежуточные итоги в таблице

Для расчета промежуточных итогов по выбранным параметрам введем =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;R[3]C:R[65533]C), где

  • 109 – функция суммы для расчета промежуточных итогов;
  • R[3]C:R[65533]C – ссылка на диапазон суммирования.

промежуточные итоги

Теперь, отфильтровав столбец «Ситуация» — «Ситуация 4» и «Действие» — Действие 2 и 3, мы получим перерасчет промежуточных итогов по каждому из показателей и можем их сравнить с показателями «Итого»:

промежуточные итоги в таблице Excel

В результате: видно, что % прироста продаж по отфильтрованным мероприятиям выше, чем по всем, причем планировали результаты получить ниже, чем по итого, а получили выше. Т.е. сработали лучше, чем планировали и лучше чем по всем мероприятиям.

Формула Excel =промежуточные.итоги() – отличный инструмент для расчета показателей по отфильтрованным параметрам.

Если есть вопросы — обращайтесь!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Дополнительная информация

  • совпадение искомых данных может быть точным и приблизительным;
  • при сопоставлении по приблизительной точности данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). Иначе, результат сопоставления будет некорректным;
  • при сопоставлении данных по приблизительной точности:
    – если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем значения поиска;
    – если функция при сопоставлении выдает ошибку #N/A, то искомое значение меньше чем самая маленькая величина в искомом диапазоне;
    – вы можете использовать подстановочные знаки для искомых значений.

Пример расчета доверительного интервала в Excel

Пример 1. В заводском цехе производят деталь, длина которой должна составлять 200 мм. Стандартное отклонение от длины – 3,6 мм. Для контроля качества деталей из партии (генеральная совокупность) делают выборку из 25 деталей. Определить интервал с доверительный уровнем 95%.

Вид таблицы данных:

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

  • 1-B2 – уровень значимости (рассчитан с учетом зависимости от доверительного уровня);
  • B3 – значение стандартного отклонения;
  • B4 – количество деталей в выборке.

То есть, границы доверительного интервала соответствуют: (Xср-1,4112;Xср+1,4112). Допустим, было определено среднее значение выборки – 199,5 мм. Тогда доверительный интервал примерно определяется как (198,1;200,9), при этом номинальная длина детали (200 мм) находится в доверительном диапазоне, то есть производственный процесс не нарушен.

Синтаксис функции ЗНАК в Excel

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

Обобщённый синтаксис для функции ЗНАК выглядит следующим образом:
ЗНАК(число)

Стоит помнить, что формула предназначена только для работы с числами. При попытке определения знака для нечисловых значений получаются непонятные результаты — примеры показаны на видео и в прикреплённом файле Excel. Будьте внимательны, если аргумент для ЗНАК() рассчитывается при помощи другого выражения.

Таким образом, относительно функции ЗНАК() можно сделать следующие выводы:

  • Формула воспринимает все типы данных как числа (или пытается это сделать);
  • Если Вы не уверены, что на входе функции аргумент всегда будет действительным числом, то выполняйте проверку при помощи функции ЕЧИСЛО.

При определении знака числа может быть три варианта в зависимости от знака и значения самого числа:

  • 1 — для положительных чисел (больше нуля);
  • -1 — для отрицательных чисел (меньше нуля);
  • 0 — только для числа ноль;

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

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

Если формула ЗНАК не сможет представить переданный ей аргумент как число, то мы получим ошибку «#ЗНАЧ!» (ошибка в значении). Обработать данную ситуацию Вам поможет формула ЕСЛИОШИБКА.

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

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

Инструмент Таблица данных в Excel

Часто при расчетах в Excel у нас возникают ситуации, когда нужно просчитать формулу при изменении переменной или переменных. За примерами далеко ходить не надо:

  1. Какой будет ежемесячный платеж, если берется кредит на 100 тыс. руб. на 3 года при различных процентных ставках.
  2. Сколько необходимо платить в месяц, если взять в кредит на год, два, три, четыре, пять при различных процентных ставках банка.

Смотрите также видеоверсию статьи «Инструмент Таблица данных в Excel».

Задача 1. А ведь ничего сложного

На самом деле, ничего сверхъестественного в вычислениях нет. Для расчета ежемесячного платежа по аннуитету используется финансовая функция Excel ПЛТ (PMT). Соответственно, зная желаемую сумму займа и срок кредитования, можно рассчитать ежемесячный платеж.

Расчет ежемесечного платежа с помощью ПЛТ

Расчет ежемесечного платежа с помощью ПЛТ

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

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

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

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

Решение первой задачи с помощью инструмента «Таблица данных».

Шаг 1. Делается первый необходимый расчет и создается заготовка под заполнение данными остальных расчетов. Здесь, кстати, абсолютно не важно каким типом ссылок пользоваться, поскольку автозаполнение применятся не будет.

При использовании "Таблица данных" не важен тип ссылок в формуле

При использовании «Таблица данных» не важен тип ссылок в формуле

Шаг 2. Выделяется диапазон с тестовым расчетом и заготовкой под остальные данные (в нашем случае это диапазон B5:G8), дальше выбирается инструмент «Таблица данных» на вкладке Данные, группа «Прогноз», команда «Анализ что если» (для версии Excel 2016, если у вас версия 2013, то таблица данных находится в группе «Работа с данными»).

Выбор инструмента "Таблица данных" на ленте интерфейса

Выбор инструмента «Таблица данных» на ленте интерфейса

Шаг 3. В диалоговом окне необходимо указать ссылку на ячейку, которая является переменной для расчетов. В нашем случае мы хотим посчитать различные варианты задачи при различных вариантах процентной ставки, значит необходимо поставить ссылку на годовой процент, который используется в вычислениях. Ссылку на годовой процент необходимо поставить в поле «Подставлять значения по столбцам в:», т.к. у нас значения заполняются по столбцам, а варианты годового процента расположены горизонтально. Если бы варианты различного процента были расположены вертикально, тогда ссылку необходимо было ставить во второе поле.

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

Выбор ссылки на переменную в диалоговом окне

Выбор ссылки на переменную в диалоговом окне

После нажатия «ОК» мы получи результат, а при выборе любого из значений, которое было рассчитано, в строке формул увидим формулу массива «<=ТАБЛИЦА(B5;)>», а не формулы расчета, как в случае с автозаполнением.

После расчета в строке формул находится только специальная функция ТАБЛИЦА

После расчета в строке формул находится только специальная функция ТАБЛИЦА

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

Задача 2. Рассчитать ежемесячный платеж при различных сроках займа и различных процентных ставках.

Подготовительная таблица выглядит следующим образом.

Заготовка для прогноза значений с двумя переменными

Заготовка для прогноза значений с двумя переменными

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

Расстановка ссылок на переменные в диалоговом окне

Расстановка ссылок на переменные в диалоговом окне

Результат работы инструмента «Таблица данных»

Результат работы инструмента "Таблица данных" с двумя входами

Результат работы инструмента «Таблица данных» с двумя входами

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

Больше примеров работы с финансовыми функциями, инструментом «Таблица данных» в восьмом занятии курса «Excel от новичка до профессионала».

Лабораторная работа «Функции Excel». Использование формул и функций в MS Excel Лабораторная формулы функции в excel

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

Функции Excel

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

Функция — от латинского Functio – исполнение.

За именем функции в круглых скобках следует через точку с запятой список аргументов. Список аргументов может состоять из чисел, текста, логических величин (ИСТИНА или ЛОЖЬ), ссылок, формул, вложенных функций. Если формула начинается с функции, перед именем функции вводится знак «= ».

По характеру аргументов встроенные функции можно разделить на три типа:

С перечислением аргументов (максимум – 30 аргументов): СРЗНАЧ (А2:С23;Е6;200;3) – возвращает среднее значение аргументов

С фиксированными аргументами: СТЕПЕНЬ (6,23;4): возводит первый аргумент (6,24) в степень второго аргумента (4)

Без аргументов : СЕГОДНЯ (): возвращает текущую дату.

Ввод формул

Последовательность ввода функции в формулу:

Открывающаяся круглая скобка;

Перечень аргументов через точку с запятой;

Закрывающаяся круглая скобка.

Ввод функции можно осуществить несколькими способами:

Функции и панель формул

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

Обязательный аргумент выделен полужирным шрифтом – без него функция не может выполнить обработку;

Читайте также:  Коэффициент финансовой независимости в Excel

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

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

Панель формул можно перемещать по экрану, перетаскивая её мышью.

Вложенные функции

Результат вычисления функции может быть использован в качестве аргумента другой функции. Функция, используемая в качестве одного из аргументов другой функции, называется вложенной . Excel поддерживает до 7 уровней вложенности функций.

ЕСЛИ (А4>0;МАКС (А9:В19) ;0)

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

Специальная вставка

Содержимое ячейки можно представлять как совокупность четырёх слоёв информации: формула, значение, формат и примечание. Excel позволяет выполнять раздельное копирование каждого слоя. Информация помещается в буфер как обычно (команда Копировать ), а вставляется с помощью команды Правка \ Специальная вставка…

Для копирования форматов, также как и других приложениях Office , используется инструмент стандартной панели – Формат по образцу . (Практическая работа « Прогноз погоды » ).

При помощи функции заполнить блок А1:А5 случайными числами в диапазоне [-10,10];

В клетку В1 ввести формулу для вычисления целой части значений колонки А;

Скопируйте полученную формулу в блок В2:В5;

Эту же последовательность операций применить к функциям и блокам соответственно:

ABS (A) — С1: С5;

EXP (A) — D1:D5;

SQRT (A ) — E 1:E 5;

Вычисление остатка при делении на 2 – F 1:F 5;

Округление с -1 – H 1:H 5;

Округление с +1 – G 1:G 5

В клетку А7 написать формулу суммы элементов первой колонки (А1:А5)

В клетке В7 – среднее арифметическое по (В1:В5)

С7 – максимальный элемент из (С1:С6)

D 7 – минимальный элемент (D 1:D 6)

E 7 – количество элементов (Е1:Е6)

F 7 – дисперсию значений (F 1:F 6)

Диапазон I 1:I 6 заполнить значениями тригонометрических функций:

В строке 10 вести заголовки полей:

Фамилия\Имя Дата рождения Количество дней

Подкорректируйте ширину колонок и произведите отцентровку заголовков;

В блоке А12:А17 ввести фамилии или имена ваших друзей, знакомых. В блоке В12:В17 – их даты рождения. Дату вводить в европейском формате;

В клетке С9 ввести текущую дату;

В клетку С12 формулу для расчёта количества дней, прожитых человеком для текущей даты;

Между колонками Дата рождения и Количество дней вставить колонку День недели;

В первую клетку колонки вписать функцию вычисления дня недели по дате рождения. Скопировать полученную формулу во все клетки колонки;

В колонке F напротив каждой фамилии написать «Молодой» или «Старый», используя логическую функцию ЕСЛИ. Функцию введите, используя, Мастер функций (ЕСЛИ Количество дней .

После завершения редактирования формулы «режим редактирования» в ячейке необходимо отключить – нажать клавишу или .

6. Что такое функция в Excel? Какова её структура?

Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel. Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “;”. Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.

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

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

Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.

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

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

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

8. Чем отличается формулы от функций? Как в диалоге сформировать текст функции?

Функции могут входить в состав формул.

В диалоге два окна-списка и несколько кнопок. Для удобства встроенные функции разбиты по категориям. В окне под названием «Категория» находится список категорий функций. А в окне с названием «Функция» представлен в алфавитном порядке список функций, выделенной категории.

Ниже списка функций даётся очень сжатая справка о выделенной функции. Но прочитать эту справку можно только при помощи JAWS-курсора. Если же нажать на кнопку помощи, то откроется новое диалоговое окно с подробной справкой о выделенной функции Excel.

А нажатие кнопки «ОК» активизирует второй шаг мастера функций — ввод входных параметров или аргументов функции. Обычно здесь нужно перечислить адреса ячеек и диапазоны, которые участвуют в вычислениях данной функции. После ввода очередного аргумента нужно нажимать табуляцию. В конце нужно нажать «Enter», чтобы активизировать кнопку «Ок». В диалоге ввода параметров также присутствует справочная информация, доступная только для JAWS-курсора.

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

9. Как пользоваться Мастером функций?

Чтобы найти нужную нам встроенную функцию Excel, необходимо войти в меню «Вставка» и активировать пункт «Функция». Раскроется диалог под названием «Мастер функций».

Выводы о проделанной лабораторной работе

В процессе выполнения лабораторной работы освоена методика работы с формулами и функциями в табличном процессоре Microsoft Office Excel.

Лабораторная работа по Microsoft Excel .

Часть I .

Вычисления в Excel . Формулы и функции.

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

Формулы.

Формулой в Excel называется последовательность символов, начинающаяся со знака равенства “=“. В эту последовательность символов могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы. Результатом работы формулы является новое значение, которое выводится как результат вычисления формулы по уже имеющимся данным.

Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.

В качестве примера приведем формулы, вычисляющие корни квадратного трехчлена: ax 2 +bx+c=0. Они введены в ячейки A2 и A3 и имеют следующий вид:

=(-B1+ КОРЕНЬ (B1*B1-4*A1*C1))/2/A1

=(-B1- КОРЕНЬ (B1*B1-4*A1*C1))/2/A1

В ячейках A1 , B1 и C1 находятся значения коэффициентов a, b и с, соответственно. Если вы ввели значения коэффициентов a=1, b=-5 и с=6 (это означает, что в ячейках A1 , B1 и C1 записаны числа 1 , 5 и -6 ), то в ячейках A2 и A3 , где записаны формулы, вы получите числа 2 и 3 . Если вы измените, число в ячейке A1 на -1 , то в ячейках с формулами вы получите
числа -6 и 1 .

Использование ссылок и имен.

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

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

Имя — это легко запоминающийся идентификатор, который можно использовать для ссылки на ячейку, группу ячеек, значение или формулу. Создать имя для ячейки можно в поле имени, или через меню Вставка | Имя. | Присвоить. Использование имен обеспечивает следующие преимущества:

Формулы, использующие имена, легче воспринимаются и запоминаются, чем формулы, использующие ссылки на ячейки.

Например, формула “=Активы-Пассивы” гораздо понятнее, чем формула “=F6-D6 ”.

При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте — в определении имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.

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

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

Excel автоматически создает имена на основе заголовков строк и столбцов рабочего листа. Подробной информация о создании таких имен содержится в главе “Базы данных”.

После того, как имя определено, вы можете:

Например, определив имя “Прибыль” как “=$F$12”, можно заменить все ссылки на ячейку $F$12 именем “Прибыль”.

Перемещение и копирование формул. Относительные и абсолютные ссылки.

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

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

При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок. Для этого перед символами адреса ячейки или ссылки устанавливаются символы “$ ”. Изменяться только те атрибуты адреса ячейки, перед которыми не стоит символ “$ ”. Если перед всеми атрибутами адреса ячейки поставить символ “$ ”, то при копировании формулы ссылка не изменится.

Например, если в записи формулы ссылку на ячейку D7 записать в виде $D7, то при перемещении формулы будет изменяться только номер строки “7”. Запись D$7 означает, что при перемещении будет изменяться только символ столбца “D”. Если же записать адрес в виде $D$7, то ссылка при перемещении формулы на этот адрес не изменится, и в расчетах будут участвовать данные из ячейки D7. Если в формуле указан интервал ячеек G3:L9, то управлять можно каждым из четырех символов: “G”, “3”, “L” и “9”, помещая перед ними символ “$”.

Если в ссылке используются символы $ , то она называется абсолютной , если символов $ в ссылке нет — относительной . Адреса таких ссылок называются абсолютными и относительными, соответственно.

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

Рассмотрим пример работы с формулами, за основу возьмем Таблицу 1. В данной таблице подсчитываются по формулам поля:

Закупочная цена ($): — в зависимости от текущего курса $, который заносится в отдельную ячейку D1 .

Сумма закупки: = Закупочная цена товара*Количество

Сумма реализации: = Розничная цена товара*Количество

Валовой доход: = Сумма реализации — Сумма закупки

Такие формулы записаны в ячейки таблицы:

Таблица 1

Наименование

Фирма поставщик

Сумма закупки

Сумма реали-зации

Валовый доход

В данном примере ячейке D1 присвоено имя «kurs» , которое используется во всех формулах для пересчета закупочной цены в $. Эта ячейка содержит только число (в нашем примере 3.62, был и такой курс когда-то), а пояснительный текст содержится в ячейке С1 .

При подсчете суммы реализации , использован другой прием для того, чтобы можно было правильно копировать формулы: для ссылки на розничную цену конкретного товара используется частично абсолютный адрес: I$2 — в этом адресе запрещено изменение номера строки, поэтому при копировании такой формулы для товара «;бананы»; ошибок не возникнет. Для других товаров нужно создать формулы со ссылкой на их розничную цену.

Функции.

Понятие функции.

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

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

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

Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “; ”. Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.

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

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

Типы функций

Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок . Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические .

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

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

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

Рабо чая книга Excel . Связь между рабочими листами.

Совместное использование данных.

Листы рабочей книги

До сих пор работали только с одним листом рабочей книги. Часто бывает полезно использовать несколько рабочих листов.

В нижней части экрана видны Ярлычки листов . Если щелкнуть на ярлычке левой клавишей мыши, то указанный лист становится активным и перемещается наверх. Щелчок правой кнопкой на ярлычке вызовет меню для таких действий с листом, как перемещение, удаление, переименование и т.д.

В левом нижнем углу окна рабочей книги находятся кнопки прокрутки с помощью которых можно переходить от одного рабочего листа к другому.

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

Расположение рабочих книг

Предположим, вы хотите видеть на экране сразу все открытые книги, Excel без труда выполнит ваше желание, причем вы сможете легко ориентироваться в своих книгах. С помощью команды Excel Окно Расположить можно расположить открытые рабочие книги на экране четырьмя способами.

Рядом — рабочие книги открываются в маленьких окнах, на которые делится весь экран «;плиточным»; способом;

Сверху вниз — открытые рабочие книги отображаются в окнах, имеющих вид горизонтальных полос,

Слева направо — открытые рабочие книги отображаются в окнах, имеющих вид вертикальных полос;

Каскадом — рабочие книги (каждая в своем окне) «;выкладываются»; на экране слоями.

Переходы между рабочими книгами

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

Щелкните навидимой части окна рабочей книги;

Нажмите клавиши/B> Ctrl + F 6> дляперехода из окна одной книги в окно другой.

Откройте меню Excel Окно. В нижней его части содержится список открытых рабочих книг. Для перехода в нужную книгу просто щелкните по имени.

Копирование данных из одной рабочей книги в другую

С помощью команды Excel Копировать можно копировать данные из одной рабочей книги в другую. Например, вы открыли две рабочих книги одна из которых содержит квартальный бюджет, а другая — годовой. Для экономии времени было бы неплохо скопировать данные по первому кварталу из первой рабочей книги во вторую. При этом исходные данные в первой рабочей книге не изменяв появится копия этих данных.

Читайте также: 

Чтобы скопировать данные из одной рабочей книги в другую, откройте обе рабочие книги. Выделите данные в первой книге и щелкните на кнопке Копировать панели инструментов Стандартная переключиться в другую книгу, используйте любой из перечисленных выше методов. Например, согласно одному изних, выберите из меню Окно имя второй рабочей книги. Перейдите в нужный рабочий лист и выделите ячейку, в которую предполагаете вставить данные. Щелкните на кнопке Вставить панели инструментов Стандартная . Excel моментально скопирует данные во вторую рабочую книгу.

Перенос данных между рабочими книгами

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

Существует быстрый способ переноса данных рабочего листа (листов) между рабочими книгами. Он состоит в использовании метода «;перетащить и опустить»;. Сначала откройте книги, задействованные в операции переноса данных. Выберите из меню Excel команду Окно/ Расположить . В открывшемся диалоговом окне Расположение окон выберите вариант рядом и щелкните на кнопке ОК . Вы должны видеть хотя бы небольшую часть окна каждой рабочей книги. Выделите ярлычок листа (листов), который вы хотите скопировать. Поместите указатель мыши поверх выделенного ярлычка листа, щелкните и, не отпуская кнопку мыши, перетащите ярлычок в окно другой рабочей книги. Когда вы отпустите кнопку мыши, лист будет «;прописан»; в новой (для него) рабочей книге.

Создание связей между рабочими листами и рабочими книгами.

Excel позволяет использовать в таблице данные с других листов и из других таблиц.

Связывание — это процесс динамического обновления данных в рабочем листе на основании данных другого источника (рабочего листа или рабочей книги). Связанные данные отражают любые изменения, вносимые в исходные данные.

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

Excel позволяет создавать связи с другими рабочими листами и другими рабочими книгами трех типов:

Использование в формуле связывания ссылки на другой рабочий лист

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

зависимой рабочей книгой , исходной рабочей книгой .

Чтобы сослаться на ячейку в другом рабочем листе, поставьте восклицательный знак между именем листа и именем ячейки. Синтаксис для этого типа формул выглядит следующим образом: =ЛИСТ!Ячейка . Если ваш лист имеет имя, то вместо обозначения лист используйте имя этого листа. Например, Отчет! B 5 .

Если имя содержит пробелы (например, Бюджет 99 ), то при создании ссылок на другие листы это имя необходимо заключать в одинарные кавычки.

Связывание нескольких рабочих листов

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

В таких случаях Excel ссылается на диапазоны ячеек с помощью трехмерных ссылок. Трехмерная ссылка устанавливается путем включения диапазона листов (с указанием начального и конечного листа) и соответствующего диапазона ячеек. Например, формула, использующая трехмерную ссылку, которая включает листы от Лист1 до Лист5 и ячейки А4:А8, может иметь следующий вид: = SUM (ЛИСТ1:ЛИСТ5!А4:А8).

Трехмерные ссылки можно включить в формулу и другим способом. Для этого достаточно щелкнуть на рабочем листе, который нужно включить в формулу. Но сначала начните формулу в ячейке, где хотите получить результат. Когда дойдет черед до использования трехмерной ссылки, щелкните на ярлычке первого листа, подлежащего включению в ссылку, затем нажмите (и не отпускайте) клавишу /B> Shift > и щелкните на ярлычке последнего листа, подлежащего включению в ссылку. После этого выделит нужные ячейки. Завершив построение формулы, нажмите клавишу /B> Enter > .

Связывание рабочих книг

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

Связь между двумя файлами достигается за счет введения в один файл формулы связи со ссылкой на ячейку в другом файле, файл, который получает данные из другого, называется файлом назначения, а файл, который предоставляет данные, — файлом-источником.

Как только связь устанавливается. Excel копирует величину из ячейки в файле-источнике в ячейку файла назначения. Величина в ячейке назначения автоматически обновляется.

При ссылке на ячейку, содержащуюся в другой рабочей книге, используется следующий синтаксис: [Книга]Лист!Ячейка . Вводя формулу связывания для ссылки на ссылку из другой рабочей книги, используйте имя этой книги, заключенное в квадратные скобки, за которыми без пробелов должно следовать имя рабочего листа, затем восклицательный знак (!), а после него — адрес ячейки (ячеек). Например « C :\Petrov\[ Журнал1 .хls]Литература»!L3.

Обновление связей

Работая с несколькими рабочими книгами и формулам связывания, необходимо знать, как эти связи обновляются. Будут ли результаты формул обновляться автоматически, если изменить данные в ячейках, на которые есть ссылки в только в том случае, если открыты обе рабочие книги.

Если данные в исходной рабочей книге изменяются в тот момент, когда зависимая книга (которая содержит формулу связывания) закрыта, то связанные данные не обновляются немедленно. Открывая зависимую рабочую книгу следующий раз, Excel запросит от вас подтверждение на обновление данных. Чтобы обновить все связанные данные в рабочей книге, выберите ответ Да. Если у вас есть связи, которые обновляются вручную, или вы хотите сами обновить связи, выберите ответ Нет.

Для удаления листа выберите команду Удалить из контекстно-зависимого меню для ярлычков. Для удаления сразу нескольких рабочих листов предварительно выделите их при нажатой клавише Ctrl .

Проверьте:

знаете ли вы, что такое: рабочая книга Excel; рабочий лист; правила записи формул для связи рабочих листов;

умеете ли вы: вставлять рабочий лист; удалять; переименовывать; перемещать; копировать; открывать окна; закрывать; упорядочивать; осуществлять связь между листами одной и разных рабочих книг.

Лабораторная работа по Microsoft Excel.

Построение диаграмм в EXCEL

С помощью Microsoft EXCEL можно создавать сложные диаграммы для данных рабочего листа. EXCEL представляет 9 типов плоских диаграмм и 6 объемных типов диаграмм. Диаграмма может находиться на рабочем листе вместе с исходными данными или на отдельном листе диаграмм, который является частью книги. Диаграмма, которая находится на рабочем листе, называется внедренной диаграммой. Прежде чем начать построение диаграммы, рассмотрим два важных определения.

Ряд данных — это множество значений, которые надо отобразить на диаграмме. В задании, например , это показатели по тестам.

Итак, ряд данных — это множество значений, которое наносится на диаграмму, а категории — это как бы «заголовки» к ним.

Создание внедренных диаграмм.

Задание 1. Постройте внедренную гистограмму по таблице «Результаты тестирования», (Рис. 1)

Чтобы построить гистограмму по данным таблицы «Результаты тестирования», выполните следующие действия:

Выделите диапазон, содержащий исходные данные (в данном случае, B 1: H 11 ) и нажмите кнопку Мастер диаграмм (или выберите в меню Вставка команду Диаграмма ). В результате появится окно мастера для построения диаграммы.

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

Первое окно диалога мастера диаграмм предоставляет набор рисунков с типами диаграмм. Выберите тип Гистограмма в левой части окна и вид – Обычная гистограмма в правой части окна. Нажмите кнопку Далее .

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

Используя переключатели Ряды данных , установите Ряды в столбцах . Примечание: Вкладка Ряд позволяет удалять или добавлять ряды данных из диаграммы . Нажмите кнопку Далее .

В третьем окне Мастера диаграмм устанавливаются различные параметры для создаваемой диаграммы. Во вкладке Заголовки назовите диаграмму «Результаты тестирования ». Во вкладке Линии сетки добавьте основные линии по оси X и Y. Во вкладке Легенда разместите легенду справа от диаграммы. Нажмите кнопку Далее .

В последнем окне диалога Мастер диаграмм , для создания внедренной гистограммы, установите переключатель в поле Поместить диаграмму на имеющемся листе . Нажмите кнопку Готово .

Измените размеры гистограммы:

выделите ее щелчком мыши;

перетащите в нужном направлении один из восьми маркеров выделения.

Создание диаграмм на отдельном листе

Задание 2. Создайте круговую диаграмму по средним показателям тестирования на отдельном листе.

Выделите диапазоны данных, содержащие фамилии студентов и средние показатели тестирования. В нашем случае это несмежные диапазоны ячеек B1:B11 и H2:H21. Для выделения несмежных диапазонов выделите сначала первый диапазон, затем нажмите на клавиатуре кнопку Ctrl и, удерживая ее, выделите второй из несмежных диапазонов.

Нажмите кнопку Мастер диаграмм (или выберите в меню Вставка команду Диаграмма ).

В диалоговом окне Мастера диаграмм выберите тип диаграммы Круговая . Вид диаграммы оставьте выбранным по умолчанию. Нажмите Далее .

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

В третьем окне диалога выберите вкладку Подписи данных и установите флажок напротив поля Значения . Выбрав вкладку Легенда , добавьте легенду внизу диаграммы.

В последнем окне Мастера диаграмм установите переключатель в поле Поместить диаграмму на отдельном листе . Нажмите Готово .

Настройка элементов диаграммы

Элемент диаграммы – это компонент диаграммы такой, например, как ось, точка данных, название или легенда, каждый из которых можно выделять и форматировать.

Задание 3. Внесите изменения в созданную на отдельном листе круговую диаграмму.

Активизируйте лист Excel, где вами была создана круговая диаграмма.

Добавьте название к диаграмме (или измените его) «Средние показатели тестирования в группе », выбрав команду меню Диаграмма Параметры диаграммы – вкладка Заголовки .

Отформатируйте введенное название, вызвав контекстное меню щелчком правой клавиши мыши по названию диаграммы и выбрав Формат заголовка диаграммы . Установите рамку вокруг названия диаграммы, выберите цвет, тип и толщину лини для этой рамки по своему желанию. Сделайте установки для шрифта, используемого в названии, по своему усмотрению.

Щелкните в пределах самой диаграммы, активизировав ее. Измените тип диаграммы, вызвав щелчком правой клавиши мыши по области диаграммы контекстное меню и выбрав пункт Тип диаграммы или выполнив в меню Диаграмма команду Тип диаграммы . В окне Тип диаграммы выберите Объемный вариант разрезанной круговой диаграммы . Нажмите ОК.

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

Поверните диаграмму на 90 0 . Для этого щелкните правой клавишей мыши по области диаграммы, вызвав тем самым контекстное меню. Выберите пункт меню Формат ряда данных и в открывшемся окне вкладку Параметры . Установите угол поворота 90 0 . Нажмите ОК .

Щелкните мышью в области диаграммы так, чтобы выделенной оказалась одна из долей диаграммы. Вызвав контекстное меню, выберите пункт Формат точки данных или выберите команду Выделенный элемент данных из меню Формат .

Во вкладке Вид окна диалога Формат элемента данных измените цвет заливки доли диаграммы. Для любой другой из долей диаграммы выберите заливку текстурой или узором в дополнительном окне Способы заливки .

Задание 4. Самостоятельно создайте кольцевую диаграмму по результатам тестирования для одного студента из группы на отдельном листе. Настройте ее по своему усмотрению.

Быстрый способ создания диаграмм

Для того чтобы быстро создать диаграмму необходимо выделить исходные данные (в нашем случае это диапазонB 1: H 11) и нажать клавишуF 11. Появится диаграмма на отдельном листе во весь лист.

Задание 5. Создайте диаграмму Результатов тестирования , используя быстрый способ создания диаграмм.

Построения графика

Графики обычно используются для отображения динамики изменений ряда значений.

Задание 6. Постройте график, отражающий динамику результатов тестирования первых трех студентов группы.

Выделите область для построения диаграммы, не захватывая средние показатели тестирования. (В нашем случае это диапазон B 1: G 4 ).

Нажмите кнопку Мастер диаграмм (или выберите в меню Вставка команду Диаграмма ).

В окне диалога Мастера диаграмм выберите тип диаграммы — График и первый из предложенных вариантов вида графиков. Нажмите Далее .

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

В третьем окне диалога во вкладке Заголовки дайте название диаграммы «График результатов тестирования ». Подпишите оси: Ось X – номер теста; Ось Y результаты тестирования в % .

Во вкладке Линии сетки добавьте промежуточные линии сетки для оси X и оси Y.

Добавьте легенду справа от диаграммы.

Добавьте к графику таблицу данных. (Вкладка Таблица данных ). Нажмите Далее .

В последнем окне диалога включите переключатель в окне Поместить диаграмму на имеющемся листе . Нажмите Готово .

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

Добавьте в полученный график Microsoft WINDOWS 4 2 Подготовка документов в текстовом процессоре Microsoft Word 10 3 Проектирование.

. . Лабораторная работа № 5 Тема: Использование логических функций в формулах Цель работы : Знакомство и освоение логических функций в MS Excel : Применение логических функции .

Лабораторный практикум по информатике

. Работа с текстом. Вычисления Лабораторная работа 40. Вычисление выражений с переменными значениями. Математические функции . Ранжированные переменные. Графики функций Лабораторная работа . . Формулы Для вычислений в Microsoft Excel используются формулы . .

Лабораторный практикум по курсу “субд” для слушателей получающих дополнительную квалификацию «разработчик профессионально-ориентированных компьютерных технологий» лабораторная работа №1 проектирование бд

. части полей по возрастанию, а других полей по убыванию). Лабораторная работа . вычисляемая формула ; дата. Вычисление итоговых значений для записей формы выполняется с помощью встроенных статистических функций . документы Microsoft Word и Microsoft Excel , .

К ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ ПО ДИСЦИПЛИНЕ «Информационная безопасность в сетях»

. Microsoft Office. Задание на лабораторную работу Изучить теоретический материал по данной лабораторной работе . Ознакомиться с указаниями по . рабочем листе Excel . В первой части следует. по формуле : Например, Приведем код на Паскале вычисления функции .

Лабораторная работа №1. Формулы и функции MS Excel

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

Задание 1 . Сформировать объявления о продаже квартир согласно образцу (рис. 1).

Рис. 1. Объявления о продаже квартир

Сгруппировать имеющиеся данные по квартирам в виде списка (рис. 2).

Рис. 2. Данные о квартирах, выставленных на продажу

В ячейку G2 ввести формулу:

Объясните данную формулу, воспользовавшись справкой.

Для диапазона G3:G5 воспользоваться маркером автозаполнения либо скопировать данную формулу.

При необходимости отформатировать полученные объявления, используя команду Формат | Автоформат.

Задание 2. Сформировать и заполнить ведомость переоценки основных средств производства по форме, приведенной на рис. 3.

В ячейку А1 ввести название ведомости.

В ячейки A4:F4 ввести названия полей ведомости: Наименование объекта, Балансовая стоимость (БС), Износ объекта (ИО), Остаточная стоимость (ОС), Восстановительная полная стоимость (ВПС), Восстановительная остаточная стоимость (вое). Поле Наименование объекта включает следующие строки: Отдел менеджмента и маркетинга, Отдел транспортировок, Сборочный цех, Отделочный цех, Склад № 1, Склад № 2, Склад № 3, Итого.

Формулы для расчетов:

где к — коэффициент, равный:

3,3 — если БС меньше либо равен 650 млн руб.;

4,2 — если БС больше 650 млн руб., но меньше 1000 млн руб.;

5,1 — если БС равен 1000 млн руб. или более.

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

для ячейки D5 : =В5-С5

для ячейки Е5 : =В5*ЕСЛИ(В5 б50;В5 650;В5 5;Е4 10; Е4 7000;Н4 10000;Н4 =2;B7 В82;»Проект экономически целесообразен»; «Проект необходимо отвергнуть»)

Рис. 30. Расчет внутренней скорости оборота инвестиций

2. Рассмотрим этот пример для всех комбинаций исходных данных. Для создания сценария следует использовать команду Сервис | Сценарии | кнопка Добавить (рис. 31).

Рис. 31. Добавление сценария для первой комбинации исходных данных

После нажатия на кнопку ОК появляется возможность внесения новых значений для изменяемых ячеек (рис. 32).

Рис. 32. Окно для изменения значений ячеек сценария

Для сохранения результатов по первому сценарию нет необходимости редактировать значения ячеек- достаточно нажать кнопку ОК для подтверждения значений, появившихся по умолчанию, и выхода в окно Диспетчер сценариев (рис. 33).

Читайте также:  Коэффициент соотношения дебиторской и кредиторской задолженности в Excel

Рис. 33 Окно Диспетчер сценариев с первым сохраненным сценарием

3. Для добавления к рассматриваемой задаче новых сценариев достаточно нажать кнопку Добавить в окне Диспетчер сценариев и повторить вышеописанные действия, изменив значения в ячейках исходных данных (рис. 34).

На рис. 34 сценарий Скорость_оборота_1 соответствует данным (-700; 70; 90; 300; 250; 300), сценарий Скорость_оборота_2 — данным (-600; 50; 100; 200; 200; 300), сценарий Скорость_оборота_З- данным (-650; 90; 120; 200; 250; 250), сценарий скорость_оборота_4- данным (-500, 100, 100, 200,

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

Рис. 34. Окно Диспетчер сценариев с добавленными сценариями

по расчету скорости оборота инвестиций

Рис. 4.31. Добавление ячеек результата в окно Отчет по сценарию

4. Для полученияитогового отчета по всем добавленным сценариям следует нажать кнопу Отчет в окне диспетчера сценариев. В появившемся окне Отчет по сценарию (рис. 35) выбрать необходимый тип отчета и дать ссылки на ячейки, в которых вычисляются результирующие функции. При нажатии на кнопку ОК на соответствующий лист рабочей книги выводится отчет по сценариям (рис. 36 и рис. 37)

Рис. 36 Отчет типаСтруктура

Рис. 37 Отчет типаСводная таблица по сценариям расчета скорости оборота инвестиций

Для выполнения задания:

1. Введите данные на рабочий лист в соответствии с рис. 38.

Рис.38 Подготовка данных для построения диаграммы

2. Выделите мышью диапазон А5:В12 и выполните команду Вставка | Диаграмма либо нажмите кнопку мастера диаграмм напанели инструментов Стандартная.

Выбор вида диаграммы — с учетом того, что в примере предложены дискретные статистические данные, подходящий вид диаграммы — гистораммного типа.

Какой ряд выбран в качестве значения данных (заполните поля Ряд и Значения). В нашем случае в поле Ряд вносим объем и в поле Значения — диапазон $В$5: $В$12;

D какой ряд будет служить подписями по оси X — в поле Подписи по оси X вносим год и указываем диапазон $А$5:$А$12.

Рис. 39. Определение рядов данных

4. Отформатируйте полученную диаграмму, используя контекстное меню каждого ее элемента (рис. 40).


Рис. 40. Построенная диаграмма Объем продаж

Задание 2. Построить график функции: у = cos 3 (πx).

Результат для этого примера представлен на рис. 41.

Для выполнения задания:

1. Задайте область определения X вводом начальных данных: 0 и 0,1, а затем маркером автозаполнения подготовьте весь диапазон А7:А27.

2. В ячейку В7 введите формулу:

=(СОЗ(ПИ()*А7))^3 и скопируйте ее на диапазон В7:В27.

3. Постройте график функции с помощью мастера диаграмм.

4. Отформатируйте полученный график.


Рис. 41 Пример построения графика функции

А1:J35, A1:J1 )

2. Сформируйте диапазон критериев для расширенного фильтра в соответствии с рис. 43.


Рис. 43. Диапазон критериев для расширенного фильтра к задаче про белые и черные машины

3. Выполните команду

4. Отфильтрованные данные приведены на рис. 44.

Рис. 44. Данные к задаче про белые и черные машины, отобранные расширенным фильтром

Задание 2. Определить, имеются ли в списке (см. рис. 42) машины, год выпуска которых больше 2000 и пробег которых более 100 00 км, но менее 100 000 км, или черные Мерседесы, цена которых более 20 000 у. е., но менее 30 000 у. е.

1. Откройте список, подлежащий фильтрации (список располагается в диапазоне А1:JЗ5, строка заголовка- в диапазоне A1:J1 )

МЗ:М4 . В ячейку МЗ введите Условие. В ячейку М4 введите формулу:

ИЛИ(И(G2>10000;G2 1990);И(C2=»Мерседес»;F2=»Черный»;H2>20000;H2 =СРЗНАЧ($G$ 2:$G$13 3))

3. Выполните команду Данные | Фильтр | Расширенный фильтр.

1. Выделите список (или — установите в список указатель ячейки) и проведите сортировку (команда Данные | Сортировка) сначала — по полю Продавец, затем — по полю Дата продажи (рис. 47).


Рис. 46. Список продаж

2. Примените команду Данные | Итоги. В окне Промежуточные итоги установите параметры в соответствии с рис. 48: для получения верхнего (первого) уровня итогов — общее количество товаров, проданных конкретным продавцом.

Рис. 47.Сортировка списка

Рис. 48. Окно Промежуточные итоги для получения итогов по полю Продавец

3. Для получения второго уровня итогов поместите указатель ячейки в список с полученными итогами, затем выполните команду Данные | Итоги, установив в окне Промежуточные итоги параметры в соответствии с рис. 49.

Рис.49. Окно Промежуточные итоги для получения итогов по полю Дата продажи

4. Полученные промежуточные итоги представлены на рис. 50.


Рис. 50.Вложенные промежуточные итоги

Консолидация данных

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

1. Указать местоположение будущих консолидированных данных.

2. Выбрать команду Данные | Консолидация.

3. В открывшемся окне указать диапазоны данных, подлежащие консолидации.

4. Указать способ консолидации:

Согласно расположению в диапазоне — сняты все флажки области Использовать в качестве имен;

Согласно заголовкам строк и столбцов- установлены

флажки подписи верхней строки и значения левого столбца.

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

6. При необходимости указать добавление структуры — установить флажок Создавать связи с исходными данными.

Задание 1. Объединить данные о количестве и стоимости проданных товаров в сети магазинов, которые представлены в виде списка со следующими полями (рис. 51): Товар, Стоимость, Количество, расположены на листе 2, листе 4 и листе 5.

Рис. 51.Данные о реализованных товарах

Консолидация в соответствии с данными рис. 52. Объединенные данные представлены на рис. 53.

Сводные таблицы

Сводные таблицы представляют собой средство для группировки, обобщения и анализа данных, находящихся в списках MS Excel или в таблицах, созданных в других приложениях. Внешне сводные


Рис. 52. Ввод данных в окно Консолидация

Рис. 53. Представление консолидированных данных

Сводные таблицы

Сводные таблицы представляют собой средство для группировки, обобщения и анализа данных, находящихся в списках MS Excel или таблицах, связанных в других приложениях. Внешние сводные таблицы являются структурой, позволяющей размещать данные в трехмерном виде. Сводные таблицы могут использоваться:

Для обобщения большого количества однотипных данных;

Для реорганизации данных (с помощью перетаскивания);

Для отбора и группировки данных;

Для построения диаграмм.

Сводные таблицы создаются с помощью мастера сводных таблиц (команда Данные | Сводная таблица) по следующей методике:

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

2. Выполнить команду Данные | Сводная таблица.

3. Задать исходный диапазон данных, выполнив шаги 1 и 2 мастера (рис. 54 и 55). После нажатия кнопки Далее в окне мастера, приведенном на рис. 55, откроется окно 3-го шага мастера (рис. 56).

4. Прежде чем указать местоположение будущей таблицы (рис. 56), необходимо нажать кнопку Макет и в открывшемся окне (рис. 57) сформировать макет сводной таблицы (т. е. задать страницу, строки, столбцы, итоговые и вычисляемые поля сводной таблицы).


Рис. 54Определение местоположения данных для сводной таблицы


Рис. 55.Диапазон данных для сводной таблицы


Рис. 56.Указание местоположения будущей сводной таблицы

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

6. Нажать кнопку Параметры (рис. 56) и в открывшемся окне (рис. 59) установить необходимые параметры сводной таблицы.


Рис. 57.Формирование макета сводной таблицы

Рис. 58. Окно Вычисление поля сводной таблицы

Рис. 59. Установка параметров сводной таблицы

7. После проведения всех подготовительных операций нажать кнопку Готово (рис. 56).

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

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

Местонахождение исходных данных – список MS Excel, внешний источник, диапазоны консолидации, находящиеся в другой сводной таблице;

Необходимость при создании структуры сводной таблицы определить:

1) поля, находящиеся в строках и столбцах таблицы;

2) поля, по которым подводятся итоги (с выбором необходимой операции);

3) поля для страниц, что позволяет представить информацию в трехмерном виде.

Сводная таблица – это средство только для отображения данных. Поэтому в самой таблице данные редактировать нельзя. Для изменения данных в сводной таблице необходимо внести изменения в источник данных, а затем обновить сводную (кнопкой Обновить данные на панели инструментовСводные таблицы (рис. 60);

Рис. 60. Панель инструментов Сводные таблицы

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

1 Построение таблиц истинности в электронной таблице Excel

2 Цели урока: обобщение и систематизация знаний по темам «Таблицы истинности», «Преобразование логических выражений», «Решение логических задач»; формирование умения автоматизировать решение логических задач в электронных таблицах MS Excel.

3 Проверка домашней работы: Круги Эйлера – Венна: 66, 67; Табличный способ решения логических задач: 59, 127.

4 Инверсия — логическое отрицание От лат. inversio — переворачиваю Логическое отрицание делает истинное высказывание ложным и, наоборот, ложное – истинным. AF=А Таблица истинности функции логического отрицания В переводе на естественный язык: «Не А» «Неверно, что А» ИСТИНА – 1 ЛОЖЬ — 0

5 Конъюнкция — логическое умножение От лат. conjunctio — связываю Результат логического умножения является истинным тогда и только тогда, когда истинны все входящие в него простые высказывания. Таблица истинности функции логического умножения ABF=A*B В переводе на естественный язык: «и А, и В» «как А, так и В» «А вместе с В» «А несмотря на В» «А, в то время как В» И,, and, &, *, ·

6 Дизъюнкция — логическое сложение От лат. disjunctio – различаю Результат логического сложения является истинным тогда, когда истинно хотя бы одно из входящих в него простых высказываний. В переводе на естественный язык: «А или В» Таблица истинности функции логического сложения ABF=A+B ИЛИ,, or, +

7 Импликация — логическое следование Результат логического следования является ложным тогда и только тогда, когда из истины следует ложь. От лат. implicatio – тесно связывать Таблица истинности функции логического следования ABF=A B А – условие, В – следствие, В переводе на естественный язык: «если А, то В» «В, если А» «Когда А, тогда В» «А достаточно для В» «А только тогда, когда В» «Для А необходимо, чтобы В»

8 Эквивалентность — логическое равенство Результат логического равенства является истинным тогда и только тогда, когда оба высказывания одновременно либо истинны, либо ложны. От лат. aeguivalens – равноценное Таблица истинности функции логического равенства ABF=A B В переводе на естественный язык: «А эквивалентно В» «А тогда и только тогда, когда В» =,,

9 В естественном языкеВ логике … и … … или … Неверно, что… … в том и только в том случае … … если …, то … … тогда и только тогда, когда … … не … Ниже приведена таблица, левая колонка которой содержит основные логические союзы (связки), с помощью которых в естественном языке строятся сложные высказывания. Заполните правую колонку таблицы соответствующими названиями логических операций.

10 В естественном языкеВ логике. и. конъюнкция. или. дизъюнкция Неверно, что. отрицание. хотя. конъюнкция. в том и только в том случае. эквивалентность. но. конъюнкция. а. конъюнкция Если. то. импликация. однако. конъюнкция. тогда и только тогда, когда. эквивалентность Либо. либо. строгая дизъюнкция. необходимо и достаточно. эквивалентность Из. следует. импликация. влечет. импликация. равносильно. эквивалентность. необходимо. импликация. достаточно. обратная импликация

11 Алгоритм построения таблицы истинности: 1.Подсчитать количество переменных n в логическом выражении; 2.Определить число строк в таблице, которое равно 2 n 3.Подсчитать количество логических операций в логическом выражении m и определить количество столбцов в таблице, которое равно m + n 4.Ввести названия столбцов таблицы в соответствии с последовательностью выполнения логических операций с учетом скобок и приоритетов; 5.Заполнить столбцы входных переменных наборами значений; 6.Провести заполнение таблицы истинности по столбцам, выполняя логические операции в соответствии с установленной в п.4 последовательностью.

12 Построение таблиц истинности в электронной таблице Excel.

13 Как построить таблицу истинности в Microsoft Excel Алгоритм: 1.Установить по формуле последовательность выполнения логических операций; 2. Записать названия столбцов и возможные наборы значений исходных логических переменных; 3. Создать логические формулы: вставка — функция; выбрать категорию: логические; указать ячейки, в которых хранятся аргументы функции; протянуть формулу для всех значений логических переменных.

14 Составить таблицу истинности для логического выражения А & (В۷С).

15 АВСС(В ۷ С)А & (В ۷ С)

16 Составить таблицу истинности для логического выражения в тетради: F = (А۷В) & (А۷С) & (В&С) & А F = A & B v A & B F = A & (B & B C) F= A (B & C) F= (A & B) v (A & B) F= A & (B v B & C)

17 Домашняя работа: 1 ( 46). Обсуждая конструкцию нового трёхмоторного самолёта, трое конструкторов поочередно высказали следующие предположения: 1) при отказе второго двигателя надо приземляться, а при отказе третьего можно продолжать полёт; 2) при отказе первого двигателя лететь можно, или при отказе третьего двигателя лететь нельзя; 3) при отказе третьего двигателя лететь можно, но при отказе хотя бы одного из остальных надо садиться. Лётные испытания подтвердили правоту каждого из конструкторов. Определите, при отказе какого из двигателей нельзя продолжать полёт. 2 ( 54). Представим такую ситуацию: по телевизору синоптик объявляет прогноз погоды на завтра и утверждает следующее: 1. Если не будет ветра, то будет пасмурная погода без дождя. 2. Если будет дождь, то будет пасмурно и без ветра. 3. Если будет пасмурная погода, то будет дождь и не будет ветра. Так какая же погода будет завтра? Уровень знания: Записать условие задач 1 и 2 на языке алгебры логики. Разобрать решение задач в учебнике (п ) Уровень понимания: + Сформулировать достоинства и недостатки метода решения задач, основанного на алгебре логики. Уровень применения: + Сформулировать тип логических задач, решаемых данным методом.

Объединение разных типов данных в одну таблицу

Утилиты наподобие FireEye Redline включают в себя функции для объединения разных типов событий в единой временной шкале. При использовании индивидуальных парсеров или артефактов в нестандартном формате бывает сложно объединить разнородной информации общую картину. Нормализация данных при помощи Excel в один набор с простыми и понятными столбцами позволяет состыковать разные типы.

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

A logon was attempted using explicit credentials.

Subject:
Security ID: DomainCorp\Administrator
Account Name: Administrator
Account Domain: DomainCorp
Logon ID: 0x1b38fe
Logon GUID: <00000000-0000-0000-0000-000000000000>
Account Whose Credentials Were Used:
Account Name: VictimUser
Account Domain: DomainCorp
Logon GUID: <00000000-0000-0000-0000-000000000000>
Target Server:
Target Server Name: DestinationServer
Additional Information:
Process Information:
Process ID: 0x5ac
Process Name: C:\Program Files\Internet Explorer\iexplore.exe
Network Information:
Network Address: —
Port: —

HKEY_USER\Software\Microsoft\Terminal Server Client\Servers\

Поскольку вышеуказанные необработанные наборы данных имеют разные колонки и типы, довольно трудно проводить анализ в общем контексте. Если мы отформатируем данные при помощи конкатенации строк в Экселе, то сможем легко скомбинировать информацию в единую систему. Для форматирования можно использовать оператор «&» вместе с функцией присоединения, которая может пригодиться в поле с итогами.

Пример команды для объединения данных, имеющих отношение к файловой системе и разделенных амперсантами, может выглядеть так: “=D2 & » | » & C2 & » | » & E2 & » | » & F2 & » | » & G2 & » | » & H2”. Комбинируя эту функцию форматирования в сочетании с колонками «Timestamp» и «Timestamp Type» мы можем получить все информацию для первичного анализа.

C:\Users\attacker\Documents\ | Default.rdp | 485 | c482e563df19a401941c99888ac2f525 | Archive | FALSE

4648 | A logon was attempted using explicit credentials.

Subject:
Security ID: DomainCorp\Administrator
Account Name: Administrator
Account Domain: DomainCorp
Logon ID: 0x1b38fe
Logon GUID: <00000000-0000-0000-0000-000000000000>
Account Whose Credentials Were Used:
Account Name: VictimUser
Account Domain: DomainCorp
Logon GUID: <00000000-0000-0000-0000-000000000000>
Target Server:
Target Server Name: DestinationServer
Additional Information:
Process Information:
Process ID: 0x5ac
Process Name: C:\Program Files\Internet Explorer\iexplore.exe
Network Information:
Network Address: —
Port: — | Logon | Administrator | SourceSystem

C:\Users\attacker\Documents\ | Default.rdp | 485 | c482e563df19a401941c99888ac2f525 | Archive | FALSE

HKEY_USER\Software\Microsoft\Terminal Server Client\Servers\ | DestinationServer | UsernameHInt | VictimUser

После объединения трех разных таблиц и сортировки полученной информации по полю timestamp обнаруживается, что с аккаунта «DomainCorp\Administrator» из системы «SourceSystem» было подключение к серверу «DestinationServer» при помощи учетной записи «DomainCorp\VictimUser» через RDP.

Статистическая функция МЕДИАНА возвращает медиану из заданного массива числовых данных. Медианой называют число, которое является серединой числового множества. Если в списке нечетное количество значений, то функция возвращает то, что находится ровно по середине. Если же количество значений четное, то функция возвращает среднее для двух чисел.

Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.

Если отсортировать значения в порядке возрастания, то все становится на много понятней:

Возвращает наиболее часто встречающееся значение в массиве числовых данных.

Если отсортировать числа в порядке возрастания, то все становится гораздо понятней:

Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН. Форма записи МОДА также поддерживается в Excel для совместимости.

Как известно, категория Статистические в Excel содержит более 100 самых разноплановых функций. Но, как показывает практика, львиная доля этих функций практически не применяется, а особенно начинающими пользователями. В этом уроке мы постарались познакомить Вас только с самыми популярными статистическими функциями Excel, которые Вы рано или поздно сможете применить на практике. Надеюсь, что данный урок был для Вас полезен. Удачи Вам и успехов в изучении Excel.

Ссылка на основную публикацию