Информационные технологии современного офиса

Л.В. Прохорова

 

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ СОВРЕМЕННОГО ОФИСА

Лекции и вопросы к зачету за 2 семестр

 

Челябинск 2012

Табличный процессор Excel.

Основные понятия.

 

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

В 1979 г. выпускник Гарвардского университета Дэн Бриклин и профессиональный программист Боб Франкстон предложили миру способ автоматизации трудоемких и утомительных операций пересчета, которые всегда требуются при ручном составлении таблиц в бухгалтерском и банковском учете, в проектно-сметных работах, при решении планово-экономических задач. Это была программа VisiCalc (наглядный калькулятор), которая позволяла представлять данные в виде таблицы на экране дисплея, прокручивать эту электронную таблицу по строкам и столбцам и, самое главное, обладала удивительным свойством – в таблице проводился автоматический пересчет содержимого ячеек при изменении значений одной из них. Причем, все действия были наглядны.

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

Табличным процессором, получившим широкое распространение, стал Lotus 1-2-3, ставший стандартом для табличных процессоров, заключающихся в следующем:

 

§ Структура таблицы (пересечения строк и столбцов создают ячейки, куда заносятся данные).

§ Стандартный набор математических и бухгалтерских функций.

§ Возможность сортировки данных.

§ Наличие средств визуального отображения данных (диаграмм).

 

В СССР получили широкое распространение два табличных процессора Super Calc и Quatro Pro. С появлением MS Windows и его приложений стандартом стал табличный процессор Excel.

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

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

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

 

Функции табличных процессоров:

 

1. Создание и редактирование электронных таблиц.
2. Создание многотабличных документов.
3. Оформление и печать электронных таблиц.
4. Построение диаграмм, их модификация и решение экономических задач графическими методами.
5. Создание многотабличных документов, объединенных формулами.
6. Работа с электронными таблицами как с базами данных: сортировка данных, выборка данных по запросам.
7. Создание итоговых и сводных таблиц.
8. Решение оптимизационных задач.
9. Решение экономических задач «что - если» путем подбора параметров.
10. Разработка макрокоманд, настройка среды под потребности пользователя.

 

Окно программы Excel.

 

Самая верхняя строчка – заголовок окна: Microsoft Excel – Книга.xls (по умолчанию) – называются файлы Excel.

Меню такое же, как у Word, только добавлен пункт «Данные».

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

Панель инструментов форматирования тоже как у Word, но с некоторыми новыми кнопками.

 

 

 

Далее идет отличная от Word строка формул, в которой указывается номер текущей ячейки и ее содержимое.

Файл Excel называется Книгой. Книга состоит из трех листов – Лист1, Лист2, Лист3 (можно при необходимости добавлять листы).

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

 

Ячейки.

 

Каждый лист Excel представляет собой таблицу. Столбцы обозначены буквами от А до Z и далее сочетаниями букв от А до IZ, а строки числами – от1 до 65536.

Ячейки расположены на пересечении строк и столбцов, и их номер складывается из номера столбца и номера строки. Например, А1, GA200. Номер ячейки называется ее адресом. Адрес отображается в поле имени в строке формул. Текущая ячейка выделяется черным контуром.

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

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

 

Типы данных.

 

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

По первому символу Excel определяет, что введено:

 

§ Если это буква или апостроф, то это текст.

§ Если цифра, то число.

§ Если знак равенства, то формула.

 

Если текст не входит в ячейку, то можно:

 

• изменить границы ячеек по горизонтали, встав курсором на границу между ми столбцов (широкий крест курсора превращается в черный крестик со стрелками) и, удерживая нажатой левую клавишу мыши, сдвинуть границу на требуемое расстояние;
• объединить несколько ячеек и них записать текст. Для этого необходимо выделить несколько соседних ячеек и выбрать через Главное меню Excel путь: Формат ячейки (появляется диалоговое окно Формат ячеек) – Выравнивание – Объединение ячеек (этот же путь можно выбрать через контекстное меню).
• организовать перенос текста в ячейке по словам: Формат – Ячейки – Выравнивание – Переносить по словам.

(Числовой формат) Ввод чисел.

Если вы набрали последовательность символов, в которую входят цифры, а также знаки «+», «-» (в начале последовательности) или «,» (как разделитель целой и дробной части), эта последовательность считается числом.

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

 

Число можно представить в различных форматах:

 

- экспоненциальный – число будет представлено в виде мантиссы и порядка. Например, 48900 4,89Е+04. Точность числа (кол-во знаков после запятой) можно регулировать с помощью кнопок панели инструментов «Форматирование».

Если число не входит в ячейку, то Excel вместо числа ставит знаки ####. Тогда необходимо раздвинуть границы ячейки.

- денежный формат, к числу добавляется наименование 27,00р.

- дробный формат – число представляется в виде обыкновенной дроби, 2/3

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

- процентный формат. Применяя его не требуется умножение на 100%. Если ввести число0,12 и задать процентный формат, то число примет вид 12%.

 

Для отмены заданного формата следует применить «Правка-Очистка-Форматы».

 

Ввод формулы.

 

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

При его записи следует соблюдать обычные правила алгоритмических языков: арифметические операции выполняются слева направо в порядке старшинства (возведение в степень ^, умножение *, деление /, сложение +, вычитание -).

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

Адреса ячеек набираются только латинским шрифтом.

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

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

 

Редактирование формулы.

 

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

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

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

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

 

Пример:

 

	А	В
			=А1+В1
			=А2+В2
			=А3+В3
			=А4+В4

 

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

 

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

 

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

 

 

	A	B	C	D
	День недели	Кол-во товаров	Сумма	Цена
	Понедельник		=В1*D$1
	Вторник		=В2*D$1
	Среда		=В3*D$1
	Четверг		=В4*D$1
	Пятница		=В5*D$1

 

F4 устанавливает знак $.

1 раз F4 – $A$2

2 раза F4 – A$2

3 раза F4 – $A2

4 раза F4 – A2

 

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

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

При абсолютной адресации адреса ссылок при копировании не изменяются.

$A1; A$1; – смешанная ссылка. $A$1.

 

Перемещение данных.

 

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

Если нажать Ctrl – произойдет копирование данных.

 

Операции в формулах.

 

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

Операция – действие, выполняемое над данными.

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

Рассмотрим арифметические выражения. Формула всегда начинается со знака =. Далее мы набираем арифметическое выражение, которое состоит из операндов

Операнд – величина в выражении, над которой производится операция; арифметические операции.

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

Знаки арифметических операций в Excel: +, -, *, /, ^.

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

 

Имена ячеек.

 

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

Присвоить имя ячейке можно следующими способами:

 

1. Выделить ячейку или блок, щелкнуть мышью в поле имени и набрать в этом поле нужное имя. Ввод имени – Enter.
2. Выделить ячейку или блок, выбрать пункт меню Вставка-Имя-Присвоить. Появляется диалоговое окно.

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

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

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

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

Пример: В ячейке А1 задано число 10. Присвоим ей имя ИТОГО.

В ячейке А2 – число 12.

Найти в ячейке В1 сумму 2-х чисел. В1=ИТОГО+А2.

Перейдем на Лист2. В ячейке А2 задано число 35. В ячейке В1 найти сумму чисел, из ячейки Лист1!А1 и ячейки Лист»!А2.

На Листе2 в ячейку В1 заносим формулу В1=ИТОГО+А2.

 

Значения ошибок в формулах.

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

 

Табличные формулы.

 

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

 

Ввод табличной формулы.

 

Рассмотрим пример.

 

	А	В	С	D
	Год	Приход	Расход	Прибыль
				{=B2:B5-C2:C5}

 

 

Выделим блок D2:D5. В этом блоке активной ячейкой является D2. Наберем знак равенства =. Выделим блок В2:В5, наберем «-», выделим блок С2:С5, нажмем сочетание клавиш Ctrl+Shift+Enter. В ячейках D2:D5 появятся значения, а в строке формул отразится формула единая для всех ячеек: {=B2:B5-C2:C5}.

 

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

 

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

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

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

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

Например, если в ячейке G6 записана формула с функцией возведения в степень = СТЕПЕНЬ (А4;2), значением этой ячейки будет значение А4, возведенное в степень 2.

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

Для упрощения ввода функций в Excel предусмотрен специальный Мастер Функций, который можно вызвать нажатием кнопки f_x на панели инструментов «Стандартная». Предварительно необходимо выделить ячейку, куда будете вводить формулу.

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

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

 

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

 

	А

 

Выделяем ячейку А6

Мастер функций–Статистические–МАКС

Окно для аргумента Число1 Вводим блок А1:А5–Ок

В ячейке А6 появляется значение, а в строке формул: =МАКС(А1:А5)

 

Логические функции.

 

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

Пример.

Обработать результаты экзамена и рассчитать начисление стипендии.

 

	А	В	С	D	E	F	G
	Ф.И.О.	Оценки за экзамен		Начисл. стипендии
		Информ.	Экон.	История	Математ.	Балл
	1.Иванов					=СУММ18(В3;F3)
	2.Зверев
	3.Калачев
	4.Синицына
	5.Писарев

 

Рассчитать стипендию, используя логическую функцию ЕСЛИ, и исходя из условия, что если суммарный балл 16, то стипендия будет 200 руб., иначе – 400 руб.

Сначала находим суммарный балл. Делаем активной ячейку F3. Воспользуемся Мастером функций. Выбираем Математические–Сумм. Аргумент-блок В3:Е3-Ок. Копируем полученную формулу с помощью маркера заполнения в оставшиеся ячейки. Получим результат. Для оценки среднего балла воспользуемся логической функцией ЕСЛИ. Мастер функций–Логические–ЕСЛИ.

Формат функции ЕСЛИ.

ЕСЛИ (<логическое выражение>, <выражение1>, <выражение2>).

Логическое выражение – правило для вычисления (логического) значения.

Если логическое выражение истинно, т.е. условие выполняется, тогда выполняется <выражение1>, иначе (когда условие не выполняется) выполняется <выражение2>.

=ЕСЛИ(F3 16; 400; 200)

Функция ЕСЛИ может быть вложенной, если суммарный балл 16, то стипендия будет 400 руб., если =16, то 300 руб., иначе 200 руб.

Если выполняется условие F3>16, то выражение примет значение 400. Если не выполняется, то будет проверяться следующее условное выражение.

Число вложенных функций ЕСЛИ не должно превышать 7.

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

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

Формат функции «и» =и(<логич.выражение1>, <логич.выражение2>, ….)

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

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

Формат функции «или» =или(<логич.выражение1>, <логич.выражение2>, …)

Пример1. Определить, существует ли треугольник со сторонами a,b,c. Условия существования треугольника: сумма двух любых сторон больше третьей.

=ЕСЛИ (и(а + в>с; а + с>b; в + с>a); существует, не существует)

Пример2. Назначить стипендию, если нет троек за сессию.

=ЕСЛИ(ИЛИ)(В3 3; С3 3; D3 3; Е3 3); нет стипендии, стипендия)

Пример3. Построить график функции на интервале [-2;2], шаг 0,2

 

Поместим х в ячейку А3

B3=ЕСЛИ (А3 0; 5*А3+3; 3*А3/(5*А3+6)

 

Пример 4. Построить график функции на интервале [-2;2], шаг 0,2

 

 

Числовую прямую можно разделить на 3 отрезка:

 

 

С3 = ЕСЛИ (И(А3>= -1; A3<= 1); А3 + 2; А3 – 2)

 

Пример 5. Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 3000, то 5%; если объем до 10000, то 2%; если выше 10000, то 1,5%. Вычислить размер вознаграждения.

В3 =ЕСЛИ(A3<3000;5%*A3;ЕСЛИ(A3<10000;2%*A3;1,5%*A3))

 

Математические функции.

 

Сумма – СУММ (интервал) – вычисляет сумму значений в заданном интервале СУММ(А1:А5).

СУММЕСЛИ – суммирует ячейки, заданные критерием

Синтаксис.

СУММЕСЛИ(диапазон; критерий; диапазон-суммирование:

- Диапазон – диапазон вычисляемых ячеек.

- Критерий – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, «32», «<32», яблоки.

Диапазон–суммирование - фактические ячейки для суммирования.

Ячейки в «диапазон-суммирование» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.

Если «диапазон-суммирование» опущен, то суммируются ячейки в аргументе «диапазон».

Пример.

	А	В
	Стоимость имущества	Комиссионные
	100 000
	200 000
	300 000
	400 000

 

Найти сумму комиссионных для стоимости имущества более 160000.

=СУММЕСЛИ (А2:А5; «>160000»; В2:В5)

Ответ: 63000

КОРЕНЬ (число) – возвращает значение квадратного корня.

СТЕПЕНЬ (число; степень) – возвращает результат возведения в степень. Показатель степени может быть дробным, т.е. корнем любой степени.

ПИ() – возвращает округленное до 15 знаков после запятой число Пи.

 

Статистические функции.

 

СРЗНАЧ (диапазон) – возвращает среднее значение чисел, содержащихся в ячейках заданного диапазона.

А

1 15

2 13

3 18

4 16

5 = СРЗНАЧ(А1:А4)

 

СЧЕТЕСЛИ – подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию

СЧЕТЕСЛИ (диапазон; критерий).

Диапазон – диапазон, в котором нужно подсчитать ячейки.

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

«>50» – выражение

«яблоко» – слово

Пример: Подсчитать количество «5»

 

А

1 4

2 5

3 5

4 4

5 3

6 = СЧЕТЕСЛИ (А1; А5; 5)

 

Ответ: 2

РАНГ – возвращает ранг числа в списке чисел. Ранг числа – это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией).

РАНГ (число; ссылка; порядок)

Число – число, для которого определяется ранг.

Ссылка – массив или ссылка на список чисел. Нечисловые значения игнорируются.

Порядок – число, определяющее способ упорядочения.

- если порядок =0 или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания.

- если порядок – любое ненулевое число, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Замечания. РАНГ присваивает повторяющимся числам одинаковый ранг. При этом наличие повторяющихся чисел влияет на ранг последующих чисел.

Например, если в списке целых чисел дважды встречается число 10, имеющие ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранг 6).

Пример:

 

А

1 Данные

2 7

3 3,5

4 3,5

5 1

6 2

 

Формула:

=РАНГ(А3; А2; А6; 1). Ранг числа 3,5 в приведенном списке (3)

=Ранг (А2; А2; А6; 1). Ранг числа 7 в приведенном списке (5)

ЧАСТОТА – вычисляет распределение значений по интервалам и возвращает вертикальный массив, содержащий на 1 элемент больше, чем массив интервалов.

ЧАСТОТА – вычисляет частоту появления значений в интервале значений и возвращает массив цифр.

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

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

ЧАСТОТА (массив_данных; массив_интервалов)

Массив данных – массив или ссылка на множество данных, для которых вычисляются частоты. Если массив данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей.

Массив_интервалов – массив или ссылка на множество интервалов, в которые группируются значения аргумента массива_данных. Если массив_интервалов не содержим значений, функция ЧАСТОТА возвращает количество элементов в аргументное частота.

Замечания.

- ЧАСТОТА вводится как формула массива после выделения интервала смежных ячеек, в которые нужно вернуть полученный массив распределения.

- количество элементов в возвращаемом массиве на 1 больше числа элементов в массиве массив_интервалов.

Дополнительный элемент в возвращаемом массиве содержит количество значений, больших чем максимальное значение в интервалах.

Например, при подсчете 3-х диапазонов значений (интервалов), введенных в три ячейки, убедитесь в том, что функция ЧАСТОТА возвращает значения в четырех ячейках. Дополнительная ячейка возвращает число значений в массив_данных, больших чем значение границы 3-го интервала.

- ЧАСТОА игнорирует пустые ячейки и тексты.

- формулы, которые возвращают массивы, должны быть введены как формулы массива.

 

ПРИМЕР.

 

	А	В
	Баллы	Интервалы

 

Формула

= ЧАСТОТА (А2:А10; В2:В5)

Описание (результат)

- число баллов в интервале в номером 70 и меньше (1)

- число баллов в интервале 71-79 (2)

- число баллов в интервале 80-89 (4)

- число баллов в интервалах с номером 90 и больше (2)

 

Примечание. Формулу в этом примере необходимо ввести как формулу массива. Выделите диапазон, начиная с ячейки, содержащей формулу, где будет подсчитана частота (число новое + 1). Нажмите клавишу F2, а затем нажмите клавиши Ctrl+Shift+Enter. Если формула не будет введена, как формула массива, единственное значение будет равно 1.

 

Автофильтр.

 

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

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

 

Для выборки сделать следующее:

 

- поставить курсор в базу данных;

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

 

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

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

Отбор по нескольким полям

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

 

С помощью Автофильтра можно решать и более сложные задачи.

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

Например, сделать выборку товаров, проданных из магазина количеством от 200 до 800 шт.

Курсор в базу данных. Данные–Фильтр–Автофильтр. Щелкаем по стрелке в ячейке Количество. В выпадающем меню выбираем Условие, после чего появится диалоговое окно, в котором можно задать условие.

 

 

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

Отмена режима фильтрации: Данные–Фильтр–Автофильтр. Появится исходная таблица, исчезнут кнопки со стрелками в заголовках.

 

Расширенный фильтр.

 

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

 

Расширенный фильтр позволяет:

 

1. Сразу копировать отфильтрованные запасы в другое место рабочего листа, на котором находится исходный список.
2. Сохранять критерий отбора для дальнейшего использования (это нужно, когда список изменяется, а нужно периодически извлекать из него информацию в соответствии с критерием).
3. Показывать в отфильтрованных записях не все столбцы, а только указанные.
4. Объединять операторами И, ИЛИ условия для разных столбцов.
5. Для одного столбца объединять операторами И, ИЛИ боле двух условий.
6. Создавать вычисляемые критерии.
7. Выводить только уникальные значения.

Рассмотрим работу расширенного фильтра на примерах.

 

А	В	С	D	E	F
Канал	Товар	Кол-во	Цена	Выручка	Дата
Магазин	Тетрадь
Магазин	Ручка		1,5
Рынок	Тетрадь		2,5
Рынок	Ручка		1,8
Рынок	Альбом
Магазин	Тетрадь		2,3
Магазин	Ручка		1,7
Магазин	Альбом		6,5
Рынок	Тетрадь		2,8

 

Применив расширенный фильтр, сделать выборку тетрадей, проданных с рынка по цене от 2 до 2,9 руб.

 

Для выборки сделать следующее:

 

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

 

Создадим таблицу критериев в ячейках А12: D13.

Для этого в ячейки А12: D12 введем имена полей, по которым необходимо сделать выборку. Лучше нужные поля скопировать из заголовка базы данных во избежание ошибок. «Канал» в ячейку А12; «Товар» в ячейку В12; «Цена» в ячейки С12 и D12.

 

 

	A	B	C	D
	Канал	Товар	Цена	Цена
	Рынок	Тетрадь	>=2	<=2,9

 

В ячейки А13: D13 ввести условия выборки, т.е. в ячейку А13 скопировать слово «рынок» из списка, в ячейку В13 «тетрадь». В ячейкиС13 и D13 вводим условия С13: >=2; D13: <=2,9

 

2. Выбрать в меню пункт Данные–Фильтр–Расширенный фильтр.

Появится диалоговое окно.

 

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

 

3. Далее щелкнуть ОК. В результате в ячейках, начиная с А20 вправо и вниз, расположится таблица, в которой будут выведены тетради, проданные на рынке по цене от 2 до 2,9 р.

 

 

	A	B	C	D	E	F
	Канал	Товар	Кол-во	Цена	Выручка	Дата
	Рынок	Тетрадь		2,5
	Рынок	Тетрадь		2,8

 

Условие, записанное в одну строку, соответствует логической функции «И».

Рассмотрим пример с использованием функции «ИЛИ».

Сделать выборку тетрадей и ручек, проданных на рынке.

Составляем таблицу критериев:

 

Канал	Товар
Рынок	Тетрадь
Рынок	ручка

 

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

 

	A	B	C	D	E
	Канал	Товар	Кол-во	Цена	Дата
	Магазин	Тетрадь			19.03.04
	Магазин	Ручка		1,5	20.03.04
	Рынок	Тетрадь		2,5	18.03.04
	Рынок	Ручка		1,8	26.03.0 12345678Следующая ⇒ Поделиться с друзьями: Автоматическое растормаживание колес: Тормозные устройства колес предназначены для уменьше­ния длины пробега и улучшения маневрирования ВС при... Адаптации растений и животных к жизни в горах: Большое значение для жизни организмов в горах имеют степень расчленения, крутизна и экспозиционные различия склонов... Эмиссия газов от очистных сооружений канализации: В последние годы внимание мирового сообщества сосредоточено на экологических проблемах... Папиллярные узоры пальцев рук - маркер спортивных способностей: дерматоглифические признаки формируются на 3-5 месяце беременности, не изменяются в течение жизни...  © cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста. Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы! 0.252 с.