1. Форма представления тестовых и числовых данных в компьютере

• Название: 1. Форма представления тестовых и числовых данных в компьютере
• Дата: 16.01.2022
• Размер: 77.23 Kb.
• Имя файла: INFAAA.docx
• Тип: Документы; #332431
• страница: 3 из 6

Смешанные ссылки

Иногда тот факт, что ссылка в формуле при копировании "сползает" относительно исходной ячейки - бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.).

Абсолютные ссылки

Ну, а если к ссылке дописать оба доллара сразу ($C$5) - она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец.

Действительно абсолютные ссылки

Решение заключается в использовании функции ДВССЫЛ, которая формирует ссылку на ячейку из текстовой строки.

Если ввести в ячейку формулу: =ДВССЫЛ("C5"), то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д.

Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:

=ЕСЛИ(ЕПУСТО(ДВССЫЛ("C5"));"";ДВССЫЛ("C5"))

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

1) Оператор МАКС предназначен для определения максимального числа из выборки. Он имеет следующий синтаксис:

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

2) Оператор МЕДИАНА определяет среднее значение в диапазоне чисел. То есть, устанавливает не среднее арифметическое, а просто среднюю величину между наибольшим и наименьшим числом области значений.

3) Формула СТАНДОТКЛОН так же, как и МОДА является пережитком старых версий программы. Сейчас используются современные её подвиды – СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г. Первая из них предназначена для вычисления стандартного отклонения выборки, а вторая – генеральной совокупности. Данные функции используются также для расчета среднего квадратичного отклонения.

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

Основные логические функции, используемые в Excel:

ИСТИНА;

ЛОЖЬ;

ЕСЛИ;

ЕСЛИОШИБКА;

ИЛИ;

И;

НЕ;

ЕОШИБКА;

ЕПУСТО.

Функция ИСТИНА не имеет собственных аргументов и практически всегда используется, как структурный компонент для других логических выражений, так как принимает одно конкретно заданное значение.

Окно аргументов функции ИСТИНА

Функция ЛОЖЬ, напротив, может принимать любые значения, не являющиеся истинными. Как и ИСТИНА, практически всегда используется, как структурный компонент для других сложных выражений. Операторы И и ИЛИ

Синтаксис оператора И выглядит следующим образом:

=И(лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

Оператор И используется в качестве элемента-связки для нескольких условий логического выражения. Важно, чтобы все аргументы оператора имели значение ИСТИНА (если в выражении их несколько), в противном случае вся логическая цепочка будет возвращать значение ЛОЖЬ.

Окно аргументов функции И в Экселе

Синтаксис оператора ИЛИ:

=ИЛИ(лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

В отличие от И, функция ИЛИ будет возвращать значение ИСТИНА даже в случае, когда хотя бы один из используемых аргументов ему соответствует, а все остальные – ложные. Оператор НЕ

Синтаксис функции НЕ: =НЕ(лог_значение).

Количество аргументов оператора НЕ – всегда один. Соответственно, результат функции (ИСТИНА/ЛОЖЬ) полностью зависит только от значения аргумента.

Окно аргументов функции НЕ в Эксель

Операторы ЕСЛИ и ЕСЛИОШИБКА

Общий синтаксис функции ЕСЛИ представляет собой конструкцию:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если-ложь).

Оператор ЕСЛИ используется для построения сложных развернутых логических цепочек. Суть функции – в проверке внесенного в качестве логического выражения условия. Если условие выполняется, и оно истинно, то в качестве результата функции возвращается одно выражение, если же логическое выражение ложно, то в качестве результата функции возвращается другое условие. Синтаксис оператора ЕСЛИОШИБКА имеет вид:

=ЕСЛИОШИБКА(значение;значение_если_ошибка).

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

Пример применения функций: Например, мы Имеем список работников предприятия с положенными им заработными платами. Но, кроме того, всем работникам положена премия. Обычная премия составляет 700 рублей. Но пенсионерам и женщинам положена повышенная премия в размере 1000 рублей. Исключение составляют работники, по различным причинам проработавшие в данном месяце менее 18 дней. Им в любом случае положена только обычная премия в размере 700 рублей. Попробуем составить формулу. Итак, у нас существует два условия, при исполнении которых положена премия в 1000 рублей – это достижение пенсионного возраста или принадлежность работника к женскому полу. При этом, к пенсионерам отнесем всех тех, кто родился ранее 1957 года. В нашем случае для первой строчки таблицы формула примет такой вид: =ЕСЛИ(ИЛИ(C4<1957;D4="жен.");"1000";"700") . Но, не забываем, что обязательным условием получения повышенной премии является отработка 18 дней и более. Чтобы внедрить данное условие в нашу формулу, применим функцию НЕ: =ЕСЛИ(ИЛИ(C4<1957;D4="жен.")*(НЕ(E4<18));"1000";"700"). Для того, чтобы скопировать данную функцию в ячейки столбца таблицы, где указана величина премии, становимся курсором в нижний правый угол ячейки, в которой уже имеется формула. Появляется маркер заполнения. Просто перетягиваем его вниз до конца таблицы. Таким образом, мы получаем таблицу с информацией о величине премии для каждого работника предприятия в отдельности.

21. Типы диаграмм и графиков, способы их построения.

При создании диаграммы на листе Excel, в документе Word или презентации PowerPoint вы можете выбрать из многих вариантов.

Типы диаграмм:

· Column chart

· График

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

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

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

Способы построения диаграмм

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

Способ 2: простейшую диаграмму можно создать, если нажать клавишу F11. В этом случае, используются установки по умолчанию, и диаграмма появится на отдельном листе. Это самый быстрый способ построения простой диаграммы. В Основном меню появится новый пункт Диаграмма, содержащий команды, с помощью которых можно вносить различные изменения в диаграмму.

22. Сортировка данных и способы ее осуществления (одноуровневая, многоуровневая).

Один из способов поиска данных – это сортировка. Сортировка – изменение расположения данных в таблице по определенному признаку (например, в алфавитном порядке, по возрастанию или убыванию и т. д.) для облегчения их просмотра и поиска. Сортировка – это упорядочение данных по возрастанию или убыванию. Самая простая сортировка может быть выполнена с помощью двух пиктограмм - кнопок сортировки (по возрастанию и убыванию). Для этого надо зафиксировать курсор на колонке, по которой производится сортировка, и щелкнуть по одной из пиктограмм. Также выполнение сортировки может производиться с помощью команды «Данные» - «Сортировка» с установкой необходимых параметров. 1. Одноуровневая сортировка:На листе «Сортировка» была произведена одноуровневая сортировка. Для ее выполнения необходимо произвести следующие операции:

необходимо установить курсор в поле списка и ввести команду «Данные» - «Сортировка». При этом должна выделиться вся область списка. Если этого не произошло, то предварительно необходимо выделить весь список, а затем ввести указанную команду. Затем в диалоговом окне «Сортировка диапазона» необходимо установить:

Сортировать по: поле «Цена», по возрастанию.

Затем по: (не сортировать).

В последнюю очередь: (не сортировать).

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

Или отсортировать данные при помощи пиктограммы - кнопки сортировки. 2. Двухуровневая сортировка: Для двухуровневой сортировки необходимо установить курсор в поле списка и ввести команду «Данные» - «Сортировка». В диалоговом окне «Сортировка диапазона» необходимо установить:

Сортировать по: поле «Цена», по возрастанию.

Затем по: поле «Количество членов семьи», по убыванию.

В последнюю очередь: (не сортировать).

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

В диалоговом окне «Сортировка диапазона» необходимо установить:

Сортировать по: поле «Цена», по возрастанию.

Затем по: поле «Количество членов семьи», по убыванию.

В последнюю очередь: поле «Отчетный период», по месяцам (Январь, Февраль, Март, Апрель и т.д.)

После этого необходимо поставить флажок в строке «Идентифицировать поля по подписям».

23. Фильтры и их виды.

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

MS Excel предлагает два вида фильтров:

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

расширенный фильтр, позволяющий выбирать строки по сложным условиям

24. Сущность сводных таблиц и способы их создания

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

· запрос больших объемов данных различными понятными способами;

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

· развертывание и свертывание уровней представления данных для получения точных сведений о результатах, детализация итоговых данных по интересующим вопросам;

· перемещение строк в столбцы или столбцов в строки ("сведение") для просмотра различных сводок на основе исходных данных;

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

· представление кратких, наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.

Создание в Excel

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

Примечание: Ваши данные не должны содержать пустых строк или столбцов. Они должны иметь только однострочный заголовок.

2. На вкладке Вставка нажмите кнопку Сводная таблица.

3. В разделе Выберите данные для анализа установите переключатель Выбрать таблицу или диапазон.

4. В поле Таблица или диапазон проверьте диапазон ячеек.

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

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

25. Назначение метода Подбор параметр.

Подбор параметра служит для определения входной величины, обеспечивающей определенное значение функции. С помощью команды «Подбор параметра» можно определить неизвестное значение, которое будет давать желаемый результат. Эта команда является удобной для решения задач, которые имеют точное целевое значение, зависящее от одного неизвестного параметра. MS Excel находит ответ с помощью последовательных операций и ему достаточно указать целевую ячейку (ее желаемое значение) и изменяемую ячейку – параметр, влияющую на содержимое целевой ячейки. При этом ячейка – это параметр, который должен содержать значение, но не формулу, и влиять на результат, который требуется получить.Для вызова Подбора параметра следует выделить ячейку с формулой, которая должна принять заданное значение и выбрать команду Сервис–Подбор параметра

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

26. Назначение метода Поиск решения.

Что такое Поиск решений

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

Как включить функцию «Поиск решения»

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

Открываем «Файл», нажимая на соответствующее название.

Кликаем на раздел «Параметры».

Затем выбираем подраздел «Надстройки». Тут будут отображены все надстройки программы, внизу появится надпись «Управление». С правой стороны от нее будет всплывающее меню, где следует выбрать «Надстройки Excel». Потом нажимаем «Перейти». 1

На мониторе высветится дополнительное окно «Надстройки». Устанавливаем флажок возле искомой функции и кликаем «ОК».

Нужная функция появится на ленте справа от раздела «Данные».

Подготовительный этап

Перед тем как разместить функцию на ленте, необходимо изучить принцип функционирования опции. К примеру, есть сведения по реализации товаров, указанные в таблице. Задачей является назначение для каждого наименования скидки, которая составляла бы 4.5 млн. рублей. Параметр отображается внутри ячейки, именуемой целевой. Отталкиваясь от нее, рассчитываются прочие параметры.Нашей задачей станет вычисление скидки, на которую умножаются суммы по реализации различной продукции. Эти 2 элемента связываются формулой, прописываемой так: =D13*$G$2. Где в D13 прописывается суммарное количество по реализации, а $G$2 – адрес искомого элемента.

Применение функции и ее настройка

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

Нужно переключиться в раздел «Данные» и нажать «Поиск решения».

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

Далее нужно перейти к настройкам прочих параметров. В разделе «До:» есть возможность задать максимальную и минимальную границу либо точное число.

Потом заполняется поле «Изменяя значения переменных:». Здесь вносятся данные искомой ячейки, которая содержит конкретное значение. Координаты прописываются самостоятельно или кликается соответствующая ячейка в документе.

Затем редактируется вкладка «В соответствии с ограничениями:», где задаются ограничения применяемых данных. К примеру, исключаются десятичные дроби либо отрицательные числа.

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

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