Нестандартное условное форматирование

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

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

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

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

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

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

1. Цвет шрифта в [ ]. Можно использовать следующие цвета: Черный, Синий, Голубой, Зеленый, Красный, Белый, Желтый. Если годится стандартный цвет, то этот параметр можно не указывать.

2. Числовой формат (см. подразд. 1.14 и 1.16). Если годится стандартный формат, то указывают слово Основной.

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

Ниже приводятся примеры текстовых строк, которые надо ввести в параметр "Тип" команды Формат ® Ячейки…(вкладка Число) в режиме "Все форматы", для того, чтобы обеспечить заказанные эффекты:

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

"Приход: "000,0" р";[Красный]"Расход:000,0"р";
[Синий]"Ничего";[Зеленый]"Ошибка: "

· Варианты данных те же, что и в предыдущем примере, но следует использовать для них только цветовое выделение:

[Красный]Основной;[Синий]-Основной;
[Зеленый]Основной;[Голубой]Основной

· Надо использовать для разных данных только цветовое выделение и у отрицательных чисел минус не выводить:

[Красный]Основной;[Синий]Основной;
[Зеленый]Основной;[Голубой]Основной

· Обеспечить при вводе числовой информации автоматическое добавление на экране подписи "шт.", а любую текстовую информацию, введенную по ошибке вместо числовой, выделять красным цветом:

· Следует составить формат, который к числам меньше тысячи добавляет подпись шт., числа больше 1000 делит на 1000 и добавляет подпись тыс. шт., а текстовую информацию выделяет красным цветом:

[<1000]0" шт";[>=1000];# ###,00 " тыс. шт.";
Основной;[Красный]Основной

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

Замените эти данные новыми. Посмотрите, как меняется их изображение на экране.

2.1. Расчет таблицы значений функции
от одного аргумента

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

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

Найти координаты корней функции y = 2cos (x + 2 )e^– ^0,5 ^x

Заполним Рабочий лист в соответствии с планом, предложенным в табл. 2.1.1.

Формулы, занесенные в ячейки А5:А25, будут нагляднее, если перед их набором присвоить константам в А3:С3 имена (см. подразд. 1.7) и вводить их с помощью щелчка по ячейке с нужной константой, а в формулу функции ввести вместо адреса аргумента подпись диапазона – текст из ячейки А4 (см. подразд. 1.8).

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

Найдите какой-нибудь корень и экстремум этой функции в отрицательной области аргументов.

Расчет таблицы значений функции от двух аргументов

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

Рассмотрим пример. Составим таблицу функции от двух переменных z = y ² x ln (x + y) для диапазонов 2 < x < 6 и1 < y < 5.

Будем менять х с шагом 0,4; у – с шагом 0,8. Заполним Рабочий лист в соответствии с планом, предложенным в табл. 2.2.1.

При вводе формулы в ячейку С4 символы "$" расставлены с таким расчетом, что при протяжке вбок разрешено менять адрес только у аргумента y, аргумент x для всех полученных копий берется из одного и того же столбца (в адресе x закреплен столбец). При протяжке вниз – наоборот: в адресе аргумента y закреплена строка, поэтому он не меняется, и в каждом столбце оказываются значения функции, сосчитанные для разных x, но одного и того же y, находящегося в том же столбце. Символ "$" можно вводить непосредственной набивкой в английском регистре или повторными нажатиями клавиши <F4> (см. подразд. 1.11).

Если перед вводом формулы в С4 присвоить диапазонам С3:Н3 и В4:В14 имена соответственно "у" и "х", то формулы в С4 можно ввести в более понятном виде: =у^2*x-LN(x+y) (см. подразд. 1.8, 1.11).

Отформатируйте полученную таблицу (см. подразд. 1.13):

· объедините ячейки А4:А14, измените в них направление текста на вертикальное;

· выделите цветом шрифта или заливкой заголовки таблицы (С2:Н3 и А4:В14);

· обведите толстой рамкой всю таблицу и отделите такой же рамкой заголовки с аргументами от значений функции;

· разделите тонкими линиями столбцы таблицы;

· разделите пунктирными линиями строки таблицы.

3. ЗАДАЧИ И УПРАЖНЕНИЯ НА ОСНОВНЫЕ
КОМАНДЫ И ПРОСТЕЙШИЕ АЛГОРИТМЫ

3.1.1. Выделите на экране столбцы от А до L. Установите масштаб окна Excel так, чтобы на экране помещались эти столбцы (команда Вид ® Масштаб ® По выделению).

3.1.2. Введите на Лист1 информацию, указанную на рис. 3.1.1. Установите в ячейке В2 следующий формат: размер шрифта – 26 пт., цвет шрифта – красный, цвет заливки – желтый, выравнивание по центру, разрешен перенос по словам в пределах ячейки (команда Формат ® Ячейки…, вкладки Шрифт, Выравнивание). Скопируйте этот формат (но не содержание ячейки) на блок С3:Е4 (команды Правка ® Копировать, Правка ® Специальная вставка). Разместите в ячейках D4 и Е4 пятые степени чисел из D3 и Е3 соответственно.

3.1.3. Присвойте ячейкам А5 и F3 (см. рис. 3.1.1) имена соответственно Делимое и Делитель (команда Вставка ® Имя… ® Присвоить…). Введите в ячейку В5 формулу: =Делимое/Делитель. Укажите ячейки, влияющие на значение в В5 (команда Сервис ® Зависимости… ® Влияющие ячейки). Введите делитель, при котором в В5 результатом будет 50.

3.1.4. Скопируйте блок А2:Е5 (см. рис. 3.1.1) на Лист2, начиная с ячейки Y12345 (для быстрого перехода в удаленную ячейку следует после команды Правка ® Копировать ввести в адресное поле адрес нужной ячейки и нажать <Enter>).

3.1.5. Введите информацию с рис. 3.1.2. на Лист1. Отформатируйте таблицу: А9:D9 – жирный шрифт, выравнивание по центру; В10:В16 и D10:D16 – денежный формат; В10:D16 – выравнивание по центру; D10:D16 – красный шрифт, разлиновка – как на рис. 3.1.2 (команда Формат ® Ячейки…, вкладки Шрифт, Выравнивание, Число, Границы). Присвойте имена ячейкам F10 и G10 (команда Вставка ® Имя… ® Создать…) и блокам В10:В15 и С10:С15 (команда Вставка ® Имя… ® Присвоить…). Заполните столбец Выплата по формуле:

="Премия" * "Коэффициент премии" –
"Ошибки" * "Штраф за ошибку"

Введите формулы итогов в ячейки В16 и D16 (кнопка Автосумма или команда Вставка ® Функция…).

3.1.6. Присвойте листам, на которых выполнялись задания 3.1.1–3.1.5, названия Формат и Копия, свободному листу Вашей рабочей книги – имя Сложение.

3.1.7. Введите на лист Сложение данные, указанные на рис. 3.1.3. Разместите в В10 их сумму, используя только заполненные числами ячейки (кнопка Автосумма или команда Вставка ® Функция…, выделение группы блоков через <Ctrl + буксировка).

3.1.8. Разлинуйте таблицу в соответствии с рис. 3.1.4 (команда Формат ® Ячейки…, вкладка Границы). Заполните таблицу сложения с масштабом в соответствии с формулой: ((Х+ Y) *Масштаб) (пользуйтесь частичным и полным закреплением адресов). Масштабный коэффициент – в ячейке В19. Сосчитайтесумму диагональных элементов таблицы.

3.1.9. Повторите задание 3.1.8, присвоив имена Х и Y блокам аргументов (через адресное поле или командой Вставка ® Имя…) и используя их в формулах вместо частичного закрепления адресов. Новую таблицу расположите, начиная с ячейки А21. Сравните вид формул и результаты в обоих заданиях.

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

3.1.11. Присвойте чистому листу имя Заполнение. Введите в ячейки А1 и В1 число 2, в ячейки А2 и В2 – число 2,5. Заполните блок А1:А10 по арифметической, а блок В1:В10 по геометрической прогрессии (следует выделить нужный блок и далее воспользоваться командой Правка ® Заполнить ® Прогрессия…). Выделите значения, которые больше пяти, голубой заливкой (команда Формат ® Условное форматирование…).

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

3.1.13. Первоначальный запас рыбы в озере 1200 тонн. Естественный ежегодный прирост составляет 15 %. Ежегодный план отлова – 300 т. Порог, после которого запас рыбы невосстановим, составляет 400 т. Рассчитайте запас рыбы в озере на ближайшие 15 лет. С помощью условного форматирования выделите красной заливкой годы, в которые запас рыбы окажется меньше критического порога. Для лет, в которые запас рыбы полностью иссякнет (отрицательные числа), дополнительно закажите красный шрифт. Составьте вспомогательную таблицу, отражающую естественное восстановление рыбного запаса при условии запрета на отлов, начиная с года, предшествующего критическому. С помощью условного форматирования отметьте зеленой заливкой годы, в которые запас рыбы полностью восстановится.

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

3.1.15. Оформите ведомость по закупке товаров, включающую 8–10 наименований (рис. 3.1.6). Введите свои значения в графы 2 и 3, расчетные формулы – в графу 4 и строки "Итого" и "Разница". Добавьте с помощью форматирования указатели единиц измерения (шт., пачки, рубли и т. п.). Подберите количество каждого товара так, чтобы итоговая сумма не превышала заданной, но была максимально близка к ней.

3.1.16. Заказчик оценил всю работу в 10000 долларов и выдал аванс в размере 4000 долларов. Этот аванс был распределен между работниками произвольным образом (кто сколько попросил). Требуется по окончании работы распределить оставшиеся 6000 долларов с учетом ранее выданного аванса, коэффициента трудового участия (КТУ) и коэффициента профессионального класса.

Расположите и отформатируйте информацию в соответствии с рис. 3.1.7, введите недостающие формулы, смысл которых описан ниже:

"Заработано" = "Всего заработано" * ("КТУ работника" / "Итого КТУ") * "Коэффициент за класс"

"Корректировка" = "Заработано" * ("Всего заработано" / "Итого заработано")

Формулы в строке "Итого" и столбце "Выдать остаток" составьте сами.

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

Формулы для расчетов (S ₀ – начальная сумма вклада, a – годовой процент): через i месяцев сумма вклада составит при простом проценте S_i = S_i _–1+ (a / 12) S ₀, при сложном S_i = S_i _–1+ (a / 12) S_i _–1.

3.1.18. На рис. 3.1.9 представлены сведения, необходимые для начисления зарплаты сотрудникам фирмы. С помощью функции ВПР() перенесите в блок Е3:Е7 тарифы, соответствующие разряду работников, а также заполните графу "Начислено". Тарифы занесены в справочную таблицу (блок А2:В6).

Функция ВПР() находится в Мастере функций в категории Ссылки и массивы. Ее следует ввести в ячейку Е3 со следующими значения аргументов:

· Табл_массив (где искать строку, начинающуюся со значения из D3) – $А$3:$В$6.

· Номер_столбца (в каком столбце найденной строки искать нужное данное) – 2.

· Диапазон_просмотра – 0 (в блоке $А$3:$В$6 ищется строка, в которой первое значение точно совпадает с D3).

Далее формула копируется на остальные ячейки этой графы.

3.1.19. На рис. 3.1.10 изображен рабочий график сотрудников фирмы. Требуется найти в нем людей, которые работали в интересующие нас дни.

В этой задаче удобно использовать команду Формат ® Условное форматирование… для выделения интересующих нас дней. При задании образца для форматирования в параметрах этой команды следует использовать функцию ГПР().

1. Установите курсор на ячейку В4 и вызовите команду Формат ® Условное форматирование...

2. В первой строке окна команды три текстовых поля. Установите в них соответственно:

Формулу наберите вручную, так как Мастер функций в этом окне недоступен. Аргумент В4 не должен быть закреплен. Смысл аргументов функции ГПР():

· Таблица –$D$1:$G$1 (таблица, в которой ведется поиск столбца, начинающегося с того же значения, что и в В4).

· Номер_строки – 1 (из какой строки найденного столбца следует брать значение функции ГПР()).

· Диапазон_просмотра – 0 (определяется тип поиска: 0 – в первой строке ищется точное совпадение с В4, 1 – в первой строке ищется ближайшее значение, которое меньше значения в В4 или равно ему).

Формат ячеек, удовлетворяющих этому условию, задайте сами.

3. С помощью команд Правка ® Копировать и Правка ® Специальная вставка… скопируйте формат из В4 на всю область Графика работ.

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

ЧТО ПРОИСХОДИТ ВО ВЗРОСЛОЙ ЖИЗНИ? Если вы все еще «неправильно» связаны с матерью, вы избегаете отделения и независимого взрослого существования...

ЧТО ТАКОЕ УВЕРЕННОЕ ПОВЕДЕНИЕ В МЕЖЛИЧНОСТНЫХ ОТНОШЕНИЯХ? Исторически существует три основных модели различий, существующих между...

ЧТО ПРОИСХОДИТ, КОГДА МЫ ССОРИМСЯ Не понимая различий, существующих между мужчинами и женщинами, очень легко довести дело до ссоры...

Что делать, если нет взаимности? А теперь спустимся с небес на землю. Приземлились? Продолжаем разговор...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: