Обработка данных с помощью средств MS Excel

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

    Работа  с именами ячеек начинается с  задания им имён.

6.1.1. Создание имён ячеек

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

    1. Задание имён на основе заголовков строки (верхней строки) и столбца (левого столбца):

• Выделить диапазон, которому требуется присвоить имя, включая заголовок строки (столбца).
• Задать команду Вставка ® Имя, а далее выбрать пункт Создать и выбрать параметр «в строке выше», или «в столбце слева», или «в строке ниже», или «в столбце справа» в зависимости от того, где располагается заголовок.

    2. Задание произвольных имен:

• Выделить диапазон, которому требуется присвоить имя, или отдельную ячейку, если имя назначается формуле.
• Задать команду Вставка ® Имя, указать пункт Присвоить, ввести имя в поле Имя и нажать ОК. Другой способ — щелкнуть в окне имен (левая часть строки формул), ввести новое имя, нажать клавишу <Еntег>. При ссылке в формулах на именованную ячейку она будет адресована абсолютно и при копировании формул не возникнут ошибки.

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

6.1.2. Замена адресов ячеек их именами

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

• выделить диапазон, в котором должны быть заменены адреса;
• задать команду Вставка ® Имя ® Применить;
• выделить все применяемые имена в этом диапазоне и нажать кнопку ОК.

6.1.3. Вставка имен в формулы

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

6.2. ИСПОЛЬЗОВАНИЕ ВСТРОЕННЫХ ФУНКЦИЙ ЕХСЕL 

    Поставляемые  вместе с Ехсеl встроенные функции  призваны значительно облегчить  работу пользователя, поскольку сокращают  написание формул. Ранее уже были рассмотрены две функции —  автосуммирования и округления. Для  вызова функции автосуммирования используется кнопка Автосумма. Для задания округления был использован способ написания  на строке формул следующей конструкции: =ОКРУГЛ(АДРЕС; ТОЧНОСТЬ).

    В принципе аналогичным же образом  любая функция может быть вручную  введена в ячейку. Ввод любой функции  начинается со знака равенства [ = ], за которым следует ее название, а затем без интервалов должны идти круглые скобки, в которых  через символ точки с запятой [;] перечисляются аргументы функции. Даже если аргументы отсутствуют (как  у функции =СЕГОДНЯ()), круглые скобки должны присутствовать. Например, в  функции автосуммирования =СУММ(ДИАПАЗОН) названием функции является слово  СУММ, а аргументом — диапазон суммируемых  ячеек. Однако такой ручной способ ввода  не является наилучшим, поскольку требует  от пользователя довольно длительного  и тщательного набора. Поэтому для задания функции используют следующие правила.

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

• нажать клавиши <Shift><FЗ>;
• задать команду Вставка - Функция;
• нажать кнопку Вставка функции [f_х] на стандартной панели.

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

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

 

6.2.1. Суммирование ячеек, удовлетворяющих определенному критерию

    СУММЕСЛИ(ДИАПАЗОН;УСЛОВИЕ;ДИАПАЗОН_СУММИРОВА-НИЯ) — группа математических функций. Функция предназначена для суммирования только ячеек, удовлетворяющих некому критерию.

• ДИАПАЗОН — это диапазон, в котором определяется критерий;
• УСЛОВИЕ — указывается в форме числа, выражения или текста;
• ДИАПАЗОН_СУММИРОВАНИЯ — это диапазон суммируемых ячеек.

    Пример 1.

    В ячейки А1, А2, АЗ, А4, А5 введена информация:

    А1=1000, А2=2000, А3=900, А4=800, А5=1500.

    Требуется подсчитать сумму чисел, значение которых  больше или равно 1000. Результат должен быть получен в ячейке А6.

    Пошаговыми  действиями Мастера функций в  ячейку А6 следует ввести формулу:

    =СУММЕСЛИ(А1 :А5;">=1000").

    В ячейке А6 получится число 4500.

    Измените  значение ячейки АЗ на любое большее 1000. Проследите изменение результата вычислений.

6.2.2. Подсчет количества значений в диапазоне

    Для подсчета количества числовых значений в диапазоне:

    СЧЕТ (ЗНАЧЕНИЕ1;ЗНАЧЕНИЕ2;...) - группа статистических функций.

    Пример 2. В ячейки В1,В2,ВЗ,В4,В5 введена информация:

    В1=«Текст», В2=2000, В3= , В4=800, В5=1500.

    Требуется подсчитать количество ячеек с числовыми  значениями в диапазоне В1:В5.

    Результат должен быть получен в ячейке В6.

    Пошаговыми  действиями Мастера функций в  ячейку В6 следует ввести формулу: 
=СЧЕТ(В1 :В5). В ячейке В6 получится число 3.

    Для подсчета количества всех значений в  списке аргументов и непустых ячеек: СЧЕТЗ(ЗНАЧЕНИЕ1;ЗНАЧЕНИЕ2;...) -- группа статистических функций.

    Если  эта функция будет введена  в примере 2 в ячейку В7, то результат  в ячейке В7 будет равен 4.

6.2.3. Подсчет количества пустых ячеек в диапазоне

    СЧИТАТЬПУСТОТЫ(ДИАПАЗОН) — группа статистических функций.

    Пример 3.

    В ячейки С1,С2,СЗ,С4,С5 скопируйте информацию из соответствующих ячеек столбца  А, оставив пустой ячейку СЗ:

    С1=1000, С2=2000, С3= , С4=800, С5=1500.

    Требуется подсчитать количество пустых ячеек  в диапазоне С1:С5. Результат должен быть получен в ячейке Сб.

    Пошаговыми  действиями Мастера функций в  ячейку С6 следует ввести формулу: =СЧИТАТЬПУСТОТЫ(С1:С5). В ячейке С6 получится число 1.

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

    СЧЕТЕСЛИ(ДИАПАЗОН; УСЛОВИЕ) — группа статистических функций.

• ДИАПАЗОН — это диапазон, в котором определяется критерий;
• УСЛОВИЕ — указывается в виде числа, выражения или текста и определяет какие ячейки надо подсчитывать.

    Пример 4.

    Подсчитать  количество непустых ячеек в диапазоне  С1:С5, значение которых больше или  равно 1000. Результат должен быть получен  в ячейке С7.

    Пошаговыми  действиями Мастера функций в  ячейку С7 следует ввести формулу: =СЧЕТЕСЛИ(С1 :С5;">=1000"). В ячейке С7 получится  число 3.

    Измените  значение ячейки С1 на любое, меньшее 1000. Проследите изменение результата вычислений.

6.2.5. Расчет среднего значения

    СРЗНАЧ(ДИАПАЗОН1; ДИАПАЗОН2;...) - группа статистических функций. В текущую ячейку возвращается среднее  значение для чисел указанного диапазона.

    Пример 5.

    В диапазоне ячеек А1 :А5 из примера 1 определить среднее значение. Результат  должен быть получен в ячейке А7.

    Пошаговыми  действиями Мастера функций в  ячейку А7 следует ввести формулу: =СРЗНАЧ(А1 :А5).

6.2.6. Определение максимального значения

    МАКС(ДИАПАЗОН1; ДИАПАЗОН2;...) — группа статистических функций. В текущую ячейку возвращается максимальное число из данного диапазона.

    Пример 6.

    В диапазоне ячеек А1:А5 из примера 1 определить максимальное значение. Результат  должен быть получен в ячейке А8.

    Пошаговыми  действиями Мастера функций в  ячейку А8 следует ввести формулу: =МАКС(А1 :А5). В ячейке А8 получится число 2000.

6.2.7. Определение минимального значения

    МИН(ДИАПАЗОН1; ДИАПАЗОН2;...) - группа статистических функций. В текущую ячейку возвращается минимальное  число из данного диапазона.

    Пример 7.

    В диапазоне ячеек В1:В5 из примера 2 определить минимальное значение. Результат должен быть получен в ячейке В8.

    Пошаговыми  действиями Мастера функций в  ячейку В8 следует ввести формулу: =МИН(В1:В5). В ячейке В8 получится число 800.

6.2.8. Функции прогнозирования²

    Для прогнозирования используется ряд  функций (ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ) и  диаграммы.

    Функция ПРЕДСКАЗ

    ПРЕДСКАЗ (X; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y; ИЗВЕСТНЫЕ  ЗНАЧЕНИЯ X).

    Функция ПРЕДСКАЗ вычисляет или предсказывает  будущее значение по существующим значениям.

    X — это точка данных, для которой  предсказывается значение.

    ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y — это зависимый массив или интервал данных.

    ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X — это независимый массив или интервал данных.

    Функция РОСТ

    РОСТ(ИЗВЕСТНЫЕ  ЗНАЧЕНИЯ Y; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X; НОВЫЕ  ЗНАЧЕНИЯ X; КОНСТ).

    Функция РОСТ рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения Y для последовательности новых значений X, задаваемых с помощью существующих Х- и Y-значений. Функция рабочего значения РОСТ может применяться также  для аппроксимации существующих Х- и Y-значений экспоненциальной кривой.

    ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y — это множество значений Y, которые уже известны для соотношения Y = b*m^X.

    ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X — это необязательное множество значений х, которые уже  известны для соотношения Y = b*m^Х.

    НОВЫЕ ЗНАЧЕНИЯ X — это новые значения X, для которых РОСТ возвращает соответствующие  значения Y.

    КОНСТ — это логическое значение, которое  указывает, требуется ли, чтобы константа b была равна 1.