Проверка данных и исправление ошибок
После ввода данных следует потратить время на проверку и исправление ошибок. Нужно убедиться в корректности числовых данных, а также проверить текст на наличие орфографических ошибок, воспользовавшись проверкой орфографии Excel. Когда проверка орфографии обнаруживает неизвестное слово, оно выделяется, и предлагаются наиболее подходящие варианты исправления ошибки. Вы можете исправить слово сами, выбрать нужное слово из списка предлагаемых или проигнорировать сообщение об ошибке. Вы также можете использовать проверку орфографии для добавления в словарь слов, которых нет в стандартном словаре, чтобы в дальнейшем программа Excel не выдавала сообщение об ошибке, обнаружив такое слово; тем самым вы освободитесь от необходимости каждый раз подтверждать корректность этого слова и сэкономите время. После того как вы внесли изменения, вы можете отменить изменения, внесенные с момента открытия рабочей книги. Чтобы отменить изменение, щелкните на соответствующей кнопке в панели инструментов или откройте меню Правка (Edit) и выберите команду Отменить (Undo). Если вы решили вернуть изменение, воспользуйтесь командой Повторить (Redo).
В этом упражнении вы обнаружите, что производитель "Уютных кресел" поменял наименование товара на "Легкое кресло". Используйте команду Найти (Find), чтобы найти слово "Уютное" и команду Заменить (Replace), чтобы заменить его на "Легкое". После этого воспользуйтесь кнопкой Формат (Find Format) в окне Найти и заменить (Find and Replace), чтобы заменить отформатированные специальным образом данные на новые. После этого проверьте ваш текст на наличие ошибок с помощью проверки орфографии.
На панели инструментов Стандартная щелкните на кнопке Открыть (Open)
Совет. Вы также можете открыть окно Найти и заменить (Find and Replace) нажатием клавиш (Ctrl)+(F).В поле Найти (Find What) введите уютное и нажмите кнопку Найти далее (Find Next). Отобразится первая ячейка, содержащая слово "уютное".В диалоговом окне Найти и заменить (Find and Replace) нажмите кнопку Найти далее (Find Next) еще раз. Отобразится следующая ячейка, содержащая слово "уютное". Совет. Нажатие кнопки Найти все (Find All) выведет список отвечающих запросу ячеек и их содержимое снизу от диалогового окна. Переместиться к любому из совпадений можно, щелкнув на нем кнопкой мыши.Щелкните на вкладке Заменить (Replace). Поле Найти (Find What) все еще содержит слово "Уютное".В поле Заменить на (Replace With) введите "Легкое".Щелкните на кнопке Заменить все (Replace All). Появится диалоговое окно, сообщающее о том, что операция завершена и было произведено три замены. Нажмите OK. Слова "Уютное" везде заменены на слова "Легкое". Важно. Вы можете заменять слова не все сразу, а одно за другим, нажимая кнопку Заменить (Replace) вместо кнопки Заменить все (Replace All). Это делается для того, чтобы контролировать каждую замену и не допускать замены тех данных, которые не требуется изменять. В диалоговом окне Найти и заменить (Find and Replace) выберите вкладку Найти (Find). Откроется вкладка Найти (Find). Удалите текст в поле Найти (Find What). Нажмите кнопку Параметры (Options) для отображения параметров вкладки Найти (Find). Появятся параметры вкладки Найти (Find).
Нажмите кнопку Формат (Format). Откроется диалоговое окно Найти формат (Find Format). Если необходимо, щелкните на вкладке Шрифт (Font) для ее открытия.
В меню Начертание (Font Style) выберите Курсив (Italic) и нажмите OK. Диалоговое окно Найти формат (Find Format) закроется. В диалоговом окне Найти и заменить (Find and Replace) нажмите кнопку Найти далее (Find Next). Отобразится первая ячейка, тексту которой присвоен шрифт курсив.
увеличить изображение
Щелкните на ячейке C12. На панели инструментов Форматирование (Formatting) нажмите кнопку Курсив (Italic)
Если необходимо, выберите слово "Дерево" в поле Варианты (Suggestions) и нажмите кнопку Заменить (Change). Excel заменит "Дерев" на "Дерево" и сообщит в диалоговом окне, что больше ошибок не найдено. Нажмите OK. Диалоговое окно закроется. Совет. Когда вы нажимаете кнопку Изменить (Change), Excel вставляет предложенное слово. Если вы не хотите заменять слово "Дерев", нажмите кнопку Пропустить (Ignore Once); нажатием кнопки Пропустить все (Ignore All) можно отменить исправление данного слова в рабочем листе. Щелкнув на кнопке Добавить в словарь (Add to Dictionary), можно добавить это слово в словарь, чтобы "Дерев" в дальнейшем воспринималось программой как корректное слово.
Создание рабочей книги
Каждый раз, когда вам нужно собрать и зафиксировать информацию, которая далека по своей тематике от информации, которая имеется в книге, нужно создавать новую рабочую книгу. В данной версии Excel новая рабочая книга по умолчанию содержит три рабочих листа; вы можете добавлять свои или удалять существующие листы по своему усмотрению. Создание новой рабочей книги - простое действие, осуществляющееся щелчком на соответствующей кнопке на панели инструментов. После создания рабочей книги вы можете начать ввод данных. Самый простой способ ввести информацию - это щелкнуть на ячейке и ввести значение; этот метод очень удобен, когда вы вводите информацию в малое количество ячеек, но такой прием нецелесообразен при работе с большими последовательностями или группами значений. Например, Кэтрин Тернер, нужно создать рабочий лист, отражающий ежечасную информацию о ходе продаж компании начиная с 13:00 и до 19:00 в будние дни. Для записи этих значений ей потребуется рабочий лист со следующей таблицей.
увеличить изображение
Повторный ввод последовательности Понедельник, Вторник, Среда, Четверг, Пятница может быть осуществлен посредством копирования и вставки из буфера первого элемента последовательности, но удобнее сделать это с помощью функции автозаполнения. Используя автозаполнение, вы вводите первый элемент известной последовательности, щелкаете на значке заполнения в правом нижнем углу ячейки и протаскиваете его, создавая область размера, необходимого для размещения вашей информации. Похожая функция, заполнение области, позволяет вам вводить два значения в области и значком заполнения расширять область в вашем рабочем листе. Например, если вам нужно создать область, начинающуюся со значения 2 и увеличивающуюся на 2, вам потребуется ввести 2 в первой ячейке и 4 во второй ячейке, затем выделить обе ячейки и, с помощью значка заполнения, расширить область ячеек до необходимой вам величины.
Другими способами ввода информации, которыми вы будете пользоваться в этом разделе, являются: автозавершение (определяет совпадение вводимой информации с данными, введенными ранее); выбрать из списка (позволяет выбрать нужное значение из значений в столбце). С помощью комбинации клавиш (Ctrl)+(Enter) вы можете вводить данные одновременно в нескольких ячейках.
| Автозаполнение | Введите первое значение известной последовательности и расширьте область ячеек с помощью значка заполнения. |
| Заполнение области | Введите первые два значения в области ячеек и с помощью значка заполнения расширьте область ячеек. |
| Автозавершение | Введите несколько первых символов в ячейке и, если вводимое значение совпадает с информацией, введенной ранее в этом же столбце, программа предложит ввести уже существующее значение. |
| Выбрать из списка | Щелкните правой кнопкой на ячейке и в появившемся меню щелкните на Выбрать из списка (Pick from List). Появится список существующих значений в данном столбце. |
| (Ctrl)+(Enter) | Выберите группу ячеек, которые должны содержать одну и ту же информацию, введите данные в активную ячейку и нажмите (Ctrl)+(Enter) |
| Копировать ячейки | Копировать содержимое выделенных ячеек в ячейки, обозначенные операцией заполнения. |
| Заполнить только значения | Заполнить ячейки, обозначенные операцией заполнения, следующими значениями из последовательности. |
| Заполнить только форматы | Присвоить формат выделенной ячейки ячейкам, обозначенным операцией заполнения, но не копировать значения из исходных в конечные ячейки. |
| Заполнить без форматирования | Заполнить ячейки, обозначенные операцией заполнения, следующими значениями в последовательности, но игнорировать формат исходных ячеек. |
| Заполнить <последовательность> | Эта опция работает по принципу обнаружения последовательностей и похожа по своему действию на опцию Заполнить только значения. Если вы вводите неизвестную программе Excel последовательность, эта опция не появится. |
В этом упражнении вы создадите рабочую книгу для учета числа покупателей компании "Все для сада", приобретающих товары в течение двух часов ежедневно на протяжении трех дней. В результате, рабочая книга будет содержать листы, несущие информацию о количестве покупателей, приобретавших товары (через каждые 15 минут), проданных товарах и количестве товаров в данной покупке. При заполнении рабочих листов вы будете использовать способы ввода данных, описанные выше, т.е. Автозаполнение, Заполнение области ячеек и комбинацию клавиш (Ctrl)+(Enter).
На панели инструментов Стандартная щелкните на кнопке Создать (New)
увеличить изображение
Щелкните на ячейке C6 и введите 3:00; затем щелкните на ячейке D6 и введите 3:15. Совет. Вы сообщаете Excel два значения, когда используете Заполнение области ячеек: первое значение - начальное для области, а второе определяет инкремент. В этом примере, 3:15 - на 15 минут больше, чем начальное значение 3:00, поэтому Excel прибавляет 15 минут к текущей ячейке, чтобы получить значение следующей ячейки в области. Щелкните на ячейке C6 и перетащите указатель на ячейку D6. Ячейки C6 и D6 будут выделены. Подведите указатель мыши к правому нижнему углу ячейки D6. Когда указатель мыши подведен к правому нижнему углу ячейки, он меняет форму на черный знак "плюс".
Изменение внешнего вида представления данных
В листах Excel может храниться и обрабатываться большой объем информации, но когда вы работаете с большим количеством листов, сложно определить по названию листа, какая информация в нем содержится. Смысловые заголовки сообщают о принадлежности содержащихся в листе данных, но важно также и визуально выделить заголовки из остальной информации. Чтобы выделить заголовки или любую другую информацию, вы можете изменять формат ячеек, в которых содержится эта информация.
Большинство средств, с помощью которых можно изменять формат ячейки, находятся в панели инструментов Форматирование.
увеличить изображение
Важно. В зависимости от разрешения вашего экрана и того, какие кнопки панели инструментов вы используете чаще всего, возможно, что на панелях инструментов Excel будут отображаться не все кнопки. Если кнопки, о которой идет речь в книге, нет на панели инструментов, нажмите на этой панели кнопку Параметры панели инструментов (Toolbars Options) с кнопкой списка, чтобы отобразить все остальные кнопки.
Вы можете применить форматирование, предлагаемое на панели инструментов, выделив ячейки, к которым вы хотите применить формат, и затем нажать соответствующую кнопку. Если вы хотите выделить заголовки полужирным шрифтом, воспользуйтесь кнопкой Полужирный (Bold). Если вы уже присвоили содержимому ячейки полужирный шрифт, то можно отменить это действие, нажав кнопку Полужирный (Bold)
Совет. Удаление всего содержимого ячейки не повлечет за собой удаления форматирования ячейки. Чтобы удалить формат ячейки, выделите нужную ячейку и затем, в меню Правка (Edit), укажите на пункт Очистить (Clear) и выберите Форматы (Formats).размер изображения, щелкнув на нем и перетащив одну из кнопочек, появившихся на картинке; имейте в виду, что в диалоговом окне Формат изображения (Picture Format) вы можете установить опцию, которая следит за тем, чтобы пропорции вашего рисунка при изменении размера не нарушались. Если вы изменили размер рисунка случайно, просто нажмите кнопку Отменить (Undo).
На кнопках панели инструментов Форматирование, которые предоставляют вам выбор действий, по правому краю кнопки есть стрелки, направленные вниз. Щелчок на этой стрелке вызывает список опций, относящихся к действию этой кнопки, например, выбор шрифтов, доступных на вашем компьютере, или цветов, которые вы хотите присвоить ячейке.
Чтобы использовать новые возможности рисования рамок, включите (если нужно) панель инструментов Границы (Borders).
Чтобы нарисовать рамку вокруг группы ячеек, щелкните указателем мыши на угле группы ячеек и перетащите указатель в противоположный угол по диагонали. Граница будет расширяться вместе с движением указателя мыши. Если вы хотите добавить границу в виде вертикальной или горизонтальной линии, перетащите указатель мыши вдоль нужной прямой линии, и Excel вставит линию, не распространив ее как рамку на соседние ячейки. Вы также можете менять тип добавляемой вами границы с помощью опций на панели инструментов Границы (Borders).
Другой способ, с помощью которого можно выделить группу ячеек - присвоить им цвет заливки. Например, в рабочем листе с информацией об объеме продаж компании "Все для сада" за месяц, Кэтрин Тернер, владелец компании, может изменить цвета заливки ячеек, содержащих заголовки (помимо изменения шрифта), чтобы выделить их еще сильнее.
FormatsНа панели инструментов Стандартная нажмите кнопку Открыть (Open)
увеличить изображение
Щелкните на ячейке G2. Ячейка G2 выделится. На панели инструментов Форматирование щелкните на стрелке кнопки Границы (Borders)
На панели инструментов Стандартная нажмите кнопку Сохранить (Save)
Сделайте информацию удобной для чтения
После того, как вы облегчили работу с листом, внеся в него соответствующие изменения, вы можете упростить восприятие информации в нем с помощью некоторых методов, направленных на повышение удобочитаемости. Один из случаев, когда вы, вероятно, захотите повысить удобство чтения - это когда информация в ячейке не помещается в ее границах, и вы не хотите объединять эту ячейку с той, за которой скрыт не поместившийся текст, руководствуясь, например, тем, что вам впоследствии нужно будет ввести данные в соседние ячейки.
Следующий рисунок показывает, как выглядит текст, не поместившийся в ячейку.
увеличить изображение
Если бы ячейки справа были пустыми, текст в левой ячейке просто отобразился бы на месте ячеек справа. Однако если в ячейках справа содержится информация, Excel расположит их поверх информации, находящейся в соседней ячейке. Чтобы предотвратить скрытие текста под ячейкой справа, вы можете привести текст в ячейке полностью, уместив его в пределах ячейки так, как показано на рисунке.
Совет. Может показаться удобным просто увеличить ширину столбца, чтобы вместить информацию, но помните, что увеличение одного столбца выделит его среди всех остальных столбцов рабочего листа и, возможно, снизит удобочитаемость информации в других столбцах.
Другой метод, позволяющий сделать данные более удобными для чтения - это закреплениеячеек. Можно сделать так, чтобы ячейки были всегда видны и располагались наверху рабочего листа, независимо от того, какое место рабочего листа отображается в окне в данный момент. Например, Кэтрин Тернер, владелец компании "Все для сада", не помнит, какая информация содержится в том или ином столбце рабочего листа. Закрепив наверху названия, указывающие на тематическую принадлежность данных каждого столбца, она сможет переходить к последней строке листа и при этом видеть вверху названия столбцов с данными. Excel выделяет границу между закрепленными и незакрепленными ячейками черной линией, именуемой границей раздела.
Совет. При закреплении строк в рабочем листе Excel закрепит строку, находящуюся над активной в данный момент ячейкой. Поэтому, если вы хотите закрепить три верхние строки листа, щелкните на ячейке в четвертой строке и затем выполните команду закрепления.DataRead
В этом упражнении вы предотвратите наложение ячейки на текст в соседних с ней ячейках, что позволит вводить комментарии в соседних с данной ячейках, избегая скрытия содержимого этой ячейки. Затем вы настроите выравнивание ячеек, содержащих названия столбцов вашего рабочего листа и закрепите их так, чтобы они оставались наверху страницы, когда вы перемещаетесь вниз по рабочему листу.
На панели инструментов Стандартная нажмите кнопку Открыть (Open). Появится диалоговое окно Открытие документа (Open). Дважды щелкните на файле DataRead.xls.
Файл DataRead.xls откроется.
Если необходимо, перейдите к листу Продажи по категориям, щелкнув на соответствующем ярлычке. Щелкните на ячейке B34. В меню Формат (Format) выберите Ячейки (Cells). Перейдите во вкладку Выравнивание (Alignment).
Поставьте галочку рядом со строкой Переносить по словам (Wrap text) и нажмите OK. Текст в ячейке B34 будет перенесен по словам и вписан в границы ячейки. Щелкните на ячейке B1 и перетащите указатель на ячейку Е1. В панели инструментов Форматирование нажмите кнопку По центру (Center)
увеличить изображение
Щелкните на ячейке А2. В меню Окно (Window) выберите Закрепить области (Freeze Panes). Линия раздела появится между строками 1 и 2. На вертикальной полосе прокрутки нажмите кнопку вниз. Строка 1 остается на месте, когда остальные строки перемещаются. В меню Окно (Window) выберите Снять разделение областей (Unfreeze Panes). Линия раздела исчезнет, и все строки будут перемещаться нормально. В панели инструментов Стандартная нажмите кнопку Сохранить (Save)
Сделайте работу с книгами удобнее
Важным шагом в облегчении работы с книгами является обеспечение простоты поиска информации в рабочей книге. Вы можете использовать несколько способов установки "указателей", помогающих пользователям найти нужную информацию. Первый способ, о котором рассказывалось в лекции "Знакомство с Excel", - это дать рабочей книге подходящее название. Вы также можете назвать рабочие листы так, чтобы пользователи, открыв нужную рабочую книгу, быстро нашли искомую информацию в соответствующем рабочем листе. Имена рабочих листов указываются на ярлычках листов в левом нижнем углу рабочей книги. Чтобы переименовать рабочий лист, щелкните правой кнопкой мыши на ярлычке нужного вам листа и выберите из появившегося меню команду Переименовать (Rename). Воспользовавшись этой командой, можно изменить имя рабочего листа. Вы также можете менять порядок расположения рабочих листов в рабочей книге, перетаскивая ярлычки листов на нужное место на панели и располагая наиболее часто используемые рабочие листы в начале списка.
После того как вы установили своего рода указатели для быстрого и удобного поиска информации, вы можете предпринять следующие действия для повышения удобочитаемости и простоты работы с информацией в рабочих книгах. Например, вы можете изменить ширину столбца или высоту строки в рабочем листе, перетаскивая границу столбца или строки в ту или иную сторону. Увеличение ширины столбца или высоты строки увеличивает пространство для содержимого ячейки, облегчая выделение информации в ячейке и помогая избежать случайного выделения данных из других ячеек.
Совет. Вы можете применить одни и те же изменения к нескольким строкам или столбцам, выделив строки и столбцы, размер которых необходимо изменить, и перетащив границу одной из выделенных строк или столбцов на нужное расстояние. Когда вы отпустите кнопку мыши, все выделенные строки или столбцы изменят свою высоту или ширину.
Изменение высоты строки или ширины столбца может повысить удобство работы с данными в рабочей книге, но вы также можете вставить строку или столбец между краем рабочего листа и ячейками с информацией. Дополнительное пространство между краями листа и ячейками или, например, между заголовком и текстом, к которому он относится, делает информацию в рабочей книге менее скученной и удобной для работы. Вы можете вставить строки, щелкнув на нужной ячейке и выбрав пункт Строки (Rows) в меню Вставка (Insert). Excel вставит строку над активной ячейкой. Вставка столбца происходит аналогично; нужно выбрать пункт Столбцы (Columns) в меню Вставка (Insert). Excel вставит столбец слева от активной ячейки.
Вы также можете по своему усмотрению добавлять в рабочий лист отдельные ячейки. Чтобы вставить ячейку, щелкните на ячейке, которая находится на том месте, куда бы вы хотели добавить новые ячейки, и выберите команду Ячейки (Cells) в меню Вставка (Insert). Появится диалоговое окно вставки. Здесь вы можете выбрать, каким образом вставить ячейки: либо со сдвигом вправо относительно выбранной ячейки (если данные располагаются в строке), либо со сдвигом вниз относительно выбранной ячейки (если это столбец). После нажатия кнопки ОК появятся новые ячейки, и информация в строке или столбце сдвинется вправо или вниз, соответственно.
Совет. Диалоговое окно вставки ячеек также позволяет вставлять строки и столбцы, выбрав соответствующую опцию.
Иногда изменение размеров строк и столбцов не является оптимальным способом повышения удобства работы с рабочей книгой. Например, даже если заголовок рабочего листа не помещается в одной ячейке, увеличение ее ширины (или ширины всех ячеек) может нарушить дизайн рабочего листа. Вы, конечно, можете разместить отдельные слова заголовка листа в отдельных ячейках, чтобы заголовок уместился на рабочем листе, но гораздо удобнее объединить две или более ячеек. Объединенные ячейки воспринимаются Excel в качестве одной ячейки с одним и тем же содержимым и форматом. Чтобы объединить ячейки в одну, нажмите на панели инструментов кнопку Объединить и поместить в центре (Merge and Center)
Совет. Щелкнув на объединенной ячейке и нажав кнопку Объединить и поместить в центре (Merge and Center), можно разделить сгруппированные ячейки.
Если вам нужно удалить строку или столбец, щелкните правой кнопкой мыши на заголовке строки или столбца и выберите из появившегося меню Удалить (Delete). Вы можете временно скрыть строки или столбцы, выбрав нужные строки или столбцы и выполнив команду Скрыть (Hide) в меню Формат (Format), пункты Строка (Row) или Столбец (Column). Строки и столбцы, которые вы выделили, исчезнут, как если бы вы использовали команду Удалить (Delete), но останутся на листе. Они будут убраны с экрана до тех пор, пока вы не вернете их на место командой Отобразить (Unhide) в меню Формат (Format), пункты Строка (Row) или Столбец (Column).
| Форматировать как сверху (FormatSame as Above) | Применить к вставленной строке форматстроки, находящейся сверху от нее. |
| Форматировать как снизу (FormatSame as Below) | Применить к вставленной строке форматстроки, находящейся снизу от нее. |
| Форматировать как слева (FormatSame as Left) | Применить к вставленному столбцу форматстолбца, находящегося слева от него. |
| Форматировать как справа (FormatSame as Right) | Применить к вставленному столбцу форматстолбца, находящегося справа от него. |
| Очистить формат (Clear Formatting) | Применить к вставленной строке или столбцу формат по умолчанию. |
В данном упражнении вы оптимизируете лист, содержащий информацию об объеме продаж за январь, делая его более удобочитаемым. Сначала вы называете рабочий лист и располагаете его поверх всех остальных листов в рабочей книге. Следующий ваш шаг - увеличение высоты строк и ширины столбцов ячеек с данными об объеме продаж. В дополнение к этому, вы объедините и отцентрируете заголовок рабочего листа и затем добавите строку между заголовком и строкой, содержащей информацию о времени, в течение которого компания Garden ведет учет проданных товаров. После этого вы вставите столбец слева от первого столбца с информацией и скроете все данные, кроме тех, которые относятся к первой неделе месяца.
На панели инструментов Стандартная нажмите кнопку Открыть (Open)
Щелкните на ярлычке листа Январь и перетащите его на место по левую сторону от ярлычка Лист1. Ярлычок листа Январь расположится слева от ярлычка Лист1. Во время перемещения ярлычка он выделяется инвертирующим черным прямоугольником. Щелкните на заголовке столбца А и перетащите указатель на столбец M. Выделятся столбцы с А по М. Расположите указатель мыши на правой границе столбца А и перетащите границу вправо до той позиции, когда в появившейся экранной подсказке будет значение ширины (Width) 10.00 (75 пикселей). Ширина выбранных столбцов изменится.
увеличить изображение
Выделите строки с 3 по 35. Строки с 3 по 35 выделятся цветом. Подведите указатель мыши к нижней границе строки 3 и перетащите вниз до значения Высоты (Height) 25.50 (34 пикселя). Высота выбранных строк изменится.
увеличить изображение
Щелкните на ячейке Е2 и перетащите на ячейку G2. На панели инструментов Форматирование нажмите кнопку Объединить и поместить в центре (Merge and Center). Ячейки E2, F2 и G2 объединятся в одну ячейку, и содержимое новой ячейки отцентрируется.
увеличить изображение
Важно. В зависимости от разрешения вашего экрана и того, какие кнопки панели инструментов вы используете чаще всего, возможно, что в панелях инструментов Excel будут отображаться не все кнопки. Если кнопки, о которой идет речь в книге, нет на панели инструментов, нажмите на этой панели кнопку со стрелкой, направленной вниз Параметры панелей инструментов (Toolbars Options), чтобы отобразить все остальные кнопки. Щелкните на ячейке А3. В меню Вставка (Insert) выберите Строки (Rows). Предыдущая строка с номером 3 сдвинется вниз и уступит место вставленной на ее место строке, которой присвоится номер 3. В меню Вставка (Insert) выберите Столбцы (Columns). Новый столбец А появится слева от столбца, прежде носившего индекс A. Выделите строки с 13 по 36. Строки с 13 по 36 выделятся цветом. В меню Формат (Format) укажите на пункт Строка (Row) и выберите Скрыть (Hide). Строки с 13 по 36 исчезнут из рабочего листа.
увеличить изображение
В меню Формат (Format) укажите на пункт Строка (Row) и выберите Отобразить (Unhide). Скрытые строки вновь появятся в рабочем листе. В панели инструментов Стандартная нажмите кнопку Сохранить (Save)
Изменение представления данных в зависимости от их значения
Хранение на рабочем листе информации по продажам, лимитам кредитования и другой информации, имеющей отношение к бизнесу, позволяет вам принимать серьезные решения. Как вы узнали из этой лекции, можно изменять оформление заголовков и самого рабочего листа, чтобы облегчить работу с данными.
Другой способ, позволяющий упростить работу с информацией - настроить Excel так, чтобы оформление данных изменялось в зависимости от их значения. Такие форматы называютсяусловными форматами, или форматами с условием, т.к. данные должны отвечать определенным условиям, чтобы к ним применился тот или иной формат. Например, если владелец компании Кэтрин Тернер хочет выделить все дни субботы, в которые объем продаж компании "Все для сада" составил более $4.000, ей потребуется создать условный формат, проверяющий значение в ячейке с данными по продажам и изменяющий формат содержимого ячейки при выполнении условия.
Чтобы создать условный формат, выделите ячейки, к которым вы хотите его применить, и откройте диалоговое окно Условное форматирование (Conditional Formatting). На рисунке ниже показаны настройки в диалоговом окне Условное форматирование (Conditional Formatting) по умолчанию.
Первая строка со списком позволяет выбрать в качестве аргумента условия либо содержимое ячейки, либо ее формулу. Почти во всех случаях вы будете использовать в качестве значения для условия содержимое ячейки.
Совет. Единственным случаем, когда вам потребуется установить формулу в качестве основания для условия, будет форматирование результата вычислений, например, итоговой суммы, которая будет таким же образом, как и обычные данные, отображаться в рабочем листе при возникновении соответствующих условий.
Вторая строка со списком в диалоговом окне Условное форматирование (Conditional Formatting) позволяет вам выбрать нужный тип сравнения. В зависимости от выбранного типа сравнения, в диалоговом окне будет либо одна, либо две строки, в которые нужно ввести значения для сравнения. Сравнение по умолчанию, "между", требует два значения, тогда как сравнения "меньше, чем" требуют ввода только одного значения.
После того как вы создали условие, вам нужно установить формат, который будет применяться к данным, отвечающим данному условию. Формат создается в диалоговом окне Формат ячеек (Format Cells). С помощью этого диалогового окна можно настроить шрифт, которым отображается значение в ячейке. Определенный вами формат появится в диалоговом окне Условное форматирование (Conditional Formatting).
Если это необходимо, можно не ограничиваться присвоением лишь одного условия каждой ячейке. Можно создать дополнительные условия, нажав кнопку А также (Add) в диалоговом окне Условное форматирование (Conditional Formatting). После нажатия кнопки А также (Add) появится окно второго условия.
Важно. Excel не проверяет ваши условия на логическую завершенность, поэтому вам нужно внимательно следить за корректностью каждого из вводимых вами условий.
Excel выполняет условия в том порядке, в котором вы задали их в диалоговом окне Условное форматирование (Conditional Formatting) и, при нахождении условия, которому отвечают данные, прекращает сравнения. Например, предположим, что Кэтрин хочет визуально разделить лимиты кредитования покупателей компании "Все для сада" на две различные категории: лимиты меньше 1.500$, и лимиты в пределах от 1.500$ до 2.500$. Она может установить отображение лимитов кредитования покупателей с применением условного формата, использующего условия, показанные на рисунке ниже.
В этом случае, Excel будет сопоставлять значение "1250" с первым значением, "<2500", и применять форматирование к ячейке, содержащей это значение. Второе условие, "<1500", ближе по модулю к сравниваемому с ним значению; как только Excel найдет условие, отвечающее этому значению, сравнение прекратится.
Совет. В первую очередь всегда задавайте наиболее строгое условие. В рассматриваемом примере установка первым условия "<1500" и вторым "<2500" даст правильный результат форматирования.Conditional
В этом упражнении вы создадите области условных форматов для изменения отображения данных в ячейках листа, содержащих лимит кредитования покупателей компании "Все для сада".
В панели инструментов Стандартная нажмите кнопку Открыть (Open)
На панели инструментов Стандартная нажмите кнопку Сохранить (Save), чтобы сохранить сделанные изменения. Нажмите кнопку Закрыть (Close) для закрытия документа Conditional.xls.
Excel выполняет условия в том порядке, в котором вы задали их в диалоговом окне Условное форматирование (Conditional Formatting) и, при нахождении условия, которому отвечают данные, прекращает сравнения. Например, предположим, что Кэтрин хочет визуально разделить лимиты кредитования покупателей компании "Все для сада" на две различные категории: лимиты меньше 1.500$, и лимиты в пределах от 1.500$ до 2.500$. Она может установить отображение лимитов кредитования покупателей с применением условного формата, использующего условия, показанные на рисунке ниже.
В этом случае, Excel будет сопоставлять значение "1250" с первым значением, "<2500", и применять форматирование к ячейке, содержащей это значение. Второе условие, "<1500", ближе по модулю к сравниваемому с ним значению; как только Excel найдет условие, отвечающее этому значению, сравнение прекратится.
Совет. В первую очередь всегда задавайте наиболее строгое условие. В рассматриваемом примере установка первым условия "<1500" и вторым "<2500" даст правильный результат форматирования.Conditional
В этом упражнении вы создадите области условных форматов для изменения отображения данных в ячейках листа, содержащих лимит кредитования покупателей компании "Все для сада".
В панели инструментов Стандартная нажмите кнопку Открыть (Open)
Повышение удобочитаемости чисел
Изменение формата ячеек в рабочем листе может намного облегчить восприятие ваших данных. Можно располагать заголовки отдельно от остальной информации и заключать их в рамки, чтобы еще ярче выделять границы между заголовками и данными. Однако изменение шрифта и внешнего вида содержимого ячейки не скажется на удобочитаемости дат, номеров телефонов или денежных сумм.
В качестве примера рассмотрим телефонные номера США. Эти номера, состоящие из десяти цифр, имеющие 3-значный код штата, 3-значный номер АТС и 4-значный номер линии, записываются в виде (###) ###-####. Конечно, можно вводить в ячейки телефонные номера, соблюдая правила их написания, но гораздо легче просто ввести последовательность цифр, чтобы они автоматически преобразовались должным образом.
Вы можете настроить Excel так, чтобы введенная последовательность цифр преобразовывалась в телефонный номер. Откройте диалоговое окно Формат ячеек (Format Cells), перейдите во вкладку Число (Number) и, щелкнув на числовом формате Дополнительный (Special), выберите в поле Тип (Type) формат Телефонный номер (Phone Number).
Теперь любая последовательность из десяти цифр будет преобразована в телефонный номер. Убедиться в том, что эта операция не искажает содержимое ячейки, а лишь преобразует его, можно, сравнив содержимое активной ячейки и строки формул (см. рисунок).
Совет. Если вы ввели 9-значный номер в ячейке, в которой установлена опция распознавания телефонного номера, сообщения об ошибке не последует; в данном случае, код штата будет двухзначным. Например, номер 425555012 отобразится как (42)555-5012. 11-значные номера отображаются как номера с 4-значным кодом штата.
Точно так же, как и в случае с преобразованием телефонного номера, можно настроить Excel и для отображения даты или денежных сумм. Эти изменения можно сделать из диалогового окна Формат ячеек (Format Cells), выбрав либо числовой формат Дата (Date), либо Финансовый (Currency). В формате Дата (Date) можно настроить ячейку на отображение даты (и включить или выключить изменение представления даты в зависимости от настроек Local Settings операционной системы компьютера при просмотре рабочей книги). Таким же образом, выбрав Финансовый (Currerncy) формат числа, можно настроить число знаков после запятой (десятичной точки), а также выбрать нужный символ валюты и способ отображения отрицательного баланса.
Также можно создать свой числовой формат с возможностью добавления слова или фразы к числу в ячейке. Например, можно добавить фразу "в месяц" в ячейку с формулой, которая подсчитывает средний объем ежемесячных продаж за год, чтобы запись в ячейке воспринималась именно как средний объем продаж за месяц. Чтобы создать свой собственный числовой формат, выберите пункт Ячейки (Cells) в меню Формат (Format), чтобы открыть диалоговое окно Формат ячеек (Format Cells). Затем перейдите во вкладку Число (Number).
В списке числовых форматов выберите Все форматы (Custom), чтобы отобразить существующие форматы в списке Тип (Type). Теперь вы можете выбрать подходящий вам базовый формат для редактирования его в строке Тип (Type). Например, при выборе формата "0.00", Excel будет отображать все числа в ячейках как числа с двумя знаками после запятой.
Совет. Нули в числовом формате означают, что на их месте может стоять любое число, и оно будет воспринято как корректное значение.
Чтобы изменить формат, щелкните на строке Тип (Type) и добавьте к формату любые нужные вам символы или текст. Например, если ввести перед форматом значок "$", а справа от формата ввести "в месяц", число 1500 будет отображаться в виде "$1500.00 в месяц".
Важно. Чтобы введенная вами информация отображалась в ячейке как текст, ее необходимо заключить в кавычки.EasyRead
В этом упражнении вы присвоите диапазонам ячеек рабочего листа форматы даты, телефонного номера и денежной суммы. После этого вы проверите их действие, вводя информацию о покупателе.
На панели инструментов Стандартная нажмите кнопку Открыть (Open)
Выберите в списке Тип (Type) "*3/14/01". Нажмите ОK. Excel присвоит ячейке выбранный формат. На панели инструментов Стандартная нажмите кнопку Формат по образцу (Format Painter)
В поле Тип (Type) выберите формат #,##0.00. В строке Тип (Type) отобразится "#,##0.00". Щелкните на месте слева от формата в строке Тип (Type) и введите $, затем щелкните справа от формата и введите всего. Нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется. На панели инструментов Стандартная нажмите кнопку Формат по образцу (Format Painter). Ячейка K4 выделится пунктирной линией. Щелкните на ячейке K5 и перетащите указатель на ячейку K23. Excel присвоит формат ячейки K4 ячейкам K5:K23. В ячейке В4 введите 25 января 2001 года и нажмите (Enter). Содержимое ячейки сменится на "1/25/01", соответственно тому формату, который вы установили ранее. В ячейке С4 наберите С100001. В ячейке D4 наберите Стивен. В ячейке E4 наберите Леви. В ячейке F4 наберите 6789 Элм Стрит. В ячейке G4 наберите Рэдмонд. В ячейке H4 наберите штат Вашингтон. В ячейке I4 наберите 87063. В ячейке J4 наберите 4255550102. Содержимое ячейки изменится на "(425) 555-0102", согласно заданному вами формату. В ячейке K4 наберите 2400. Содержимое ячейки изменится на "$2,400.00 всего", согласно созданному вами ранее формату.
На панели инструментов Стандартная нажмите кнопку Сохранить (Save) для сохранения изменений. Нажмите кнопку Закрыть (Close)
Применение существующего формата к данным
При работе с Excel вы, вероятно, будете применять свои собственные форматы к заголовкам, названиям и другим элементам рабочего листа. Вместо того чтобы каждый раз присваивать ячейкам отдельные элементы форматирования, вы можете сохранить созданный формат и переименовать его для дальнейшего использования. Доступные стандартные форматы вы можете найти в диалоговом окне Стиль (Style).
В диалоговом окне Стиль (Style) вы можете присвоить ячейке существующий стиль. Если в списке стилей отсутствует нужный вам стиль, вы можете создать ваш собственный, набрав имя в поле Имя стиля (Style Name) и затем нажав кнопку Изменить (Modify). Откроется диалоговое окно Формат ячеек (Format Cells). Настроив характеристики вашего нового стиля, нажмите ОK, чтобы добавить его в список стилей.
В диалоговом окне Стиль (Style) довольно много параметров, и использовать его лишь для того, чтобы применять формат, присвоенный вами одной ячейке, к содержимому других ячеек, нерационально. Это удобнее сделать с помощью кнопки Формат по образцу (Format Painter)
CreateNew
В этом упражнении вы создадите формат, примените его к заголовку и затем используете Формат по образцу (Format Painter) для присвоения нового стиля содержимому другой ячейки.
На панели инструментов Стандартная нажмите кнопку Открыть (Open)
На панели инструментов Стандартная нажмите кнопку Сохранить (Save)
Поиск и исправление ошибок в вычислениях
Применение вычислений в рабочем листе решает много проблем при работе с данными. Однако всегда существует возможность того, что в ваших формулах есть ошибки. Excel облегчает поиск ошибок в формулах, определяя, какие ячейки использовались в вычислении и описывая все обнаруженные ошибки. Процесс проверки рабочего листа на наличие ошибок в формулах называется аудитом.
Excel обозначает обнаруженные ошибки несколькими способами. Первый способ - отображение кода ошибки в ячейке, содержащей формулу, в которой обнаружена ошибка. На рисунке ниже ячейка D8 отображает код ошибки"#ИМЯ?".
Когда ячейка с ошибкой является активной ячейкой, рядом появляется кнопка Ошибка (Error)
| ##### | Ширина столбца недостаточна для того, чтобы вместить значение |
| #ЗНАЧ! | В формулу введен неверный тип аргумента (например, текст, там, где должны быть значения ИСТИНА или ЛОЖЬ) |
| #ИМЯ? | Формула содержит текст, который не распознается Excel (например, неизвестный диапазон ячеек) |
| #ССЫЛКА! | Формула ссылается на несуществующую ячейку (это может произойти, если, например, ячейки были удалены) |
| #ДЕЛ/0! | Попытка деления на ноль в формуле |
Другой способ предотвратить ошибки в формулах - убедиться, что соответствующие ячейки содержат необходимые для формулы значения. Например, вы хотели вычислить полный объем продаж в какой-либо категории товара, но случайно написали формулу, ссылающуюся на названия товаров, а не на их цены. Такую ошибку можно обнаружить посредством обозначения прецедентов ячейки(иливлияющих ячеек), т. е. ячеек, значения которых используются в формуле активной ячейки. Excel определяет прецеденты ячеек, отображая синюю стрелку проверки от прецедента к активной ячейке.
Вы также можете проверять ваш рабочий лист, определяя ячейки с формулами, которые используют значение данной ячейки. Например, формула, вычисляющая среднюю стоимость всех заказов, полученных за день, использует полную стоимость одного заказа. Ячейки, использующие для своих вычислений значения в других ячейках, называются зависимыми, т. е. результаты их собственных вычислений зависят от содержимого других ячеек. Так же, как и в случае с обозначением влияющих ячеек, вы можете указать на пункт Зависимости формул (Formula Auditing) в меню Сервис (Tools) и выбрать Зависимые ячейки (Trace Dependents), чтобы программа Excel отобразила синие стрелки от активной ячейки к тем ячейкам, в которых производятся вычисления с использованием данных этой ячейки.
Если стрелки указывают на неверные ячейки, вы можете убрать стрелки и исправить формулу. Чтобы убрать с рабочего листа стрелки зависимости, подведите указатель мыши к пункту Зависимости формул (Formula Auditing) в меню Сервис (Tools) и выберите Убрать все стрелки (Remove All Arrows).
FindErrors
В этом упражнении вы используете
В этом упражнении вы используете возможности аудита формул в Exсel для обнаружения и исправления ошибок в формулах.
На панели инструментов Стандартная нажмите кнопку Открыть (Open)
В меню Сервис (Tools) подведите указатель мыши к пункту Зависимости формул (Formula Auditing) и выберите Убрать все стрелки (Remove all Arrows). Стрелка исчезнет. Щелкните на ячейке D20. В строке формул появится выражение =СРЗНАЧ(D7,D15). В меню Сервис (Tools) укажите на пункт Зависимости формул (Formula Auditing) и выберите Источник ошибки (Trace Error). Появятся синие стрелки, указывающие из ячейки D20 на ячейки D7 и D15. Эти стрелки сообщают о том, что использование значений (или отсутствие значений, в данном случае) в указанных ячейках вызывает возникновение ошибки в ячейке D20.
В меню Сервис (Tools) укажите на пункт Зависимости формул (Formula Auditing) и затем выберите Убрать все стрелки (Remove All Arrows). Стрелки исчезнут. Удалите текущую формулу из строки формул, введите =СРЗНАЧ(D8,D16) и нажмите (Enter). В ячейке D20 отобразится значение $149.08. На панели инструментов Стандартная нажмите кнопку Сохранить (Save)
Присвоение названий группе данных
Когда вы работаете с большими объемами информации, желательно идентифицировать группы ячеек, которые содержат соответствующую информацию. Например, на рисунке ниже ячейки с С2 по С6 содержат цены на товары, заказанные покупателем.
Вместо того чтобы указывать ссылки на ячейки каждый раз, когда вам нужно обратиться к информации в них, вы можете объединить эти ячейки в диапазон(или названный диапазон). Например, вы можете объединить товары из предыдущего рисунка в диапазон, названный "ЗаказанныеТовары1". Если вам нужно использовать данные из этого диапазона в вычислениях, вы можете просто ввести имя диапазона вместо того, чтобы указывать ссылку на каждую ячейку.
Существует несколько способов создания названного диапазона, два из которых осуществляются из меню Вставка (Insert). Первый метод полезен, если у вас в листе есть столбец данных с заголовком вверху столбца, как показано на рисунке.
В данном случае, вы открываете диалоговое окно Создать имена (Create Name), выбрав в меню Вставка (Insert) подменю Имя (Name) и щелкнув на команде Создать (Create). В диалоговом окне Создать имена (Create Name) вы можете создать названный диапазон, присвоив ему в качестве имени заголовок (верхнюю ячейку) столбца. Вы также можете создавать и удалять диапазоны через диалоговое окно Присвоение имени (Define Name), открыть которое можно, указав на пункт Имя (Name) в меню Вставка (Insert) и выбрав Присвоить (Define).
Наконец, можно создать диапазон, выбрав ячейки, которые вы хотите объединить в диапазон, и введя в строке Имя (Name) название диапазона. Вы можете получить список диапазонов, созданных на листе, щелкнув в этой строке на кнопке списка.
Важно. Каждый диапазон в рабочей книге должен иметь свое имя. Присвоение имени уже существующего диапазона новому удалит первоначальное имя- ссылку, аналогично тому, как это происходит при присвоении имени рабочему листу. NameRange
В этом упражнении вы создадите диапазоны, чтобы адресовать ссылки на группы ячеек.
На панели инструментов Стандартная нажмите кнопку Открыть (Open). Появится диалоговое окно Открытие документа (Open). Перейдите в папку Chap07 и дважды щелкните на файле NameRange.xls. Файл nameRange.xls откроется. Перейдите к листу Инструменты. Щелкните на ячейке С3 и перетащите указатель на ячейку С18. Выбранные ячейки выделятся цветом. В меню Вставка (Insert) укажите на пункт Имя (Name) и затем выберите Создать (Create). Откроется диалоговое окно Создать имена (Create names).
Отметьте пункт В строке выше (Top Row). Нажмите OK. Excel присвоит диапазону ячеек имя "Цена". В нижнем левом углу окна рабочей книги щелкните на ярлычке листа Поставки. Появится лист Поставки. Щелкните на ячейке С4 и перетащите указатель на ячейку С29. В меню Вставка (Insert) укажите на пункт Имя (Name) и выберите Присвоить (Define). Откроется диалоговое окно Присвоение имени (Define name).
В строке Имя (Names in Workbook) введите ЦеныНаПоставки и нажмите ОК. Excel присвоит имя "ЦеныНаПоставки" диапазону ячеек, и диалоговое окно Присвоение имени (Define Name) закроется. В левом нижнем углу окна рабочей книги щелкните на ярлычке листа Фурнитура. Появится рабочий лист Фурнитура. Щелкните на ячейке С4 и перетащите указатель на ячейку С18. Щелкните на строке Имя (Name). Содержимое строки Имя (Name) выделится. Введите СтоимостьФурнитуры и нажмиnе (Enter). Excel присвоит диапазону ячеек имя "СтоимостьФурнитуры". В меню Вставка (Insert) укажите на пункт Имя (Name) и затем выберите Присвоить (Define). Откроется диалоговое окно Присвоение имени (Define Name). В нижней области диалогового окна щелкните на строке "Цена". В поле Имя (Names in workbook) появится слово "Цена". В поле Имя (Names in workbook) удалите слово Цена, введите СтоимостьИнструментов и нажмите ОК. Диалоговое окно Присвоение имени (Define Name) закроется. На панели инструментов Стандартная нажмите кнопку Сохранить (Save)
Создание формул для вычисления значений
После того как вы добавили информацию в рабочий лист и создали диапазоны ячеек, можно ускорить обращение к информации, написав формулу, т.е. выражение, которое производит вычисления с вашими данными. Например, вы можете вычислить общую сумму заказа покупателя, вывести средний объем продаж для всех дней среды января месяца или найти максимальный или минимальный уровень продаж за неделю, месяц или год.
Чтобы написать формулу Excel, начните вводить данные в ячейку со знака равенства, тогда они будут интерпретироваться как выражение для вычисления, а не текст. После знака равенства вы вводите формулу. Например, вы можете найти сумму значений в ячейках С2 и С3 с помощью формулы =С2+С3. После того как вы ввели формулу в ячейку, вы можете проверить ее, щелкнув на ячейке и отредактировав ее содержимое в строке формул. Например, вы можете заменить эту формулу на =C3-C2, для вычисления разности между содержимым ячеек С2 и С3.
Подсказка. Если Excel распознает вашу формулу как текст, проверьте, нет ли перед знаком равенства пробела или другого случайно введенного символа. Помните, знак равенства должен быть первым символом!
Ввод ссылок на 15 или 20 ячеек может показаться утомительным, однако в Excel легко работать со сложными вычислениями. Для задания нового вычисления выберите пункт Функция (Function) в меню Вставка (Insert). Откроется диалоговое окно Мастер функций (Insert Function) со списком функций, или предопределенных формул, из которого вы можете выбрать нужную вам функцию.
В следующей таблице описаны наиболее часто используемые функции.
| СУММ(SUM) | Вычисляет сумму чисел в заданных ячейках |
| СРЗНАЧ(AVERAGE) | Находит среднее значение чисел в заданных ячейках |
| СЧЕТ(COUNT) | Подсчитывает количество чисел в списке аргументов в заданных ячейках |
| МАКС(MAX) | Находит наибольшее значение в заданных ячейках |
| МИН(MIN) | Находит наименьшее значение в заданных ячейках |
Вы также можете использовать две другие функции, ТДАТА() [NOW()] и ПЛТ() [PMT()]. Функция ПЛТ() возвращает время, когда рабочая книга была открыта, поэтому значение функции будет изменяться каждый раз, когда будет открываться рабочая книга. Правильная запись функции выглядит так: =ТДАТА; чтобы обновить текущее время и дату, просто сохраните работу, закройте и снова откройте документ. Функция ПЛТ() немного сложнее. Она вычисляет сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки. Чтобы произвести с ее помощью вычисления, функции требуется задать ставку, количество месяцев платежей и стартовый баланс. Элементы, вводимые в функцию, называются аргументамии должны быть введены в определенном порядке. Этот порядок выглядит так: ПЛТ(ставка;кпер;пс;бс;тип). В следующей таблице приведены описания каждого аргумента функции ПЛТ.
| ставка | Процентная ставка по ссуде, делится на 12 для определения ежемесячных выплат по ссуде |
| кпер | Общая число выплат по ссуде |
| пс | Приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой |
| бс | Требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение бс равно 0 |
| тип | Число 0 (нуль) или 1, обозначающее, когда должна производиться выплата |
В этом упражнении вы создадите формулу для подсчета общей суммы заказа, копируете эту формулу в другую ячейку и затем создаете формулу, вычисляющую среднюю стоимость товаров в заказе. Ячейки с ценами на товары в этом заказе объединены в диапазон, названный "ТоварыЗаказа".
На панели инструментов Стандартная нажмите кнопку Открыть (Open)
Щелкните на ячейке D7 и затем на панели инструментов Стандартная нажмите кнопку Копировать (Copy)
Щелкните на ячейке C10. В меню Вставка (Insert) выберите Функция (Function). Откроется диалоговое окно Мастер функций (Insert Function). В списке Выберите функцию (Select a function) щелкните на функции ЕСЛИ и нажмите ОК. Откроется диалоговое окно Аргументы функции (Function Arguments). В строке Логическое_выражение (Logical_test) введите D7>50. В строке Значение_если_истина (Value_if_true) введите "скидка 5%". В строке Значение_если ложь (Value_if_false) введите "Без скидки" и нажмите ОК. Диалоговое окно Аргументы функции (Function Arguments) закроется, и в ячейке С10 появится текст "скидка 5%".
На панели инструментов Стандартная нажмите кнопку Сохранить (Save)
Ограничение отображения информации
Таблицы Excel могут содержать очень много информации, но вам может потребоваться работать лишь с некоторыми конкретными данными рабочего листа. Например, вам нужно изучить объем продаж вашей компании за первую, вторую и последнюю треть месяца. Вы можете ввести ограничения на показ информации в листе, создав фильтр, т.е. правило, согласно которому те или иные строки рабочего листа отображаются или скрываются.
Чтобы создать фильтр, щелкните на ячейке в области, которую вы хотите отфильтровать, и в меню Данные (Data) включите опцию Автофильтр (AutoFilter). Когда вы включите автофильтр, являющийся набором нескольких средств фильтрации, в ячейке, которая распознана программой Excel как заголовок столбца, появится кнопка списка.
Важно. При включении фильтрации Excel принимает ячейки столбца под заголовком за диапазон ячеек. Для правильности работы фильтрации необходимо всегда добавлять заголовок к столбцу, который вы хотите отфильтровать.
Щелчок на кнопке списка выведет перечень значений и опций. Первые несколько пунктов в списке - опции фильтрации, такие как, например, отображение только первых десяти значений в столбце, создание своего собственного фильтра или отображение всех значений в столбце (то есть отключение фильтра). Остальными пунктами списка являются отдельные значения столбца; щелкнув на одном из этих значений можно отобразить строку или строки, содержащие это значение.
Выбор опции Первые 10 (Top 10) не только ограничивает отображение данных первыми десятью значениями; также открывается диалоговое окно Первые 10 - автофильтр (Top 10 AutoFilter). В этом окне вы можете выбрать, откуда начинать отображение данных, либо сверху, либо снизу столбца, задать количество значений, которые должны отображаться, а также указать, в каком формате введено количество значений - в числах, либо в процентах. Используя диалоговое окно Первые 10 - автофильтр (Top 10 AutoFilter), можно определить десять самых удачных продавцов вашей компании или определить, какие покупатели входят в пять процентов самых активных.
Выбрав пункт Расширенный фильтр (Custom) в меню Фильтр (AutoFilter), вы можете установить правило, согласно которому Excel будет определять, какие строки отображать после включения фильтра. Например, вы можете установить правило, согласно которому на рабочем листе будут отображаться только те дни, в которые объем продаж составил менее $2.500. Анализируя эти данные, можно выяснить, какие факторы в эти дни плохо сказываются на интенсивности продаж вашей компании.
В Excel можно осуществлять два взаимосвязанных действия - выбирать произвольным образом строки и отображать конкретные значения в столбце рабочего листа (не из перечня под кнопкой списка, т.к. в данном случае нормальная работа с ним невозможна). Сгенерировав список отдельных значений и поместив их в одну колонку, можно получить важную информацию, например, выяснить, клиенты из каких городов покупают ваши товары, или какие виды товаров покупаются в течение часа.
Выбор строк произвольным образом полезен, например, при рассылке покупателям специальных предложений, при выборе дней месяца для проведения аудита или при определении победителя при розыгрыше призов. Для выбора строк можно использовать функцию СЛЧИС (RAND), которая генерирует случайное значение и сопоставляет его со значением условия, введенным в выражении. Выражением, возвращающим значение ИСТИНА в 30% случаев, будет RAND()<=30%; вы можете использовать это выражение для выбора каждой строки в листе с вероятностью 30%.
В этом упражнении вы создадите фильтр, чтобы отобразить пять самых удачных дней продаж января месяца, показать объем продаж за дни понедельника этого же месяца, отобразить дни с объемом продаж не менее 3.000$, выбрать случайным образом дни месяца для проведения аудита и сгенерировать список отдельных значений в одном из столбцов рабочего листа.
На панели инструментов Стандартная нажмите кнопку Открыть (Open)
Щелкните на средней строке, удалите "10", введите 5 и нажмите OK. Отобразятся только те строки, которые содержат пять наибольших значений в столбце P.
В меню Данные (Data) укажите на пункт Фильтр (Filter) и выберите Автофильтр (AutoFilter). Отобразятся отфильтрованные строки.Щелкните на ячейке B5.В меню Данные (Data) укажите на пункт Фильтр (Filter) и выберите Автофильтр (AutoFilter). В ячейке B5 появится кнопка ниспадающего меню.В ячейке B5 щелкните на кнопке списка и выберите Пн. На рабочем листе будут отображаться только строки со значением "Пн" в столбце В.В меню Данные (Data) укажите на пункт Фильтр (Filter) и выберите Автофильтр (AutoFilter). Появятся отфильтрованные строки.Щелкните на ячейке Р5, в меню Данные (Data) укажите на пункт Фильтр (Filter) и выберите Автофильтр (AutoFilter). В ячейке Р5 появится кнопка списка. В ячейке Р5 щелкните на кнопке списка и выберите Условие: (Custom:). Откроется диалоговое окно Пользовательский Автофильтр (Custom AutoFilter).
В левой верхней строке щелкните на кнопке списка и выберите Больше или равно (Is greater than or equal to).В правой верхней строке введите 3000 и затем нажмите OK. На рабочем листе теперь будут отображаться только значения, большие или равные 3000.
Установка набора допустимых значений для данного диапазона ячеек
Важным моментом в создании удобочитаемого рабочего листа является обеспечение корректности вводимых в лист значений. Несмотря на то, что обнаружение каждой опечатки или орфографической ошибки невозможно, вы можете установить Правило проверки (Validation Rule)для уверенности в том, что вводимая в ячейки информация отвечает определенным стандартам.
Чтобы создать правило проверки, откройте диалоговое окно Проверка вводимых значений (Data Validation).
Диалоговое окно Проверка вводимых значений (Data Validation) (см. рис. вверху следующей страницы) может быть использовано для определения типа данных, которые должны вводиться в определенную ячейку, и затем, в зависимости от выбранного вами типа данных, для установки условий, которым должна отвечать информация для ее отображения в ячейке. На следующем рисунке показано, как сделать, чтобы в ячейку можно было ввести, например, только значения от 1000 до 2000.
Установка необходимых правил проверки поможет предотвратить ввод, например, имени покупателя, в ячейку, в которую должен быть введен номер его телефона, или не допустить ввод размера денежной суммы, превышающий определенное значение. Чтобы от пользователя требовался ввод в ячейку численного значения, войдите во вкладку Параметры (Settings) диалогового окна Проверка вводимых значений (Data Validation) и, нажав кнопку списка Тип данных (Allow), выберите из списка Целое значение (Whole Number) или Действительное (Decimal), в зависимости от того, что вам требуется.
Несомненно, было бы неразумным запрещать ввод в ячейку не соответствующей ей информации и при этом не выводить подсказку, помогающую пользователю разобраться, в какую ячейку вводить те или иные данные. Excel позволяет создавать подсказки, сообщающие пользователю о том, какая информация должна быть введена в ячейку, и, если она не отвечает этим условиям, выдает сообщение об ошибке.
Можно отключить проверку данных в ячейке, нажав кнопку Очистить все (Clear All) в левом нижнем углу диалогового окна Проверка вводимых значений (Data Validation) на вкладке Параметры (Settings).
Validate
В этом упражнении вы создадите правило проверки, ограничивая размер кредитования клиентов компании "Все для сада" суммой $2.500, добавите сообщение о лимите кредитования и создадите сообщение об ошибке, которое будет появляться при попытке ввода суммы больше $2.500. После этого вы проверите работу правил проверки и вывод сообщений.
На панели инструментов Стандартная нажмите кнопку Открыть (Open)
Нажмите Да (Yes). Окно предупреждения исчезнет. Важно. Нажав кнопку Нет (No), вы можете отредактировать значение, а нажатие кнопки Отмена (Cancel) удалит введенные данные. Щелкните на ячейке J4. Ячейка J4 станет активной, и подсказка появится снова.Введите 2500 и нажмите (Enter). Excel примет введенное вами значение как правильное.Щелкните на ячейке J4.В меню Данные (Data) выберите пункт Проверка (Validation). Откроется диалоговое окно Проверка вводимых значений (Data Validation).Перейдите на вкладку Параметры (Settings).В строке Тип данных (Allow) нажмите кнопку списка и выберите Любое число (Whole Number).Нажмите OK.В ячейке J4 введите 2499.95 и нажмите (Enter). Появится предупреждающее сообщение с заголовком Ошибка (Error) и текстом по умолчанию.Нажмите Нет (No). Окно предупреждения закроется, ячейка J4 станет активной, и вновь появится экранная подсказка.Введите 2500 и нажмите (Enter).Excel примет введенные вами данные.В панели инструментов Стандартная нажмите кнопку Сохранить (Save)
Вычисления над отфильтрованными данными
При фильтрации рабочего листа вы ограничиваете отображение информации. Возможность сконцентрировать внимание на информации, нужной в данный момент, необходима, но есть несколько важных моментов. Одним из них является то, что любая формула не изменяет своих вычислений, даже если используемые в формуле строки скрыты фильтром.
Существуют два метода нахождения суммы значений группы отфильтрованных ячеек. Первый из них предусматривает использование Автовычисления (AutoCalculate). Чтобы применить автовычисление, выделите нужные ячейки, и сумма значений ячеек отобразится в области автовычисления строки состояния внизу окна Excel.
При использовании автовычисления никаких ограничений на поиск суммы значений выделенных ячеек не накладывается. Возможности подсчета значений не ограничиваются лишь суммой. Щелкните правой кнопкой мыши на области автовычисления и выберите из появившегося меню нужную вам команду.
Автовычисление полезно для быстрого нахождения суммы или среднего значения отфильтрованных ячеек, но оно не добавляет результат вычислений в рабочий лист. Чтобы отобразить результат в рабочем листе, можно создать функцию СУММ. Так же, как с автовычислением, вы можете выбирать операцию, производимую функцией.
Calculation
В этом упражнении вы используете автовычисление для нахождения суммы группы ячеек в отфильтрованном рабочем листе, создаете функцию СУММ для появления значения суммы в рабочем листе и редактируете функцию СУММ таким образом, чтобы она подсчитывала не сумму, а среднее значение.
В панели инструментов Стандартная нажмите кнопку Открыть (Open)
В ячейке Р5 появится кнопка списка.
Нажмите кнопку списка в ячейке Р5 и выберите из списка Первые 10: (Top 10:).
Откроется диалоговое окно Первые 10 - Автофильтр (Top 10 AutoFilter).
Нажмите OK. Окно Первые 10 - Автофильтр (Top 10 AutoFilter) закроется, и в столбце Р отобразятся строки с наибольшими десятью значениями. Совет. Нажатие кнопки OK в данном окне установит опцию по умолчанию - отображение первых десяти значений в выделенных ячейках.Щелкните на ячейке Р6 и перетащите указатель на ячейку Р27.
Ячейки выделятся, и в области автовычисления в правом нижнем углу окна Excel отобразится СУММ=36781.
Щелкните на ячейке Р37 и затем на панели инструментов Стандартная нажмите кнопку Автосумма
После замены "9" на "1" функция СУММ будет подсчитывать среднее значение вместо суммы. В ячейке Р37 отобразится среднее из десяти наибольших значений ячеек с Р6 по Р36 - "3678.1". Значение в ячейке Р38 также сменится на "101603.1", но этот результат включает в себя среднее значение отфильтрованных ячеек в столбце.
Щелкните на ячейке Р37 и нажмите (Enter). Excel удалит формулу СУММ из ячейки Р37, и сумма в ячейке Р38 изменится на "97925".На панели инструментов Стандартная нажмите кнопку Сохранить (Save). Excel сохранит ваши изменения.Нажмите кнопку Закрыть (Close). Документ Calculations.xls закроется.
