Операторы ссылок

: (двоеточие). Ставится между ссылками на первую и последнюю ячейку диапазона. Такое сочетание является ссылкой на диапазон (A1:A15);

; (точка с запятой). Объединяет несколько ссылок в одну ссылку (СУММ(A1:A15;B1:B15));

(пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8).

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

Билет №53. Возможности и порядок применения надстройки «Анализ данных» в MS Excel.

Надстройки — это компоненты Microsoft Excel, предоставляющие доступ к дополнительным функциям и командам. Двумя из наиболее популярных надстроек являются "Пакет анализа" и "Поиск решения", которые реализуют функции расширенного анализа данных для планирования "что-если". Чтобы использовать эти надстройки, их необходимо установить и активировать.

Установка и активация надстроек "Пакет анализа" 1. Откройте вкладку Файл. 2. Нажмите кнопку Параметры и выберите категорию Надстройки. 3. Убедитесь, что в нижней части диалогового окна Параметры Excel в поле Управление выбран элемент Надстройки Excel, и нажмите кнопку Переход. 4. В диалоговом окне Надстройки установите флажки Пакет анализаи нажмите кнопку ОК. 5. Если отобразится сообщение о том, что надстройку не удается запустить, и выведется предлоожение установить ее, нажмите кнопку Да, чтобы установить надстройку.

Начало работы с пакетом анализа 1. Выделите ячейку на листе. 2. На вкладке Данные в группе Анализ нажмите кнопку Анализ данных. 3. В диалоговом окне Анализ данных выберите нужное средство и нажмите кнопку ОК. 4. В диалоговом окне для выбранного средства введите и выберите нужные параметры. Вопрос №55 MS Excel позволяет использование таблиц в качестве базы данных. Сама по себе структура из строк и столбцов электронной таблицы отлично подходит для создания информационных списков. Списки позволяют работать эффективно с большими наборами данных Список – упорядоченный набор данных, имеющих одинаковую структуру, и состоит из трех основных элементов:1. записи;2. поля;3. заглавной строки. Каждый элемент списка занимает одну строку, в которой данные распределяются по нескольким полям (столбцам). В программе Excel списки являются специальным видом таблиц, для которых имеются операции для их обработки. При работе со списками часто требуются разные представления одних и тех же данных, для этого Excel позволяет использовать встроенные типы сортировки, а также создавать собственные.   Чтобы отсортировать список или базу данных, надо выделить диапазон сортируемых данных и выбрать команду Данные►Сортировка. В диалоговом окне Сортировка диапазона установить параметры сортировки, с их помощью можно задать три уровня ключей, которые будут использоваться при упорядочении для каждого уровня (возрастание/убывание), а также указать наличие (отсутствие) у полей списка имен.   Если не выбрать диапазон данных, программа сделает это автоматически. При этом выделяются все ячейки списка, за исключением первой строки, которая рассматривается как строка заголовков. Если некоторые ячейки списка не содержат данных, необходимо выбрать диапазон вручную, иначе список может сортироваться некорректно. Дело в том, что пустые строки или столбцы списка прерывают автоматически определяемую область сортировки, поэтому часть списка не будет участвовать в сортировке диапазона сортировки.   Сортировку списка можно выполнять по одному, по двум или трем полям как по возрастанию, так и по убыванию. Для выбора параметров сортировки надо щелкнуть по кнопке Параметры. В отрывшемся диалоговом окне Параметрысортировки, исходя из ориентации списка, установить направление сортировки (строки диапазона или столбцы диапазона). Для сортировки данных регистра установить флажок опцииУчитывать.   Чтобы выделить весь список, надо установить в окне предупреждения переключать автоматически, расширить выделенный диапазон. В противном случае сортировки будет выполняться только для выделенной части списка   При сортировке данных просмотр значений всегда осуществляется слева направо. Начальными символами при сортировке считаются цифры, затем - пробелы, далее - символы пунктуации и наконец, буквы. Если значения ячеек содержат пробелы, то результат сортировки может отличаться от ожидаемого.   Используя разные способы сортировки, можно добиться наиболее удобного представления информации в конкретной ситуации. Например, список, содержащий данные «об оптовых покупателях», в первую очередь логичнее сортировать по объему закупок, а затем по имени покупателя или названию контракта.   С помощью кнопок Сортировка по возрастанию и Сортировка по убыванию стандартной панели инструментов можно отсортировать список по первому столбцу выделенного диапазона. Однако при этом не следует выделять только поля списка, так как его оставшаяся часть сортироваться не будет, и целостность данных нарушиться.     3. Сортировка данных. Команда Данные – Сортировка открывает окно, в котором задаются ключи сортировки (столбцы или строки) и порядок сортировки.   Рис. Окно Сортировка диапазона   В MS Excel используется следующий порядок сортировки: 1. числа (от до ); 2. текст и формулы; 3. значение ЛОЖЬ; 4. значение ИСТИНА; 5. значения ошибок; 6. пустые значения. При использовании Сортировки следует помнить следующие замечания. · Порядок сортировки данных в MS Excel зависит от национальных настроек MS Office Windows. · Если необходимо упорядочить числовые величины в алфавитном порядке, следует перед числовыми величинами ставить апостроф либо отформатировать числа как текст, либо ввести число как формулу (например, =”345”). · При сортировке списков, содержащих формулы, следует помнить, что относительные ссылки в формулах при перемещении записей могут привести к неправильным результатам, поэтому в списках лучше использовать формулы с абсолютными ссылками.   · Сортировка в особом порядке позволит отсортировать данные в каком-либо заданном порядке (например, дни недели, месяцы и т.д.). Для этого используется команда Данные – Сортировка – кнопка Параметры – поле Порядок сортировки по 1-му ключу (выбирается вариант порядка сортировки). Часто для этого нужно создавать собственный пользовательский список. Возвращение к обычному порядку – выбрать в поле Порядок сортировки по 1-му ключу элемент Не производится.   Параметры сортировки   · Даты и время должны быть введены в соответствующем формате либо с помощью функций даты или времени, так как для сортировки таких данных MS Excel использует внутреннее представление этих величин.   · Сортировка по полям, большим трех, осуществляется через последовательные сортировки, начиная с ключей самого низшего уровня. Также, можно сортировать по каким–либо признакам, например: -по болезням -по возрасту -по полу     Группировка в Excel   Во время построения больших таблиц полезно использовать группировку данных, чтобы скрывать детальную информацию, представляя в лучшем виде наиболее важную. В то же время, когда необходимо получить уточнение по каким-либо показателям, сгруппированные данные можно быстро отобразить, раскрыв определенную группу.   Шаги 1. Откройте файл, в котором вы хотите работать. Группировка и создание структуры очень полезны для того, чтобы облегчать управление большими по размеру и виду таблицами, но для упрощения в этом примере будет использоваться маленькая таблица.   2. Перейдите на вкладку Данные. Метод 1 из 2: Автоматическое создание структуры документа 1. Перейдите в Данные > Группировать > Создание структуры. При группировке Excel ищет данные, расположенные между логическими элементами (например, заголовками в начале и результатами формул в конце), выделяет их и дает возможность свернуть их. Если Excel сообщил, что не смог автоматически создать структуру, то причиной этого является отсутствие формул в таблице. Вам придется группировать ваши данные вручную (переходите к группировке данных вручную, которая приведена ниже). Посмотрите на вашу таблицу с автоматической структурой. В зависимости от сложности и схемы вашей таблицы, вы заметите, что участок или участки ваших данных были сгруппированы и теперь отмечены полосками вдоль верхней и/или левой стороны ячеек. · Посмотрите на оставшиеся данные. Обычно это ключевая информация, которая позволяет пользователю выбирать, изучать конкретные данные или нет и делает таблицу более удобоваримой. Если хотите, очистите автоматическую структуру. Если Excel неправильно понял ваши данные, и вы не удовлетворены результатами, вы можете очистить структуру, перейдя в Данные > Разгруппировать и выбрав Очистить структуру; здесь вы вручную группируете и разгруппировываете данные. Если все почти правильно, то для того чтобы сэкономить время, вы можете настроить автоматическую структуру. Метод 2 из 2: Группировка данных вручную 1. Выберите ячейки, которые вы хотите в дальнейшем свернуть. В этом примере мы хотим, чтобы после сворачивания данных видимыми оставались только шапка и итог, поэтому мы выбираем все, что находится между ними. 2. Перейдите на Данные > Группировать > Группировать. Если у вас более старая версия Excel, перейдите на Данные > Группировка и Структура > Группировать. 3. В ответ на запрос выберите или Ряды или Колонки. В этом примере мы хотим выровнять данные по вертикали, поэтому мы выбираем Ряды. 4. Посмотрите на сгруппированный участок данных. Сейчас он отмечен полоской вдоль слева от ячеек. 5. Чтобы свернуть данные, щелкните кнопку [-]. 6. При желании выберите и сгруппируйте дополнительные участки. Если это необходимо, разверните текущий выбор (щелкнув на [+]), а затем повторите Шаги 1-4, чтобы создать новые группы. В этом примере мы хотим создать подгруппу первой группы, которая исключает промежуточные итоги, поэтому мы выбираем ячейки между шапкой и промежуточными итогами. · Посмотрите на новый сгруппированный участок данных. · Сверните новую группу и обратите внимание на различия в виде. Чтобы разгруппировать участок данных, выберите его снова и перейдите на Данные > Разгруппировать > Разгруппировать. Для старых версий Excel перейдите на Данные > Группировка и Структура > Разгруппировать Чтобы разгруппировать все участки, перейдите на Данные > Разгруппировать (или Группировка и Структура) > Очистить Структуру.

56. Способы выполнения фильтрации данных в MS Excel.

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

Excel содержит два варианта фильтрации: автофильтр и усиленный фильтр. Автофильтр осуществляет быструю фильтрацию списка в соответствии с содержимым ячеек или в соответствии с простым критерием поиска. Активизация автофильтра осуществляется командой Фильтр — Автофильтр меню Данные (указатель должен быть установлен внутри области списка). Заглавная строка списка в режиме автофильтра содержит в каждом столбце кнопку со стрелкой. Щелчок раскрывает списки, элементы которого участвуют в формировании критерия. Каждое поле (столбец) может использоваться в качестве критерия. Список содержит следующие элементы:

- Все — будут выбраны все записи.

- Первые 10 — предназначены для создания нового списка, состоящего из 10 исходных или наибольших значений предыдущего списка. Число 10 устанавливается по умолчанию. При выборе этого элемента раскрывается окно Наложение условия по списку, в котором можно указать число элементов в создаваемом списке; определить, какие по значению элементы следует выбирать (наибольшие или наименьшие); установить численное или процентное ограничение на количество выводимых элементов. Процентное — задается доля выводимых элементов, имеющих наибольшие или наименьшие значения из всего списка.

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

- Элементы, представляющие собой неповторяющиеся значения данного поля из списка и предназначенные для формирования критерия отбора.

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

- Непустые — предназначены для формирования критерия отбора тех записей из списка, которые имеют значение в данном поле.

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

- возможность сохранения критериев и их многократного использования;

- возможность оперативного внесения изменений в критерии в соответствии с потребностями;

- возможность располагать результаты фильтрации в любой области рабочего листа.

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

Фильтрация списка с помощью усиленного фильтра выполняется командой Фильтр — Расширенный фильтр меню Данные. В окне команды Расширенный фильтр следует указать:

1) в поле ввода Исходный диапазон — диапазон ячеек, содержащих список;

2) в поле ввода Диапазон условий — диапазон ячеек, содержащих критерий отбора;

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

4) с помощью переключателя Обработка определить расположение результатов фильтрации на рабочем листе:

Фильтровать список на месте — означает, что список остается на месте, ненужные строки скрываются;

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

5) Параметр Только уникальные записи — означает, что выборка должна содержать только уникальные записи в соответствии с критериями отбора.

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

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

2. Критерий отбора содержит несколько условий, накладываемых на несколько столбцов (полей) одновременно. Здесь возможны следующие варианты:

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

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

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

3. Вычисляемый критерий. Условия отбора могут содержать формулу. Полученное в результате вычисления формулы значение будет участвовать в сравнении. Правила формирования вычисляемого критерия следующие:

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

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

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

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

вычисляемые критерии можно сочетать с невычисляемыми;

не следует обращать внимание на результат, выдаваемый формулой в области критерия (обычно ИСТИНА или ЛОЖЬ).

Вопрос №57

Дата добавления: 2015-08-17