 |
Задачи для самостоятельного решения
|
|
|
|
Задача 1. В ячейке введена скорость 50 километров в час. Создайте пользовательский формат, чтобы число 50 выводилось как 50 км/час.
Задача 2. Создайте пользовательский формат, который выводит отрицательные числа красным цветом, а положительные и нуль – черным.
Задача 3. Создайте пользовательский формат, который дописывает к дате краткое название дня недели (3 буквы).
Задача 4. Введите в А1:А15 прогрессию 1,2,…,15. Используя условное форматирование, выделите красным курсивом на голубом фоне числа от 2 до 4, синим курсивом – числа от 6 до 8, зеленым курсивом – числа от 12 до 15.
Задача 5. Введите в В1:В15 прогрессию 1,2,…,15.Выделите полужирным красным курсивом четные числа, полужирным синим курсивом – нечетные числа.
Задача 6. Введите в С1:С15 прогрессию 1,2,…,15.Выделите полужирным красным курсивом числа, кратные трем, но не кратные двум.
Задача 7. Используйте условное форматирование для анализа вклада ежеквартальных вложений в общий годовой итог.
Если вложения, сделанные в течение квартала (ячейки B2:E3), составили 30 и более процентов от общего годового итога, то результаты отобразите полужирным синим шрифтом. Если же вложения, сделанные в течение квартала, составили 20 и менее процентов, то результаты отобразите полужирным красным шрифтом.
Задача 8.
a) выделите красным курсивом фамилию профессора с максимальным стажем;
b) выделите синим курсивом фамилию самого молодого доцента;
c) выделите зеленым курсивом фамилию и должность сотрудника с минимальным разрядом;
d) выделите красной заливкой ячейки с фамилией и датой рождения сотрудника, у которого в ближайшее время будет день рождения.
Задача 9. Откройте файл RASHOD.xls с сетевого диска COMMON. Используя условное форматирование:
|
|
|
1. Выделите красным курсивом суммы, размер которых больше среднего значения по полю Сумма;
2. Выделите синим курсивом получателей, кто получал деньги в понедельник или вторник
3. Выделите синим курсивом дату, когда была выдана максимальная сумма.
4. Используйте заливку для выделения дат, когда выдавалась только одна сумма.
Проверка вводимых данных
Проверка значений
Чтобы определить тип данных, разрешенный для ячейки или диапазона, выполните следующие действия:
1. Выделите ячейку или диапазон.
2. Выберите команду Проверка в меню Данные, а затем откройте вкладку Параметры.
3. Выберите значение из раскрывающегося списка Тип данных. Для того, чтобы определить формулу, необходимо выбрать значение Другой.
4. Выберите условие из списка Значения. Задайте необходимые данные в появившихся полях. Можете использовать константы, ссылки на ячейки и даже формулы.
5. Если пустые записи допускаются, то установите флажок Игнорировать пустые ячейки.
6. Если вносимые изменения нужно применить ко всем другим ячейкам, содержащим исходные условия проверки, то установите флажок Распространить изменения на другие ячейки с тем же условием.
7. Чтобы при выборе ячейки пользователем отображалось дополнительное сообщение для ввода, перейдите на вкладку Сообщение и установите флажок Отображать подсказку, если ячейка является текущей, после чего укажите заголовок и введите текст для сообщения. Эта возможность используется для того, чтобы подсказать пользователю, какие данные должны вводиться.
8. Перейдите на вкладку Сообщения об ошибке и установите флажок Выводить сообщение об ошибке. Определите сообщение, которое будет выводиться при вводе неверного значения. С помощью списка Вид можно определить, какие действия будут доступны пользователю при вводе неверных данных. Чтобы не допустить ввод неверного значения, выберите опцию Останов. Это действие выполнять необязательно.
|
|
|
Вкладка Параметры диалогового окна Проверка вводимых значений позволяет определить условия для следующих типов данных: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста, Другой.
Если в качестве условия вы выбрали Список и установили флажок Список допустимых значений, то пользователь должен будет выбирать значение из указанного списка. Диапазон, используемый для списка, определяется в поле Источник. Диапазон должен представлять собой строку или столбец и находиться на том же рабочем листе. Если список находится на другом рабочем листе, то можно определить для него имя, которое затем использовать в качестве источника. В этом случае перед именем нужно поставить знак равенства. Если список небольшой, то его можно ввести непосредственно в поле. Элементы списка разделяются символом, определенным в настройках региональных стандартов системы (для России это обычно точка с запятой, иногда – запятая).
Если в качестве условия вы выбрали Другой, то для проверки корректности данных может использоваться формула. Формула задается непосредственно в поле Формула, или же определяется как ссылка на ячейку, содержащую формулу. Формула должна иметь значение ИСТИНА для корректных данных и ЛОЖЬ _ для некорректных данных. При использовании относительных ссылок формулу нужно задавать для левой верхней ячейки диапазона так же, как в формулах условного форматирования.
После того как данные введены, можно найти значения, не удовлетворяющие условиям. Для этого используется панель инструментов Зависимости. Чтобы ее вывести на экран, в меню Сервис укажите на пункт Зависимости формул, а затем выберите команду Панель зависимостей. Если щелкнуть на кнопке Обвести неверные данные, ячейки, содержащие неверные данные будут обведены. Если неверные данные исправить, обводка будет снята. Можно удалить обводку с помощью кнопки Удалить обводку неверных данных той же панели, не исправляя данных.
Для обнаружения ячеек, имеющих условия на значения, можно воспользоваться диалоговым окном Переход (открывается командой Правка / Перейти или клавишей F5). Щелкните на кнопке Выделить и затем выберите переключатель проверка данных. Далее выберите переключатель всех или этих же и нажмите кнопку ОК.
|
|
|
Проверка формул
Ячейка, на которую ссылается формула из другой ячейки, называется влияющей. Ячейка, которая содержит формулу, ссылающуюся на другие ячейки, называется зависимой. Например, если ячейка D10 содержит формулу = В5, то ячейка D10 является зависимой от ячейки В5, а ячейка В5 является влияющей на ячейку D10
Чтобы выделить цветом влияющие аргументы в формуле, нужно выделить ячейку с формулой и нажать кнопку F2.
Используя панель инструментов Зависимости или пункты меню Сервис / Зависимости формул можно отобразить и влияющие ячейки, и зависимые ячейки, и формулы. Предварительно в меню Сервис выберите пункт Параметры и откройте вкладку Вид. Убедитесь, что в меню Объекты выбрано отображать или только очертания.
Стрелки слежения показывают связь между активной ячейкой и связанными с ней ячейками. Синие стрелки слежения показывают на ячейку, значение которой используется другой ячейкой, а красные – на ячейки со значениями ошибок.
Чтобы выявить влияющие ячейки выполните следующее:
1. Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.
2. Чтобы отобразить стрелку слежения от каждой влияющей ячейки, нажмите кнопку Влияющие ячейки на панели инструментов Зависимости.
3. Чтобы продолжить поиск следующего уровня влияющих ячеек, снова нажмите кнопку Влияющие ячейки.
4. Чтобы удалить первый уровень стрелок слежения, начиная с самой дальней влияющей ячейки, нажмите кнопку Убрать стрелки к влияющим ячейкам. Чтобы удалить следующий уровень, снова нажмите эту кнопку.
Чтобы выявить зависимые ячейки:
1. Укажите ячейку, для которой следует найти зависимые ячейки.
2. Чтобы отобразить стрелку зависимостей к каждой зависимой ячейке, нажмите кнопку Зависимые ячейки на панели инструментов Зависимости.
3. Чтобы продолжить поиск следующего уровня зависимых ячеек, снова нажмите кнопку Зависимые ячейки.
4. Чтобы удалить первый уровень стрелок слежения, начиная с самой дальней зависимой ячейки, нажмите кнопку Убрать стрелки к зависимым ячейкам. Чтобы удалить следующий уровень, снова нажмите эту кнопку.
|
|
|
Чтобы удалить все стрелки слежения на рабочем листе, нажмите кнопку Убрать все стрелки на панели инструментов Зависимости.
Очень полезной для проверки формул является команда Вычислить формулу. Она есть на панели инструментов Зависимости и в меню Сервис / Зависимости формул. С помощью этой команды можно просмотреть значения различных частей сложной формулы, вычисляемые шаг за шагом в соответствии с порядком действий. Чтобы проверить, как вычисляется формула, выполните следующие действия:
1. Выделите ячейку, которую необходимо вычислить. Вычислена может быть только одна ячейка за один раз.
2. Выберите команду Вычислить формулу.
3. Нажмите кнопку Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычислений показан курсивом. Если подчеркнутая часть формулы является ссылкой на другую формулу, нажмите кнопку Шаг с заходом, чтобы отобразить другую формулу в поле Вычисление. Нажмите кнопку Шаг с выходом, чтобы вернуться в предыдущую ячейку и формулу.
4. Продолжайте, пока каждая часть формулы не будет вычислена.
5. Чтобы снова увидеть вычисления, нажмите кнопку Начать сначала.
6. Чтобы закончить вычисления, нажмите кнопку Закрыть.
|
|
|
