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

Где могут быть расположены новые ячейки относительно выделенных (отметьте возможные варианты)? выше ниже справа слева

⦁ Нужно построить электронную таблицу, которая рассчитывает стоимость покупок в магазине:

Запишите адреса всех ячеек, в которых находятся исходные данные (числа):
Запишите адреса всех ячеек, в которые нужно внести формулы:
Какими двумя способами можно подсчитать общую сумму? Запишите два варианта формулы, которую можно ввести в ячейку D6:
1)
2)
Какой из вариантов вам кажется лучшим?

Выделение данных с помощью условного форматирования

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

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

Применение условного форматирования в отчете сводной таблицы

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

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

Примечание: В иерархии данных дочерние элементы не наследуют условное форматирование от родительских, а родительские — от дочерних.

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

Определение области по выделению

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

Определение области по полю значения.

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

Определение области по соответствующему полю.

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

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

• столбец и поле родительской строки;
• строка и поле родительского столбца.

Использование экспресс-анализа для применения условного форматирования

Примечание: Экспресс-анализ недоступен в Excel 2010 и более ранних версиях.

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

1. Выделите данные, к которым необходимо применить условное форматирование. В правом нижнем углу выделенной области появится кнопка Экспресс-анализ.
2. Нажмите кнопку Экспресс-анализили клавиши CTRL+Q.
3. В появившемся всплывающем окне на вкладке Форматирование наведите указатель мыши на различные варианты для предварительного просмотра данных, затем выберите подходящий вариант форматирования.

• Варианты форматирования, которые отображаются на вкладке Форматирование, зависят от выбранных данных. Если выделенный фрагмент содержит только текст, будут доступны параметры Текст содержит, Повторяющиеся, Уникальные значения, Равно и Очистить формат. Если выделенный диапазон содержит только числа или числа и текст, доступны параметры Гистограмма, Цветовая шкала, Набор значков, Больше, Первые 10 % и Очистить формат.
• Динамический просмотр будет работать только для тех вариантов, которые поддерживаются для данных. Например, если выделенный диапазон не содержат повторяющихся значений и выбрать Повторяющиеся, динамический просмотр не будут работать.

Если нужно посмотреть видео об использовании экспресс-анализа для применения условного форматирования, см. Видео: использование условного форматирования.

Скачивание образца книги

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

Форматирование ячеек с помощью двухцветной шкалы

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

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

Совет: Если какие-либо из выделенных ячеек содержат формулу, возвращающую ошибку, условное форматирование не применяется к этим ячейкам. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА для возврата значения, отличного от ошибки.

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Цветовые шкалы.
3. Выберите двухцветную шкалу. Просмотрите значки цветовой шкалы и определите значок, соответствующий двухцветной шкале. Верхний цвет соответствует высоким значениям, нижний цвет — низким значениям.

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

Расширенное форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
3. Выполните одно из указанных ниже действий.
   • Чтобы добавить совершенно новый вариант условного форматирования, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
   • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
   • Для изменения условного форматирования выполните указанные ниже действия.
     1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
   • к выделенным ячейкам, чтобы выбрать ячейки по выделению;
   • ко всем ячейкам, содержащим значения , чтобы выбрать ячейки с определенной меткой.
   • ко всем ячейкам, содержащим значения для , чтобы выбрать ячейки с определенной меткой, кроме промежуточных и общих итогов.
5. В разделе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений (по умолчанию).
6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Двухцветная шкала.
7. В полях Тип параметров Минимальное значение и Максимальное значение выберите их типы.
   • Форматирование минимального и максимального значений. Выберите минимальное значение и максимальное значение. В этом случае не вводите минимальное и максимальноезначения.
   • Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное и максимальноезначения.
   • Форматирование процентного значения. Введите минимальное и максимальноезначения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента. Рекомендуется использовать процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений является пропорциональным.
   • Форматирование процентиля. Выберите элемент Процентиль, а затем введите значения параметров Минимальное значение и Максимальноезначение. Допустимыми являются значения процентилей от 0 (нуля) до 100. Используйте процентиль, если необходимо визуализировать группу высоких значений (например, верхнюю 20ю процентиль) с одним оттенком, а группу низких значений (например, нижнюю 20ю процентиль) — с другим, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
   • Форматирование результата формулы. Выберите элемент Формула, а затем введите минимальное и максимальное значения.
     • Формула должна возвращать число, дату или время.
     • Начинайте ввод формулы со знака равенства (=).
     • Недопустимая формула не позволит применить форматирование.
     • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
       • Убедитесь, что минимальное значение меньше, чем максимальное.
       • Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения.

       Форматирование ячеек с помощью трехцветной шкалы

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

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

       Быстрое форматирование

       

       1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
       2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Цветовые шкалы.
       3. Выберите трехцветную шкалу. Верхний цвет соответствует высоким значениям, центральный цвет — средним значениям, а нижний цвет — низким значениям. Просмотрите значки цветовой шкалы и определите значок, соответствующий трехцветной шкале.

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

       Расширенное форматирование

       

       1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
       2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
       3. Выполните одно из указанных ниже действий.
          • Чтобы добавить новый вариант условного форматирования, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
          • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
          • Для изменения условного форматирования выполните указанные ниже действия.
            1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
            2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
            3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
       4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
          • чтобы выбрать поля по выделению, выберите только эти ячейки;
          • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
          • чтобы выбрать поля по полю значения, выберите все ячейки .
       5. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений.
       6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Трехцветная шкала.
       7. Выберите тип параметров Минимальное значение, Среднее значение и Максимальное значение. Выполните одно из указанных ниже действий.
          • Форматирование минимального и максимального значений. Выберите элемент Среднее значение. В этом случае не вводите минимальное и максимальноезначения.
          • Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное, среднее и максимальное значения.
          • Форматирование процентного значения. Выберите элемент Процент, а затем введите минимальное, среднее и максимальное значения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента. Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
          • Форматирование процентиля. Выберите элемент Процентиль, а затем введите Минимальное значение, Среднее значение и Максимальное значение. Допустимыми являются значения процентилей от 0 (нуля) до 100. Используйте процентиль, если необходимо визуализировать группу высоких значений (например, верхнюю 20юпроцентиль) с одним оттенком, а группу низких значений (например, нижнюю 20юпроцентиль) — с другим, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
          • Форматирование результата формулы. Выберите элемент Формула, а затем введите минимальное, среднее и максимальное значения. Формула должна возвращать число, дату или время. Начинайте ввод формулы со знака равенства (=). Недопустимая формула не позволит применить форматирование. Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
            • Можно задать минимальное, среднее и максимальное значения для диапазона ячеек. Убедитесь, что минимальное значение меньше среднего, а оно, в свою очередь, меньше максимального.
            • Для параметров Минимальное значение, Среднее значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения, процентиль для среднего значения и процентный тип для максимального значения.
            • Во многих случаях среднее значение по умолчанию, равное 50 процентам, оптимально, но его можно изменить с учетом конкретных требований.
            • Чтобы выбрать дополнительные цвета или создать пользовательский цвет, нажмите кнопку Другие цвета.
            • Выбранная цветовая шкала появится в окне предварительного просмотра.

            Форматирование ячеек с помощью гистограмм

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

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

            

            Совет: Если какая-либо из ячеек в диапазоне содержит формулу, возвращающую ошибку, условное форматирование к ней не применяется. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА для возврата значения (например, 0 или «Н/Д»), отличного от ошибки.

            Быстрое форматирование

            

            1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
            2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выделите пункт Гистограммы, а затем выберите гистограмму.

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

            Расширенное форматирование

            

            1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
            2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
            3. Выполните одно из указанных ниже действий.
               • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
               • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
               • Для изменения условного форматирования выполните указанные ниже действия.
               • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                 2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                 3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
            4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
               • чтобы выбрать поля по выделению, выберите только эти ячейки;
               • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
               • чтобы выбрать поля по полю значения, выберите все ячейки .
            5. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений.
            6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Гистограмма.
            7. Выберите значения Минимальное и Максимальное для типа. Выполните одно из указанных ниже действий.
               • Форматирование минимального и максимального значений. Выберите Минимальное значение и Максимальное значение. В этом случае не вводите Минимальное значение и Максимальное значение.
               • Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное и максимальноезначения.
               • Форматирование процентного значения. Выберите элемент Процент, а затем введите минимальное и максимальное значения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента. Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
               • Форматирование процентиля. Выберите элемент Процентиль, а затем введите значения параметров Минимальное значение и Максимальное значение. Допустимыми являются значения процентилей от 0 (нуля) до 100. Используйте процентили, если необходимо визуализировать группу высоких значений (например, верхнюю 20ю процентиль) в одной пропорции гистограммы и группу низких значений (например, нижнюю 20ю процентиль) — в другой, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
               • Форматирование результата формулы. Выберите элемент Формула, а затем введите значения параметров Минимальное значение и Максимальное значение.
                 • Формула должна возвращать число, дату или время.
                 • Начинайте ввод формулы со знака равенства (=).
                 • Недопустимая формула не позволит применить форматирование.
                 • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
               • Убедитесь, что минимальное значение меньше, чем максимальное.
               • Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения.

               Форматирование ячеек с помощью набора значков

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

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

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

               

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

               Совет: Если какие-либо из выделенных ячеек содержат формулу, возвращающую ошибку, условное форматирование не применяется к этим ячейкам. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА для возврата значения (например, 0 или «Н/Д»), отличного от ошибки.

               Быстрое форматирование

               

               1. Выделите ячейки, к которым необходимо применить условное форматирование.
               2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выделите пункт Набор значков, а затем выберите набор значков.

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

               Расширенное форматирование

               

               1. Выделите ячейки, к которым необходимо применить условное форматирование.
               2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
               3. Выполните одно из указанных ниже действий.
                  • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                  • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                  • Для изменения условного форматирования выполните указанные ниже действия.
                    1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                    2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                    3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
               4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                  • чтобы выбрать поля по выделению, выберите только эти ячейки;
                  • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
                  • чтобы выбрать поля по полю значения, выберите все ячейки .
               5. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений.
               6. В разделе Измените описание правила в списке Формат стиля выберите пункт Набор значков.
                  1. Выберите набор значков. По умолчанию используется набор 3 сигнала светофора без обрамления. Число значков, операторы сравнения, заданные по умолчанию, и пороговые значения для каждого значка могут быть различны для разных наборов значков.
                  2. Вы можете изменить операторы сравнения и пороговые значения. Размеры диапазонов значений по умолчанию для всех значков одинаковы, но их можно изменить в соответствии с конкретными требованиями. Убедитесь, что пороговые значения находятся в логической последовательности — от высших к низшим, сверху вниз.
                  3. Выполните одно из указанных ниже действий.
                     • Форматирование числового значения, значения даты или времени. Выберите элемент Число.
                     • Форматирование процентного значения. Выберите элемент Процент. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента. Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
                     • Форматирование процентиля. Выберите элемент Процентиль. Допустимыми являются значения процентилей от 0 (нуля) до 100. Используйте процентили, если необходимо визуализировать группу максимальных значений (например, верхнюю 20ю процентиль) с помощью одного значка, а группу минимальных значений (например, нижнюю 20ю процентиль) — с помощью другого, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
                     • Форматирование результата формулы. Выберите элемент Формула, а затем введите формулы в каждое поле Значение.
                       • Формула должна возвращать число, дату или время.
                       • Начинайте ввод формулы со знака равенства (=).
                       • Недопустимая формула не позволит применить форматирование.
                       • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
                  4. Чтобы первый значок соответствовал меньшим значениям, а последний — большим, выберите параметр Обратный порядок значков.
                  5. Для отображения только значка, но не значения в ячейке, выберите параметр Показать только значок.
                     • Может потребоваться скорректировать ширину столбцов, чтобы они могли вместить значки.
                     • Отображаемый размер значка зависит от размера шрифта, используемого в данной ячейке. При увеличении размера шрифта пропорционально увеличивается и размер значка.

                     Форматирование ячеек, содержащих текст, числовые значения, дату или время

                     Чтобы удобнее находить определенные ячейки, можно отформатировать их с помощью оператора сравнения. Например, в инвентарном перечне, отсортированном по категориям, можно выделить желтым цветом товары, которых осталось менее 10 штук. В сводном листе розничного магазина можно выделить все магазины с доходом более 10 %, с объемом продаж менее 100 000 долларов и с регионом «Юго-восток».

                     В приведенных здесь примерах демонстрируются встроенные критерии условного форматирования, такие как «больше чем» и «%_лучших». Города с численностью населения более 2 000 000 выделяются зеленым цветом фона, а 30 % лучших (самых высоких) показателей среднемесячной максимальной температуры — оранжевым.

                     

                     Примечание: Невозможно применить условное форматирование к полям в области «Значение» отчета сводной таблицы по тексту или по дате (можно только по числам).

                     Быстрое форматирование

                     

                     1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                     2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила выделения ячеек.
                     3. Выберите нужную команду, например Между, Текст содержит или Дата.
                     4. Введите значения, которые необходимо использовать, и выберите формат.

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

                     Расширенное форматирование

                     

                     1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                     2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                     3. Выполните одно из указанных ниже действий.
                        • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                        • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                        • Для изменения условного форматирования выполните указанные ниже действия.
                        • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                          2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на этом или других листах и нажмите кнопку Развернуть диалоговое окно.
                          3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                     4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                        • чтобы выбрать поля по выделению, выберите только эти ячейки;
                        • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
                        • чтобы выбрать поля по полю значения, выберите все ячейки .
                     5. В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.
                     6. В группе Измените описание правила в поле со списком Форматировать только ячейки, для которых выполняется следующее условие выполните одно из указанных ниже действий.
                        • Форматирование по числу, дате или времени. Выберите элемент Значение ячейки и задайте оператор сравнения, а затем введите число, дату или время. Например, можно выбрать команду Между, а затем ввести числа 100 и 200 или выбрать команду Равно и ввести дату 01.01.2009. Вы также можете ввести формулу, которая возвращает число, дату или время.
                          • Начинайте ввод формулы со знака равенства (=).
                          • Недопустимая формула не позволит применить форматирование.
                          • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
                        • Форматирование по тексту. Выберите элемент Текст, задайте оператор сравнения и введите текст. Например, можно выбрать команду Содержит и ввести значение Серебро или выбрать команду Начиная с и ввести значение Три. Кавычки включаются в строку поиска, допускается использование подстановочных знаков. Максимальная длина строки — 255 знаков. Можно также ввести формулу, которая возвращает текст.
                          • Начинайте ввод формулы со знака равенства (=).
                          • Недопустимая формула не позволит применить форматирование.
                          • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

                          Форматирование только первых или последних значений

                          Можно найти максимальное и минимальное значения в диапазоне ячеек на основе указанного порогового значения. Например, можно найти 5 самых популярных продуктов в региональном отчете, 15 % наименее популярных продуктов в клиентском опросе или 25 самых высоких зарплат в отчете по персоналу отдела.

                          Быстрое форматирование

                          

                          1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила отбора первых и последних значений.
                          3. Выберите нужную команду, например Первые 10 элементов или Последние 10 %.
                          4. Введите значения, которые необходимо использовать, и выберите формат.

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

                          Расширенное форматирование

                          

                          1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                          3. Выполните одно из указанных ниже действий.
                             • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                             • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                             • Для изменения условного форматирования выполните указанные ниже действия.
                             • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                               2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                               3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                          4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                             • чтобы выбрать поля по выделению, выберите только эти ячейки;
                             • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
                             • чтобы выбрать поля по полю значения, выберите все ячейки .
                          5. В группе Выберите тип правила выберите пункт Форматировать только первые или последние значения.
                          6. В группе Измените описание правила в поле со списком Форматировать значения, которые относятся к выберите пункт Сверху или Снизу.
                          7. Выполните одно из указанных ниже действий.
                             • Чтобы указать верхнее или нижнее пороговое значение, введите число, а затем снимите флажок % от выделенного диапазона. Допустимыми являются значения от 1 до 1000.
                             • Чтобы указать верхнее или нижнее процентное значение, введите число, а затем установите флажок % от выделенного диапазона. Допустимыми являются значения от 1 до 100.
                          8. Кроме того, можно настроить применение формата к полям в области «Значения» отчета сводной таблицы, область которых определяется соответствующим полем. По умолчанию условное форматирование основано на всех видимых значениях. Однако при определении области по соответствующему полю можно применить условное форматирование не ко всем видимым значениям, а к каждому из следующих сочетаний:
                             • столбец и поле его родительской строки (вариант каждой группы столбцов);
                             • строка и поле ее родительского столбца (вариант каждой группы строк).
                          9. Чтобы задать формат, нажмите кнопку Формат. Отобразится диалоговое окно Формат ячеек.
                          10. Выберите формат чисел, шрифта, рамки или заполнения, который необходимо применять, если значение в ячейке соответствует условию, а затем нажмите кнопку ОК. Можно выбрать несколько форматов. Выбранные форматы появятся в окне предварительного просмотра.

                          Форматирование только значений, находящихся выше или ниже среднего

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

                          Быстрое форматирование

                          

                          1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила отбора первых и последних значений.
                          3. Выберите нужную команду, например Выше среднего или Ниже среднего.
                          4. Введите значения, которые необходимо использовать, и выберите формат.

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

                          Расширенное форматирование

                          

                          1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                          3. Выполните одно из указанных ниже действий.
                             • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                             • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                             • Для изменения условного форматирования выполните указанные ниже действия.
                             • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                               2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                               3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                          4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                             • чтобы выбрать поля по выделению, выберите только эти ячейки;
                             • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
                             • чтобы выбрать поля по полю значения, выберите все ячейки .
                          5. В группе Выберите тип правила выберите пункт Форматировать только значения, которые находятся выше или ниже среднего.
                          6. В группе Измените описание правила в поле со списком Форматировать значения, для которых выполняется следующее условие выполните одно из указанных ниже действий.
                             • Для форматирования ячеек, значения в которых больше или меньше среднего значения для всех ячеек в диапазоне, выберите пункт Выше или Ниже.
                             • Для форматирования ячеек, значения в которых больше или меньше одного, двух или трех значений стандартного отклонения для всех ячеек в диапазоне, выберите стандартное отклонение.
                          7. Кроме того, можно настроить применение формата к полям в области «Значения» отчета сводной таблицы, область которых определяется соответствующим полем. По умолчанию условное форматирование основано на всех видимых значениях. Однако при определении области по соответствующему полю можно применить условное форматирование не ко всем видимым значениям, а к каждому из следующих сочетаний:
                             • столбец и поле его родительской строки (вариант каждой группы столбцов);
                             • строка и поле ее родительского столбца (вариант каждой группы строк).
                          8. Нажмите кнопку Формат для отображения диалогового окна Формат ячеек.
                          9. Выберите формат чисел, шрифта, рамки или заполнения, который необходимо применять, если значение в ячейке соответствует условию, а затем нажмите кнопку ОК. Можно выбрать несколько форматов. Выбранные форматы появятся в поле Просмотр.

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

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

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

                          

                          Быстрое форматирование

                          

                          1. Выделите ячейки, к которым необходимо применить условное форматирование.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила выделения ячеек.
                          3. Выберите вариант повторяющиеся значения.
                          4. Введите значения, которые необходимо использовать, и выберите формат.

                          Расширенное форматирование

                          

                          1. Выделите ячейки, к которым необходимо применить условное форматирование.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                          3. Выполните одно из указанных ниже действий.
                             • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                             • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                             • Для изменения условного форматирования выполните указанные ниже действия.
                             • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист или таблица.
                               2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                               3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                          4. В группе Выберите тип правила выберите параметр Форматировать только уникальные или повторяющиеся значения.
                          5. В разделе Измените описание правила в списке Форматировать все выберите значение уникальные или повторяющиеся.
                          6. Нажмите кнопку Формат для отображения диалогового окна Формат ячеек.
                          7. Выберите формат чисел, шрифта, рамки или заполнения, который необходимо применять, если значение в ячейке соответствует условию, а затем нажмите кнопку ОК. Можно выбрать несколько форматов. Выбранные форматы появятся в окне предварительного просмотра.

                          Создание собственных правил условного форматирования

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

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

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

                          

                          1. Выделите ячейки, которые нужно отформатировать.
                          2. На вкладке Главная нажмите кнопку Условное форматирование и выберите пункт Создать правило.
                          3. Создайте правило и задайте для него параметры форматирования, а затем нажмите кнопку ОК. Если нужные варианты отсутствуют, можно определить форматируемые ячейки с помощью формулы — инструкции см. в следующем разделе.

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

                          Если при создании собственного правила условного форматирования отсутствуют нужные варианты, можно использовать логическую формулу, чтобы задать условия форматирования. Например, можно сравнить значения в выбранных ячейках с результатом, возвращенным функцией, или оценить данные в ячейках за пределами выделенного диапазона, которые могут находиться на другом листе этой же книги. Формула должна возвращать значение Истина или Ложь (1 или 0), но вы можете использовать условную логику для объединения набора соответствующих условных форматов, таких как различные цвета для каждого из небольших наборов текстовых значений (например, названия категорий товаров).

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

                          Совет: Если какие-либо ячейки содержат формулу, возвращающую ошибку, условное форматирование не применяется к таким ячейкам. Для решения этой проблемы используйте в формуле функции ЯВЛЯЕТСЯ или функцию ЕСЛИОШИБКА, чтобы возвращать указанное вами значение (например, 0 или «Н/П») вместо значения ошибки.

                          1. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                          2. Выполните одно из указанных ниже действий.
                             • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                             • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                             • Для изменения условного форматирования выполните указанные ниже действия.
                             • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                               2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на этом или других листах и нажмите кнопку Развернуть диалоговое окно.
                               3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                          3. В разделе Применить правило выполните одно из следующих действий, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                             • Чтобы определить область по выделенному фрагменту , щелкните Выделенные ячейки.
                             • Чтобы определить область по соответствующему полю , щелкните ко всем ячейкам, содержащим значения .
                             • Чтобы определить область по полю «Значение» , выберите ко всем ячейкам, содержащим значения для .
                          4. В группе Выберите тип правила нажмите кнопку Использовать формулу для определения форматируемых ячеек.
                             1. В группе Измените описание правила введите формулу в поле со списком Форматировать значения, для которых следующая формула является истинной.

                                Можно выбрать несколько форматов. Выбранные форматы появятся в окне предварительного просмотра. Пример 1. Применение двух условных форматов с условиями, использующими проверку с операторами И и ИЛИ В приведенном ниже примере показано использование двух правил условного форматирования. Если не применяется первое правило, то применяется второе. Первое правило: покупатель дома выделил до 75 000 долларов в качестве первого взноса и 1500 в месяц в качестве выплат по закладной. Если сумма первого взноса и ежемесячные платежи отвечают требованиям, ячейки B4 и B5 будут отформатированы зеленым цветом. Второе правило: если первый взнос или ежемесячный платеж не укладываются в бюджет покупателя, ячейки B4 и B5 будут отформатированы красным цветом. Измените некоторые значения, например годовую процентную ставку, срок кредитования, сумму первого взноса или сумму покупки, чтобы увидеть, что произойдет в ячейках, к которым применено условное форматирование.

                                Формула для первого правила (применяется зеленый цвет)	=И(ЕСЛИ($B$4 <=75000,1),ЕСЛИ(ABS($B$5)<=1500,1))
                                Формула для второго правила (применяется красный цвет)	=ИЛИ(ЕСЛИ($B$4>=75000;1);ЕСЛИ(ABS($B$5)>=1500;1))

                                Пример 2. Заливка каждой второй строки с помощью функций ОСТАТ и СТРОКА Применение условного форматирования ко всем ячейкам листа выполняет заливку синим цветом каждой второй строки в диапазоне. Функция ОСТАТ возвращает остаток от деления числа (первый аргумент) на делитель (второй аргумент). Функция СТРОКА возвращает номер текущей строки. При делении номера текущей строки на 2 в остатке всегда будет получаться 0 для четных чисел и 1 для нечетных чисел. Поскольку 0 — это ЛОЖЬ, а 1 — ИСТИНА, форматирование будет применяться к каждой нечетной строке. Правило использует такую формулу: =MOD(ROW(),2)=1.

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

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

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

                                МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РЕСПУБЛИКИ КАЗАХСТАН Казахский агротехнический университет им. С.Сейфуллина АМИРХАНОВА А.А. ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО ДИСЦИПЛИНЕ «ИНФОРМАТИКА» Астана, 2015 1 Показать больше

                                МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РЕСПУБЛИКИ КАЗАХСТАН Казахский агротехнический университет им. С.Сейфуллина АМИРХАНОВА А.А. ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО ДИСЦИПЛИНЕ «ИНФОРМАТИКА» Астана, 2015 1 Спрятать

                                • Похожие публикации
                                • Поделиться
                                • Код вставки
                                • Добавить в избранное
                                • Комментарии

                                ЕН. 02 Информатика МУ к ПЗ для 2 курса для специальности 23.02.03

                                Методические указания к выполнению практических занятий в Microsoft Excel для обучающихся 2 курса образовательных организаций среднего профессионального образования очной формы обучения базовой подготовки

                                Нижневартовск 2019

                                

                                Методические указания к выполнению практических занятий в Microsoft Excel для обучающихся 2 курса образовательных организаций среднего профессионального образования очной формы обучения базовой подготовки по ЕН.02 Информатика специальности 23.02.03 Техническое обслуживание и ремонт автомобильного транспорта (23.00.00 ТЕХНИКА И ТЕХНОЛОГИИ НАЗЕМНОГО ТРАНСПОРТА), разработаны в соответствии с:

                                1. Федеральным государственным образовательным стандартом (далее – ФГОС) по специальности среднего профессионального образования (далее – СПО) 23.02.03 Техническое обслуживание и ремонт автомобильного транспорта, утвержденным приказом Министерства образования и науки № 383 от 22.04.2014г.

                                2. Рабочей программой учебной дисциплины ЕН.02 Информатика, утвержденной на методическом совете ННТ (филиал) ФГБОУ ВО «ЮГУ» протоколом № 4 от 11.06.2018 г.

                                Супрун Анна Валентиновна, преподаватель Нижневартовского нефтяного техникума (филиала) ФГБОУ ВО «Югорский государственный университет».

                                1. Хайбулина Р.И., преподаватель высшей категории Нижневартовского нефтяного техникума (филиала) ФГБОУ ВО «ЮГУ».

                                3. Павлий Т.В., преподаватель первой квалификационной категории БУ ПО ХМАО — Югры Нижневартовского строительного колледжа.

                                Замечания, предложения и пожелания направлять в Нижневартовский нефтяной техникум (филиал) федерального государственного бюджетного образовательного учреждения высшего образования «Югорский государственный университет» по адресу: 628615, Тюменская обл., ХантыМансийский автономный округ, г. Нижневартовск, ул. Мира, 37.

                                ©Нижневартовский нефтяной техникум (филиал) ФГБОУ ВО «ЮГУ»,

                                ВЕДЕНИЕ

                                В связи с увеличением объемов обрабатываемой информации и развитием компьютерного рынка особенно актуальным становится применение пакетов прикладных программ для обработки и анализа экономической информации. Табличный процессор MS Microsoft Excel является одним из программно-инструментальных средств, которое может быть применено при решении широкого класса задач финансово-экономического характера. Его используют в своей деятельности экономисты, финансисты, маркетологи, менеджеры и др., поскольку множество задач, стоящих перед ними, носят учетно-аналитический характер и требуют табличной компоновки данных с последующим их анализом, сортировкой, группировкой, подведением итогов, построением диаграмм и графиков. Целью является формирование практических навыков использования инструментальных средств табличного процессора Microsoft Excel для решения экономических задач. Он состоит их 11 практических работ, которые содержат как подробно разобранные примеры, так и задачи для самостоятельной работы обучающихся. Предназначено для обучающихся 2 курса специальности 23.02.03 Техническое обслуживание и ремонт автомобильного транспорта.

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

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

                                — основные понятия автоматизированной обработки информации;

                                — общий состав и структуру персональных электронновычислительных машин (ЭВМ) и вычислительных систем;

                                — базовые системы, программные продукты и пакеты прикладных программ.

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

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

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

                                ОК 3. Принимать решения в стандартных и нестандартных ситуациях и нести за них ответственность.

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

                                ОК 5. Использовать информационно-коммуникационные технологии в профессиональной деятельности.

                                ОК 6. Работать в коллективе и команде, эффективно общаться с коллегами, руководством, потребителями.

                                ОК 7. Брать на себя ответственность за работу членов команды (подчиненных), результат выполнения заданий.

                                ОК 8. Самостоятельно определять задачи профессионального и личностного развития, заниматься самообразованием, осознанно планировать повышение квалификации.

                                ОК 9. Ориентироваться в условиях частой смены технологий в профессиональной деятельности. В результате освоения учебной дисциплины ЕН.02 Информатика обучающийся должен овладевать профессиональными компетенциями, включающими в себя способность:

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

                                ПК 1.2. Осуществлять технический контроль при хранении, эксплуатации, техническом обслуживании и ремонте автотранспорта.

                                ПК 1.3. Разрабатывать технологические процессы ремонта узлов и деталей.

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

                                ПК 2.2. Контролировать и оценивать качество работы исполнителей работ.

                                ПК 2.3. Организовывать безопасное ведение работ при техническом обслуживании и ремонте автотранспорта.

                                ПЕРЕЧЕНЬ ПРАКТИЧЕСКИХ ЗАНЯТИЙ

                                Номер и наименование работы

                                Формируемы компетенции

                                Кол-во часов

                                Практическое занятие №9. Знакомство с интерфейсом MS Excel

                                Практическое занятие №10. Формулы и функции в

                                Практическое занятие №11. Условная функции в MS

                                ОК 1, ОК 5, ОК 9, ПК 2.2.

                                Практическое занятие №12. Автоматизация расчетов в MS Excel

                                ОК 2, ОК 6, ПК 2.2.

                                Практическое занятие №13. Построение диаграмм и графиков в MS Excel

                                ПК 1.1, ПК 2.2, ПК 1.3.

                                Практическое занятие №14. Решение систем уравнений

                                Практическое занятие №15. Построение таблицы и графика функций в среде ЭТ Excel

                                ОК 5, ОК 9, ПК 2.3.

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

                                Практическое занятие №17. Специальные возможности Excel по обработке табличных данных: сортировка, фильтрация, консолидация

                                ОК 3, ОК 9, ПК 1.2, ПК 2.2,

                                Практическое занятие №18. Использование графических возможностей табличного процессора

                                ОК 2, ПК 1.3, ПК 2.1, ПК 2.2

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

                                ОК 2, ОК 8, ПК 1.3, ПК 2.3.

                                КРИТЕРИИ ОЦЕНКИ

                                — Оценка «5» выставляется: практическая работа выполнена в полном объеме, в соответствии с заданием, с соблюдением последовательности выполнения, работа выполнена аккуратно, написан отчет.

                                — Оценка «4» выставляется: практическая работа выполнена в полном объеме, в соответствии с заданием, с соблюдением последовательности выполнения, частично с помощью преподавателя, ответы даны кратко, работа выполнена аккуратно, написан отчет.

                                – Оценка «3» выставляется: практическая работа выполнена не в полном объеме, не соблюдается последовательность, частично с помощью преподавателя, ответы даны кратко, не написан отчет.

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

                                ПРАКТИЧЕСКОЕ ЗАНЯТИЕ №9

                                ЗНАКОМСТВО С ИНТЕРФЕЙСОМ MICROSOFT EXCEL

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

                                Теоретические сведения:

                                1. Запуск Excel I вариант:

                                • щелкните на кнопке Пуск;

                                • выберите пункт меню Программы→ Microsoft Office;

                                • выберите Microsoft Excel и щелкнуть на нём.

                                

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

                                2. Окно Excel

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

                                Каждая рабочая книга в Excel состоит из рабочих листов. Лист состоит из ячеек, образующих строки и столбцы. На одном листе может содержаться 1 048 576 строк и 16 384 столбцов.

                                3. Ввод данных

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

                                • формулы; Рисунок 1 – Структура рабочей книги

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

                                Чтобы ввести текст, выполните следующие действия:

                                • Щелкните на ячейке, в которую вы собираетесьвводить текст.

                                • Наберите его. Текст появляется в ячейке и строке формул.

                                • Если вы сделали ошибку, щелкните на строке формул и отредактируйте текст.

                                Ввод числовых данных

                                Числовые значения могут содержать только цифры от 0 до 9 и специальные символы + – E e( ) . , $ % /

                                Чтобы ввести числовые данные, сделайте следующее:

                                • Активизируйте необходимую ячейку.

                                • Введите данные. Используя числовой формат, для введения отрицательного числа поставьте перед ним минус или заключите его в скобки. При введении дробей пользуйтесь либо десятичной дробью (0. ), либо натуральной (например, 1/2).

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

                                (insertionpoint).

                                Введение даты и времени

                                Подобные данные вводятся в самых различных форматах (чтобы увидеть форматы в которых может выводиться дата и время выберите вкладку Главная и вызовите в группе Число диалоговое окно Формат ячеек, на вкладке Число в области Числовые форматы выберите Дата или Время). Когда вы вводите дату в одном из форматов, она преобразуется в целое число, соответствующее этой дате, отсчитанной от 1 января 1900 года. Хотя вы не видите этих целых чисел, они используются Excel для выполнения дальнейших вычислений, производимых с датами.

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

                                • Щёлкните на интересующей вас ячейке.

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

                                • Нажмите Enter. Дата или время выровняются по правому краю. Маркер заполнения

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

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

                                4. Редактирование данных

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

                                Чтобы отредактировать данные сделайте следующее:

                                • Выделите необходимую ячейку.

                                • Щёлкните в строке формул или нажмите клавишу F2 или дважды щёлкните в этой же ячейке. Таким образом, ячейка переходит в режим редактирования (в строке состояния появляется надпись Правка).

                                • Нажмите клавишу Enter или щёлкните на соответствующей кнопке, расположенной в строке формул для введения в ячейку набранных данных.

                                • Если вы передумали и не хотите вводить или сохранять исправления, щёлкните на кнопке Отмена в строке формул или нажмите клавишу

                                Выделение ячейки

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

                                Для выделения одной ячейки просто щёлкните на ней.

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

                                Копирование данных

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

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

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

                                • Щёлкните на кнопке Копировать, расположенной в группе Буфер обмена на вкладке Главная. При этом содержимое ячейки (ячеек) скопируется в буфер обмена.

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

                                • Щёлкните на кнопке Вставить, расположенной в группе Буфер обмена на вкладке Главная. Данные появятся в выбранной вами ячейке.

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

                                Перетаскивание данных

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

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

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

                                Перемещение данных выполняется следующим образом:

                                • Выделите интересующие вас ячейки.

                                • Щёлкните на кнопке Вырезать.

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

                                • Щёлкните на кнопке Вставить.

                                5. Завершение работы.

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

                                

                                • либо щёлкнуть на кнопке в верхнем правом углу окна программы;

                                • либо выбрать Файл→ Выход.

                                • либо нажать сочетание клавиш Alt+F4

                                1. В папке Мои документы создайте папку со своим именем (если она еще не создана).

                                2. Запустите Microsoft Excel.

                                3. Выполните задания приведенные ниже.

                                1. В ячейку А1 введите число 5, в ячейке А2 число 2.

                                2. Выделите ячейки А1 и А2, потяните Маркер заполнения вниз до ячейки А10. Запишите в тетрадь что получилось.

                                3. В ячейке В2 наберите слово «январь», потяните маркер заполнения вправо до ячейки К2. Запишите в тетрадь что получилось.

                                4. Создайте собственный список, например список состоящий из фамилий учащихся вашего класса (создание собственных списков: Excel- Файл→Параметры→Основные→Создавать списки для сортировки и заполнения→ е→ Изменить списки→Новый список).

                                5. В любой ячейке наберите любую фамилию из вашего списка и потянитеза маркер заполнения. Запишите в тетрадь что получилось? Задание 2.

                                1. Введите в ячейку В1 число 5 и в ячейку С1 число -2.

                                2. Выделите ячейку D1 и нажмите на знак = в Строке формул.

                                3. Теперь наберите в ней следующую формулу: =(4*B1-C1)/2 (НЕ ВВОДИТЕ АДРЕСА ЯЧЕЕК В ФОРМУЛУ ВРУЧНУЮ. Для того

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

                                4. Перемести курсор на любую другую ячейку. Запишите в тетрадь что содержаться в ячейке D1.

                                5. Выделите ячейку В1 и наберите другое число, например 3. Перемести курсор на любую другую ячейку. Запишите в тетрадь что произошло с содержимым ячейки D1.

                                6. Чтобы просмотреть формулу, которая находится в ячейке, необходимо выделить ту ячейке в которую вводилась формула. Формула появится в Строке формул. Выделите ячейку с числом 7. Запишите в тетрадь что отображается в Строке формул.

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

                                

                                • выделить ячейку, в которой вычисляется сумма, щелкнуть мышью по кнопке Автосуммана Главная→Редактирование:

                                • выделить нужный интервал ячеек, содержащих числа (это и будет областью суммирования);

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

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

                                • нажать мышью на пиктограмму суммирования;

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

                                Если в области суммирования в одной из ячеек окажется текст, или ячейка окажется пустой, то Excel пропустит эти ячейки, а просуммирует только числа.

                                Выполните задание.

                                1. Наберите в ячейках столбца Е числа 24, 15, 55, 34, 98.

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

                                3. Сумма чисел этих ячеек должна появится в выделенной ячейке. Задание 4.

                                На Листе 2 создайте таблицу «Расходы предприятия за квартал» согласно алгоритму.

                                

                                Алгоритм выполнения задания

                                Ввод названия таблицы

                                

                                1. В ячейку А1 введите название таблицы. Выделите диапазон А1:F1 и на пиктограмме (объединить и поместить в центре) на Главная→Выравнивание.

                                Ввод и оформление шапки

                                2. Выделите диапазон А2:F2, выберите Главная→ Выравнивание→ Диалоговое окно «Формат ячеек» (Excel 2003 команду Формат→Ячейки→ вкладка Выравнивание) и установите флажок переносить по словам для размещения текста в несколько строк. Выберите варианты вертикального и горизонтального выравнивания по центру. Завершите работу в окне кнопкою ОК.

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

                                Ввод и оформление боковика

                                4. Выделите диапазон А3:А7, выберите Главная→ Выравнивание→ Диалоговое окно «Формат ячеек» (Excel 2003 команду Формат→Ячейки→ вкладка Выравнивание) и установите флажок переносить по словам для размещения текста в несколько строк. Выберите варианты вертикального выравнивания – по центру, горизонтального – по левому краю. Завершите работу в окне кнопкой ОК

                                5. Введите в каждую ячейку текст соответствующего названия строки Ввод данных

                                6. Выделите диапазон В3:F7, выберите Главная→Число→Диалоговое окно «Формат ячеек» и установите для выделенных ячеек денежный формат с двумя десятичными знаками, обозначение денежной единицы – р.

                                7. Введите числа (дробную часть числа, пробелы и наименование денежной единицы вводить не нужно!).

                                8. Выполните повторное сохранение. Завершение оформления таблицы

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

                                Вычисления в таблице

                                1. В ячейку Е3 введите формулу для вычисления среднемесячной суммы расходов на гос.налоги.

                                

                                2. Выполните групповое заполнение ячеек Е3:Е6 формулой, для этого навести указатель мыши в нижний правый угол ячейки. Курсор примет вид черного крестика и протянуть его до последней ячейки диапазона.

                                3. В ячейку F3 введите формулу для вычисления суммы расходов на государственные налоги за квартал.

                                

                                4. Выполните групповое заполнение формулой ячеек F3: F6

                                5. В ячейках B7:D7, F7 получите суммарные расходы по месяцам и за квартал.

                                6. Переименуйте Лист 2 в Расходы предприятия за квартал (щелчок правой кнопкой мыши на листе Лист2, выбрать пункт Переименовать).

                                7. Выполните повторное сохранение рабочей книги.

                                8. Перейдите на Лист 3, назовите его Крупнейшие озера мира (аналогично п.6)

                                9. Создайте таблицу «Крупнейшие озера мира»

                                

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

                                Чтобы воспользоваться встроенными функцияминеобходимо выбрать

                                

                                Формулы → Библиотека функций→ кнопка и выбрать соответствующую функцию:

                                11. Для нахождения минимальной глубины нужно щелкнуть по ячейке

                                С10 и выбрать функцию МИН

                                12. Для нахождения максимальной площади нужно щелкнуть по ячейке В11 и выбрать функцию МАКС

                                13. Для нахождения средней высоты над уровнем моря нужно щелкнуть по ячейке D12 и выбрать функцию СРЗНАЧ.

                                14. Сохраните таблицу в своей папке с именем Озера.xls.

                                15. Завершите работу с Microsoft Excel.

                                ПРАКТИЧЕСКОЕ ЗАНЯТИЕ №10

                                ФОРМУЛЫ И ФУНКЦИИ В MICROSOFT EXCEL

                                Цель: Освоение навыков по созданию и редактированию формул с использованием мастера функций и кнопки «Сумма». Использование абсолютной и относительной адресации ячеек в формулах при перемещении и копировании формул. Использование ссылок, имен ячеек и диапазонов в формулах. Знакомство с особенностями использования некоторых стандартных функций Excel: функций преобразования и округления чисел, статистических функций для расчета средних значений, поиска наибольших и наименьших значений, расчета количества ячеек, выборочного суммирования.

                                Теоретические сведения.

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

                                

                                Рисунок 1 – Вкладка Формулы

                                Порядок работы:

                                1. Выделите ячейку, в которую требуется ввести формулу.

                                2. Щелкните по кнопке нужной категории функций в группе Библиотека функций и выберите нужную функцию.

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

                                4. В окне Аргументы функции нажмите кнопку ОК.

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

                                

                                Рисунок 2 — Мастер функций

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

                                1. В ячейку или в строку формул введите знак » MsoNormal» style=»margin: 0 40.8pt 0.25pt 0″> 2. Выберите нужную функцию, для чего дважды щелкните по ней мышью.

                                3. С использованием клавиатуры и мыши введите аргументы функции. Подтвердите ввод формулы.

                                Для быстрого выполнения некоторых действий с применением функций без запуска мастера функций можно использовать кнопку Сумма. Эта кнопка, помимо группы Библиотека функций вкладки Формулы(там она называется Автосумма), имеется также в группе Редактирование вкладки Главная. Кроме вычисления суммы чисел в ячейках, кнопку Сумма можно использовать при вычислении среднего значения, определения количества числовых значений, нахождения максимального и минимального значений.

                                

                                Рисунок 3 — Ввод формулы с использованием автозавершения

                                В этом случае необходимо щелкнуть по стрелке кнопки и выбрать необходимое действие:

                                Среднее – расчет среднего арифметического;

                                Число – определение количества численных значений;

                                Максимум – нахождение максимального значения; Минимум – нахождение минимального значения.

                                Перемещать и копировать ячейки с формулами можно точно так же, как и ячейки с текстовыми или числовыми значениями. Кроме того, при копировании ячеек с формулами можно пользоваться возможностями специальной вставки. Это позволяет копировать только формулу без копирования формата ячейки. При перемещении ячейки с формулой содержащиеся в формуле ссылки не изменяются. При копировании формулы ссылки на ячейки могут изменяться в зависимости от их типа (относительные или абсолютные). По умолчанию ссылки на ячейки в формулах относительные, то есть адрес ячейки определяется на основе расположения этой ячейки относительно ячейки с формулой. При копировании ячейки с формулой относительная ссылка автоматически изменяется. Именно возможность использования относительных ссылок и позволяет копировать формулы. В некоторых случаях использование относительных ссылок недопустимо. Для того чтобы ссылка на ячейку при копировании не изменялась, необходимо использовать абсолютные ссылки. Абсолютная ссылка ячейки имеет формат $A$1, где $ – служебный символ, показывающий абсолютную ссылку.

                                Чтобы ссылка на ячейку была абсолютной, после указания ссылки на ячейку следует нажать клавишу F4. Ссылку можно преобразовать из относительной в абсолютную и при редактировании ячейки с формулой. К заголовкам столбца и строки в адресе ячейки следует добавить служебный символ $. Например, для того чтобы ссылка на ячейку А20 стала абсолютной, необходимо ввести $А$20. Ссылка может быть не только относительной или абсолютной, но и смешанной. Ссылка формата A$1 является относительной по столбцу и абсолютной по строке, т.е. при копировании ячейки с формулой выше или ниже, ссылка изменяться не будет. А при копировании влево или вправо будет изменяться заголовок столбца. Ссылка формата $A1 является относительной по строке и абсолютной по столбцу, т.е. при копировании ячейки с формулой влево или вправо выше или ниже ссылка изменяться не будет. А при копировании выше или ниже будет изменяться заголовок строки. Абсолютную адресацию в формулах обеспечивает также применение имен ячеек и их диапазонов. Например, формула =Март+Апрель+Май предполагает сложение данных, содержащихся в ячейках с соответствующими именами (вне зависимости от того, в каком месте книги находится именованные диапазоны и формула, которая на них ссылается). Имя – слово или строка знаков, представляющих ячейку, диапазон ячеек, формулу или константу. Имена можно использовать в любом листе книги.

                                Присваивание имени ячейке или диапазону ячеек:

                                1. Выделите ячейку или диапазон ячеек.

                                2. В группе Определенные имена вкладки Формулы нажмите кнопку Присвоить имя.

                                3. В окне Создание именив поле Имявведите имя ячейки или диапа-

                                4. Для задания области действия имени в поле со списком Область выберите Книга или имя листа в книге.

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

                                

                                Рисунок 4 — Присваивание имени ячейке

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

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

                                • первый знак в имени должен быть буквой или знаком подчеркивания;

                                • пробелы не допускаются;

                                • не допускаются имена, которые имеют такой же вид, как и ссылки на ячейки, например Z$100 или R1C1;

                                • в имени может быть больше одного слова. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки, например: Год_2010 или Год.2010;

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

                                Имя можно присвоить формуле или постоянному значению (константе). Например, имя «Скидка», которому присвоено значение 33 процента, можно использовать в любом месте для вычисления цены со скидкой. Для

                                присвоения имени формуле или константе в поле Диапазон окна Создание имени следует ввести знак равенства (=) и формулу или константу (рис. 5.).

                                

                                Рисунок 5 — Присвоение имени константе

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

                                

                                Рисунок 6 — Вставка имени в формулу

                                Если нужное имя не отображается в списке, выберите команду Вставить имена (см. рис. 6.), а затем в окне Вставка имени выберите вставляемое имя. Ненужное или ошибочное имя можно удалить:

                                1. В группе Определенные имена вкладки Формулы нажмите кнопку Диспетчер имен.

                                2. В окне Диспетчер имен выделите имя и нажните кнопку Удалить. Задание 1.

                                1. Создайте таблицу

                                

                                2. В ячейке Н4 рассчитайте сумму ячеек С4:G4.

                                3. В ячейке I4 рассчитайте произведение ячеек В4 и H4. Присвойте формуле имя «Доход».

                                4. В ячейке C10 с использованием кнопки (Сумма) рассчитайте сумму ячеек C4:C9.

                                5. В ячейке B10 с использованием кнопки (Сумма) рассчитайте среднее значение ячеек B4:B9.

                                6. Скопируйте формулу ячейки H4 на ячейки H5:H9.

                                7. Скопируйте формулу ячейки I4 на ячейки I5:I9.

                                8. Скопируйте формулу ячейки C10 на ячейки D10:I10.

                                9. Отредактируйте формулу в ячейке B10: среднее значение должно быть рассчитано для ячеек B4:B7.

                                10. В ячейке А11 введите текст «курсдоллара», а в ячейке А12 укажите значение курса доллара по отношению к рублю на текущую дату.

                                11. Присвойте ячейке А12 имя «Курс_доллара».

                                12. В ячейке J4 рассчитайте частное от деления ячейки I4 на ячейку A12 так, чтобы эту формулу можно было копировать на ячейки J5:J10.

                                13. Удалите из книги имя ячейки Доллар.

                                14. Сохраните файл под именем «Продажа_компьютеров.xlsx».

                                Все функции на вкладке «Формулы» распределяются по темам на следующие группы:

                                «Математические». Предназначены для решения алгебраических задач: функции для округления данных, тригонометрические и т. д.

                                «Логические». Применяются для решения задач с условиями.

                                «Финансовые». Применяются для выполнения финансовых расчетов. «Текстовые». Предназначены для работы с текстовыми значениями «Даты и времени». Применяются для работы с данными в формате

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

                                Приведем обзор некоторых используемых в практикуме функций Округление чисел особенно часто требуется при денежных расчетах. Например, цену товара в рублях, как правило, нельзя устанавливать с точностью более двух знаков после запятой. Если же в результате вычислений получается большее число десятичных разрядов, требуется округление. В противном случае накапливание тысячных и десятитысячных долей рубля приведет в итоге к ошибкам в вычислениях. Для округления чисел можно использовать целую группу функций. Наиболее часто используют функции ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ. Синтаксис функции ОКРУГЛ(А;В), где A – округляемое число; В – число знаков после запятой (десятичных разрядов), до которого округляется число. Синтаксис функций ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ точно такой же, что и у функции ОКРУГЛ. Функция ОКРУГЛ при округлении отбрасывает цифры меньшие

                                5, а цифры большие 5 округляет до следующего разряда. Функция ОКРУГЛВВЕРХ при округлении любые цифры округляет до следующего разряда. Функция ОКРУГЛВНИЗ при округлении отбрасывает любые цифры. Эти функции можно использовать и для округления целых разрядов чисел. Для этого необходимо использовать отрицательные значения аргумента В. Для округления чисел в меньшую сторону можно использовать также функцию ОТБР.

                                Синтаксис функции ОТБР(А;В), где A – округляемое число; В – число знаков после запятой (десятичных разрядов), до которого округляется число. Фактически функция ОТБР отбрасывает лишние знаки, оставляя только количество знаков, указанное в аргументе В. Для округления числа до меньшего целого можно использовать функцию ЦЕЛОЕ(А), где A – округляемое число. Наконец, для округления до ближайшего четного или нечетного числа можно использовать функции ЧЕТН(А) и НЕЧЕТН(А), а для ближайшего кратного большего или меньшего числа – функции ОКРВЕРХ и ОКРВНИЗ. Для преобразования результатов вычислений в положительное число можно использовать функцию ABS(А), где А – число, для которого определяется абсолютное значение.

                                Простое суммирование содержимого заданного интервала ячеек осуществляется функцией СУММ(А), где A – список от 1 до 30 элементов, которые требуется суммировать. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Фактически данная функция заменяет непосредственное суммирование с использованием оператора сло-

                                жения (+). Формула =СУММ(В2:В7), тождественна формуле

                                =В2+В3+В4+В5+В6+В7. Однако есть и некоторые отличия. При использовании функции СУММ добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.

                                Для умножения используют функцию ПРОИЗВЕД(А), где A – список от 1 до 30 элементов, которые требуется перемножить. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Фактически данная функция заменяет непосредственное умножение с использованием оператора умножения (*). Так же как и при использовании функции СУММ, при использовании функции ПРОИЗВЕД добавление ячеек в диапазон перемножения автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон перемножения. Аналогично формула будет изменяться и при уменьшении диапазона. Задание 2.

                                1. Создать новую книгу. Сохранить ее на диске в своей папке с именем «Ассортимент».

                                2. На первом листе книги набрать данные таблицы 1, приведенной ниже

                                

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

                                3. На втором листе текущей рабочей книги создать таблицу 2:

                                

                                4. Листам дать название «Ксероксы» и «Факсы» соответственно.

                                5. На листах «Ксероксы» и «Факсы» в столбиках «Цена», используя абсолютную ссылку, создать формулу для вычисления Цены, при условии, что она на 30% больше стоимости.

                                6. На листах «Ксероксы» и «Факсы» в столбцах «Сумма» создать формулу =количество*цена;

                                7. На листах «Ксероксы» и «Факсы» отформатировать таблицы:

                                • шрифт – Times New Roman; размер 13; задать границы – тонкая пунктирная линия;

                                • в столбцах «Товар», «Модель», «Название» установить выравнивание по левому краю;

                                • в столбцах «Стоимость», «Цена», «Количество», «Сумма» установить выравнивание по центру;

                                • в ячейках, содержащих денежные суммы, установить денежный формат.

                                8. Над таблицами добавить строку, в столбце А1 набрать «Курс $», в ячейке В1 набрать текущий курс доллара и установить денежный формат. При-

                                своить этой ячейке имя «Курс_$».

                                9. к таблицам добавить столбец с названиями «Стоимость в у.е.», где создать формулу с абсолютной ссылкой =D3/Курс_$.

                                10. В Столбце «Стоимость в у.е.» установить форматирование как в предыдущих столбцах, воспользовавшись кнопкой «Формат по образцу».

                                11. Над таблицами добавить три строки в первой строке объединить столбцы, которые занимает ваша таблица, и набрать в объединенной ячейке «Ассортимент ксероксов фирмы «Флагман», «Ассортимент факсов фирмы «Флагман».

                                12. Используя «Буфер обмена», скопировать обе таблицы на третий лист текущей книги и назвать его «Полный ассортимент».

                                13. На листе «Полный ассортимент», используя Условное форматирование, выделить те суммы, величина которых от 6000 руб. до 450$.

                                14. В ячейке E2 округлите указанную там цену товара до ближайшего целого. Скопируйте формулу на ячейки Е3:Е22.

                                15. В основной таблице под столбиками «Цена», «Количество», «Сумма» найти: минимальный элемент, максимальный элемент и среднее значение.

                                16. Вычислите итоговое значение столбцов Количество и Сумма.

                                17. Сохраните файл под именем «Ксероксы и факсы».

                                ПРАКТИЧЕСКОЕ ЗАНЯТИЕ №11

                                УСЛОВНАЯ ФУНКЦИЯ В EXCEL

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

                                Теоретические сведения.

                                Условие — это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ.

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

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

                                если условие истинно, то значение данной ячейки определит , в противном случае — .

                                Логические выражения.

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

                                >= Больше или равно

                                Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

                                Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае — 20:

                                =ЕСЛИ(А1>3;10;20)

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

                                =ЕСЛИ(А1>=4;»Зачет сдал»;»Зачет не сдал»)

                                Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0 Например:

                                =ЕСЛИ(СУММ(А1:А3)=30;А10;»»)

                                Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:

                                =ЕСЛИ(А1=»Динамо»;10;290)

                                Эта формула возвращает значение 10, если ячейка А1 содержит строку «Динамо», и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

                                Функции И (AND), ИЛИ (OR), НЕ (NOT) позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции Ии ИЛИ могут иметь до30 логических аргументов и имеют синтаксис:

                                Функция НЕ имеет только один аргумент и следующий синтаксис: =НЕ(логическое_значение)

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

                                Приведем пример. Пусть Excel возвращает текст «Прошел», если ученик имеет средний балл более 4(ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

                                =ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")

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

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

                                Вложенные функции ЕСЛИ

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

                                (И(А1>=60;А1<80);"Иногда";"Никогда")))

                                Если значение в ячейке А1 является целым числом, формула читается следующим образом: «Если значение в ячейке А1 равно 100, возвратить строку «Всегда». В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить «Обычно». В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку «Иногда». И, если ни одно из этих условий не выполняется, возвратить строку «Никогда». Всего допускается до 7 уровней вложения функций ЕСЛИ. Практика.

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

                                =ЕСЛИ(E2

                                

                                Составьте таблицу покупок: № товара, наименование товара, цена, количество, стоимость. 5%-аяскидка на покупку предоставляется в том случае, если стоимость покупки превысит 5000 руб., 10%-ая,если стоимость покупки превысят 20000 руб. Напишите список не менее чем из 10 наименований товаров и с использованием функции «ЕСЛИ» рассчитайте итоговую сумму.

                                

                                Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг10, то – в 1,5 раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена до уценки, цена после уценки.

                                

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

                                ячейке (С-) — сколько месяцев хранится товар, а в соответствующей ячейке (В-) – цена до уценки.

                                

                                Составить таблицу для вычисления количества лифтов в доме в зависимости

                                от количества этажей.

                                • Количество этажей — случайное целое число в интервале от 1 до 30

                                • Менее 6 этажей – лифта нет; •

                                • От 6 до 10 этажей – 1 лифт; •

                                • От 11 до 18 этажей – 2 лифта; •

                                • От 19 до 30 этажей – 4 лифта. • Задание 5.

                                Составьте таблицу, по образцу. В качестве фамилий и имён внесите данные своей группы. В ячейке «Средний балл» используйте функцию «СРЗНАЧ». В ячейке «Статус ученика» составьте формулу, которая выдаёт следующие значения:

                                если средний балл = 5, то «Отличник»; • если средний балл от 4 до 5, то «Хорошист»; • в противном случае — «Троечник». •

                                

                                ПРАКТИЧЕСКОЕ ЗАНЯТИЕ №12

                                АВТОМАТИЗАЦИЯ РАСЧЕТОВ ВMSECXEL

                                Цель: Освоение навыков по созданию и редактированию формул с использованием мастера функций и кнопки «Сумма». Использование абсолютной и относительной адресации ячеек в формулах при перемещении и копировании формул. Использование ссылок, имен ячеек и диапазонов в формулах. Знакомство с особенностями использования некоторых стандартных функций Excel: функций преобразования и округления чисел, статистических функций для расчета средних значений, поиска наибольших и наименьших значений, расчета количества ячеек, выборочного суммирования. Закрепление материала предыдущих лабораторных работ.

                                Работа с функциями Год и Сегодня

                                Ячейки, в которых выполнена заливка серым цветом, должны содержать формулы!

                                1. Создать и отформатировать таблицу по образцу (Фамилии ввести из списка с помощью авто заполнения)

                                2. Вычислить стаж работы сотрудников фирмы по формуле:

                                =ГОД(СЕГОДНЯ()-Дата приема на работу)-1900

                                1. Переименовать Лист1 в Сведения о стаже сотрудников Сведения о стаже сотрудников фирмы «Рога и копыта»

                                Дата приема на работу

                                Читать:

                                1971 год когда музыка все изменила