Скрытие строк, столбцов, листов и книг

Лабораторная работа №8

­­ Тема: Скрытие и защита данных. Команда Подбор параметра.

Скрытие строк, столбцов, листов и книг

Иногда бывает необходимо скрыть информацию в рабочем листе. Excel позволяет скрывать целые столбцы или строки. Чтобы скрыть столбец, сначала выделите ячейку в этом столбце. На вкладке Главная в группе Ячейки в подменю Формат выберите команду Скрыть или отобразить и затем Скрыть столбцы. После выбора команды программа установит ширину выделенного столбца равной 0.

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

С тем же результатом можно ввести 0 в окно диалога Ширина столбца. Когда ширина столбца установлена равной 0, Excel пропускает этот столбец при перемещении активной ячейки, а буква заголовка столбца не отображается в верхней части листа. Например, если вы сократите столбец C, то останутся видны заголовки столбцов A, B и D и т.д.

Чтобы снова вывести столбец в рабочем листе, сначала выделите столбцы, расположенные слева и справа от скрытого столбца. После этого в меню Формат выберите команду Скрыть или отобразить и затем – Отобразить столбцы. Так же вы можете выделить заголовки столбцов, расположенных слева и справа от скрытого столбца, затем, щелкнув правой кнопкой мыши, выбрать опцию Отобразить.

Рис. 8.1

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

 

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

Чтобы скрыть лист книги, выполните нижеописанные команды:

1. Щелкните на ярлычке листа, который хотите скрыть.

2. В меню Формат выберите команду Скрыть или отобразить, а затем – Скрыть лист.

ИЛИ

Щелкните правой кнопкой мыши на ярлычке листа, который хотите скрыть и выберете команду Скрыть.

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

Режим скрытия формул

Если к ячейкам применен режим скрытия формул, то при активизации таких ячеек содержащихся в них формул не выводятся в строке формул. Сами формулы в этих ячейках по-прежнему сохраняются, они просто недоступны для просмотра, а результат вычислений видим, если только вы не используете «скрытый» числовой формат.

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

1. Выделите ячейки, которые хотите скрыть.

2. Щелкните правой кнопкой мыши по последней из выделенных ячеек.

3. В появившемся окне выберите пункт контекстного меню Формат ячеек.

4. В появившемся окне выберите вкладку Защита.

5. Установите флажок Скрыть формулу и нажмите кнопку ОК.

6. Перейдите на вкладку Рецензирование и в группе Изменения нажмите на кнопку Защитить лист.

7. Можно выбрать необходимые опции и дважды ввести пароль.

8. В появившемся окне нажмите на кнопку ОК.

 

Защита листов, книг, данных.

Защита книг и листов

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

1. Перейти на вкладку Рецензирование.

2. В группе Изменения выберете в зависимости от задачи Защитить лист или Защитить книгу.

3. В появившемся окне диалога выберете необходимые параметры защиты и нажмите кнопку ОК.

 

Ввод паролей

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

1. В группе Изменения выберете в зависимости от задачи Защитить лист или Защитить книгу.

2. Введите пароль и нажмите кнопку ОК.

3. После запроса Excel подтвердите пароль, для чего введите его повторно, и нажмите кнопку ОК для возвращения в окно книги.

Рис. 8.2

Рис. 8.3

Удаление защиты

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

Если при установке защиты листа или книги вы назначили пароль, то не сможете снять защиту без ввода этого пароля. При вводе неправильного пароля Excel выведет сообщение: Неверный пароль. Убедитесь, что отключена клавиша CAPS LOCK и используется правильный регистр. Вводимый текст должен совпадать с назначенным паролем во всех деталях, включая регистр букв.

 

 

Защита данных

По умолчанию Excel блокирует (защищает) ячейки, графические объекты, диаграммы и т.д. Для включения этой защиты необходимо выбрать вкладку Рецензирование, далее выбрать вкладку Изменения и выбрать Защитить лист. Там же можно задать пароль для защиты, а также ограничить допустимые действия над документом. После включения защиты изменить заблокированный элемент (в зависимости от заданных Вами параметров защиты) невозможно.

При попытке изменить заблокированную ячейку Excel выведет общение «Ячейка или диаграмма защищена от изменений».

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

1. Выделите объекты, которые вы хотите защитить.

2. На вкладке Главная в группе Ячейки щелкните Формат, а затем выберите команду Формат ячеек.

3. На вкладке Защита снимите флажок Защищаемая ячейка и нажмите кнопку OK.

4. На вкладке Рецензирование выберите Защитить лист.

Рис. 8.4

 

 

Команда Подбор параметра

Если известен результат, который требуется вычислить при помощи формулы, но неизвестны значения, которые необходимо ввести для получения этого результата, можно воспользоваться средством «Подбор параметра». С его помощью можно, например, изменять процентную ставку в ячейке B3 до тех пор, пока размер платежа в ячейке B4 не станет равен 900,00 руб.

Рис. 8.5

 

 

Например, предположим, что вы хотите узнать какую максимальную ссуду под закладную можно взять на 30 лет при процентной ставке 6,5 процента и условии, что месячные выплаты не будут превышать $2000 (рис.1). Чтобы использовать команду Подбор параметра для получения ответа на этот вопрос выполните описания, которые приводятся ниже:

1. Решите задачу с каким-нибудь начальным значением параметра. Например, в задаче о максимальной ссуде при размере ссуды в $500 000 месячные выплаты превышают $2000.

2. Выделите ячейку с формулой, в данном случае B4.

3. На вкладке Данные в группе Средства обработки данных выберите команду Анализ «что-если», а затем выберите в списке пункт Подбор параметра.

4. В окне диалога Подбор параметра оставьте без изменений значение в поле Установить в ячейке (там должна содержаться абсолютная ссылка на активную ячейку).

5. В поле Значение введите максимальное значение, которое вы хотите получить в качестве результата формулы, в данном случае – 2000.(Введите число со знаком «минус», потому что вы будите выплачивать деньги, а не получать).

6. В поле Изменяя значения ячейки введите ссылку или щелкните в листе на ячейке, значения которой является неизвестным. В данном случае это ячейка В1. (если ячейке В1 назначено имя, например Ссуда, в поле Изменяя значение ячейки можно ввести это имя.)

 

Рис. 8.6

7. Нажмите кнопку ОК или клавишу Enter, Excel выведет окно диалога Результат подбора параметра. Ответ на наш вопрос находиться в ячейке, заданной ранее в поле Изменяя значения ячейки. В данном примере результат 316422,64 появляется в ячейке В1 (рис.7).

8. Чтобы сохранить это значение, нажмите кнопку ОК в окне диалога Результат подбора параметра. Для восстановления значения, которое было в ячейке В1 до использования команды Подбор параметра, нажмите кнопку Отмена.

Рис. 8.7

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

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

 

 

Практические задания:

1. Рабочему листу дайте имя «Параметры параллелепипеда»

2. Дан прямоугольный параллелепипед с рёбрами x, y, z.

3. В ячейки A1, A2, A3, A5, A7, A9, A11 введите соответственно тексты x, y, z, V, S, d, Vш.

4. Ячейкам B1, B2, B3, B9 дайте имена x, y, z, d.

5. В ячейки B1, B2, B3 введите значения x, y, z.

Вариант	x	y	z

 

6. В ячейке B5 вычислите объём данного параллелепипеда по формуле V=xyz.

7. В ячейке B7 вычислите площадь поверхности данного параллелепипеда по формуле S=2(xy+yz+xz).

8. В ячейке B9 вычислите длину диагонали данного параллелепипеда по формуле d=КОРЕНЬ(x^2+y^2+z^2).

9. В ячейке B11 вычислите объем шара, диаметром которого является диагональ данного параллелепипеда, по формуле Vш =(d^3)/6.

10. Скопируйте лист дважды. Первой копии дайте имя «защита», а второй-«Подбор параметра».

11. Вернитесь к листу «Параметры параллелепипеда»: скройте 7 и 8 строки. Скройте формулы в ячейках B5 и B11. Скройте этот лист.

12. Перейдите на лист «Защита»: создайте на нём какой-либо объект WordArt. Защитите весь этот лист, кроме объекта WordArt, столбца A и ячейки B9 (введите пароль)ю

13. Перейдите к листу «Подбор параметра»: вычислите длину ребра y по известному d. Вычислите длину ребра x по известному S.

14. Защитите структуру текущей книги (без ввода пароля).

Вариант	S

Вариант	d

 

 

Лабораторная работа №9

Тема: Диаграммы в Excel

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

 

Основы построения диаграмм:

Мастер диаграмм – это средство построения диаграмм, настройки их параметров и установок.

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

Данные для диаграммы не обязательно должны находиться в одном смежном диапазоне. Чтобы выделить несколько, нажмите клавишу <Ctrl> и щёлкните на нужных ячейках. В этом случае Excel будет использовать для построения диаграммы только выделенные ячейки.

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

 

Вразделе Диаграммы на панели инструментов будут представлены Стандартные типы диаграмм (Гистограмма, График, Круговая и т.п.) (Рис. 9.1.).

Рис. 9.1.

 

Также в разделе Диаграммы можно выбрать помимо Стандартных типов различные специфические типы диаграмм. Для этого надо щёлкнуть на кнопке Другие диаграммы. Чтобы получить полный обзор всех типов диаграмм, надо в разделе Диаграммы щёлкнуть в нижнем правом углу стрелочку (Создать диаграмму). Появиться окно Изменение типа диаграммы (или «Вставка диаграммы») ( Рис.9.2 ), в котором и будут представлены все возможные типы диаграмм.

Рис.9.2

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

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

 

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

Рис.9.3

 

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

Ø Группа Подписи включает в себя несколько элементов.

o Название диаграммы отвечает за добавление заголовков к диаграмме и дополнительные параметры заголовков.

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

o Легенда. Здесь можно указать отображать ли легенду, и если да, то куда её поместить.

o Подписи данных. Добавление, удаление или размещение подписей данных. Позволяют обозначить элементы диаграммы с помощью реальных значений данных.

o Таблица данных. Добавление таблицы данных к диаграмме.

 

Ø Оси:

o Вкладка Оси. Изменение форматирования и разметки каждой оси.

o Сетка. Включение или отключение линий сетки, определение их параметров.

Ø Фон:

o Область построения диаграммы.

 

Ø Анализ.

Ø Свойства.

Рис.9.4

 

Размещение диаграммы

 

Конструктор => Расположение => Переместить диаграмму (Рис.9.5)

Рис.9.5

 

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

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

 

Настройка диаграмм

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

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

Ø Изменить любые цвета, вид, толщину линий, стиль маркеров и шрифты.

Ø Изменить диапазоны данных, которые используются для построения диаграммы. Добавить новые ряды данных или удалить существующие.

Ø Изменить параметры линии сетки лили удалить её.

Ø Определить размер и расположение легенды (или удалить её вообще).

Ø Изменить место пересечения осей.

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

Ø Добавить заголовки к осям и диаграмме, а также поместить любой текст в любое место диаграммы.

Ø Добавить линии трена к наборам данных.

Ø Отобразить точки данных в обратном порядке.

Ø Повернуть объёмные диаграммы в пространстве.

Ø Заменить маркеры данных рисунками (растровыми изображениями).

Если диаграмма активизирована, то для обработки можно выделить различные её части. Модификация диаграммы – процесс похожий на любое другое действие в Excel. Сначала вы выделяете объект, а затем создаёте команду. Все инструменты для изменения диаграмм расположены на вкладках Макет и Формат. (Рис.9.6).

Рис.9.6

Заголовки диаграммы

В диаграмме используются следующие заголовки:

 

o Заголовок/Название диаграммы.

o Заголовок/Название основной горизонтальной оси.

o Заголовок/Название основной вертикальной оси.

Количество заголовков зависит от типа диаграммы, например, в круговой диаграмме присутствует только заголовок диаграммы, потому что в ней нет осей.

Чтобы добавить название к диаграмме необходимо В группе Подписи на вкладке Макет В группе вкладок Работа с диаграммами щёлкнуть кнопку Название диаграммы. Для изменения внешнего вида и некоторых параметров названия необходимо щелкнуть по кнопке Дополнительные параметры заголовков, или, нажав правую кнопку мыши (на названии диаграммы), выбрать пункт контекстного меню Формат названия диаграммы, или использовать инструменты на вкладке Формат.

 

Легенда диаграммы

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

За добавление, удаление и размещение легенды в диаграмме отвечает кнопка во вкладке Макет в разделе Подписи. Там же есть дополнительные параметры, которые помогаю внести изменения во внешний вид легенды и её положение.

Чтобы удалить легенду, выделите её и нажмите клавишу <Delete>. Если нужно передвинуть легенду, щёлкните по ней и перетащите в нужное место.

Можно форматировать определённые параметры легенды. Для этого нужно щёлкнуть по ней правой клавишей мыши и выбрать Формат легенды или одну из нужных опций.

Если вы не включили текст для легенды, когда первоначально выделяли ячейки для построения диаграммы, Excel будет использовать фиктивные имена Ряд1, Ряд2.

 

Работа с рядами данных

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

Прежде чем приступить к работе с данными, их необходимо выделить. Активизируйте диаграмму, а затем щёлкните на нужном наборе данных в диаграмме, чтобы его выделить. В гистограмме это столбец, в графике - линия и т.д. Убедитесь в том, что вы выбрали ряд, а не отдельную точку. Все виды настройки ряда осуществляются с помощью диалогового окна Формат ряда данных, которое открывается по нажатию правой кнопкой мыши и включает в себя вкладки Параметры ряда, Заливка, Цвет границы, Стили границы, Тень, Формат объёмной фигуры.

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

 

 

Удаление ряда данных

Чтобы удалить ряд данных, выделите его и нажмите клавишу <Delete>. Ряд данных будет удалён из диаграммы. Конечно, данные на рабочем листе останутся в неизменном виде.

Изменение ряда данных

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

Рис.9.8

 

Редактирование формулы ряда

С каждым набором данных в диаграмме связана определённая формула. Можно непосредственно исправить ссылки на диапазон в формуле.

В описываемой формуле используется функция РЯД с четырьмя аргументами:

=РЯД (имя ряда; имя категории; диапазон данных; номер ряда)

 

Рис.9.9

 

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

Добавление подписей данных

Иногда необходимо, чтобы диаграмма показывала значение данных в каждой точке. Подписи данных задаются во вкладке Макет → Подписи данных. Для каждого типа диаграммы можно установить определённые опции.

 

Рис.9.10

 

Отображение скрытых данных

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

Изменение диаграммы.

Для того чтобы изменить макет диаграммы, следуйте следующим указаниям:

1. Выберите диаграмму. Excel добавит к ленте новый раздел Работа с диаграммами.
2. Выберите Конструктор → Макеты диаграмм, а затем щелкните по маленькой кнопке Дополнительные параметры (в нижнем правом углу этой группы). Приложение Excel отобразит панель, содержащую все макеты, доступные для выбранного типа диаграммы (рис.12)

 

Рис.9.12

 

1. Щелкните по нужному макету. Excel закроет панель Макеты диаграмм и применит к диаграмме выбранной макет.

Типы диаграмм Excel

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

В качестве справочной информации в этом разделе приведены типы диаграмм Excel.

Гистограммы

Самый распространённый тип диаграмм. Они удобны для отображения дискретных данных, которые являются противоположностью непрерывным данным. Можно применять любое количество рядов данных, столбцы могут располагаться стопками:

 

Рис.9.13

Линейчатые диаграммы

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

Рис.9.14

Графики

Один из самых распространённых типов диаграмм. Они часто применяются для отображения непрерывных данных.

 

Рис.9.15

Круговые диаграммы

Её используют для показа пропорций или части чего-либо целого.

Рис.9.16

 

Точечная диаграмма

Другое их название – диаграммы рассеивания. Их основное отличие в том, что по обеим осям такой диаграммы откладываются значения:

 

Рис.9.17

 

Практические задания:

1. Введите значения в три столбца. Первый столбец должен содержать номер года. Например, начиная с 1970 и по 1980. (Значений должно быть не меньше десяти). Второй столбец должен содержать значения прибыли соответственно году. Третий – значение расхода.

2. Постройте гистограмму зависимости прихода и расхода от года. По оси Х должны откладываться годы, а по Y- работы. Линий сетки быть не должно, над столбиками должны быть числовые значения из таблицы. Диаграмму поместите на отдельном листе.

3. Для зависимости прихода и расхода от года постройте диаграмму в виде графика. Поменяйте форму и цвет маркеров.

4. Постройте круговую диаграмму год-приход.

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

 

 

 

 

 

 

 

 

 

 

 

Лабораторная работа № 10

Тема: Адресация

Практически все формулы включают ссылки на ячейки или диапазоны ячеек. Эти ссылки позволяют работать с данными, содержащимися в этих ячейках и диапазонах, а не просто использовать фиксированные значения. Если формула имеет ссылку на ячейку А1, и вы изменили значение в этой ячейке, формула автоматически пересчитается в соответствии с новым значением ячейки А1. Если не использовать ссылок на ячейки, вам придется вручную редактировать формулы, изменяя значения, которые она использует в своих вычислениях. В формулах используются три типа ссылок на ячейки и диапазоны.

 

 

Изменение типа ссылок

 

 

Неотносительные (т. е. абсолютные и смешанные) ссылки можно ввести вручную, вставив в нужных местах знаки доллара. Можно воспользоваться удобным клавиатурным эквивалентом – клавишей F4. При вводе ссылки в ячейку – либо вручную, либо путем указания – нужно нажать клавишу F4 несколько раз, чтобы программа “прокрутила” по циклу все четыре типа ссылок.

Например, если в начале формулы вы вводите =А1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $A$1, второе – в А$1, третье – в $А1, а четвертое вернет ей первоначальный вид – А1. Нажмите клавишу F4 до тех пор, пока не появится нужный тип ссылки.

Когда вы даете имя ячейке или диапазону, то по умолчанию используется абсолютная ссылка на это имя. Например, если вы присвоили диапазону А1:А12 имя Прогноз_Продаж, то в поле Формула диалогового окна Присвоение имени ссылка на этот диапазон будет выглядеть так: $A$1:$A$12. Почти всегда это и нужно пользователю. Таким образом, если скопировать формулу, в которой содержится поименованная ссылка, то в скопированной формуле эта ссылка останется без изменений.

 

 

ЛАБОРАТОРНАЯ РАБОТА №11

Рис.11.1

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

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

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

2. На вкладке Главная в группе Редактирование необходимо выбрать команду Очистить форматы.

3. Чтобы удалить также и значения в ячейках, выберите команду Очистить все.

Рис.11.2

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

 

Применение автоформата

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

1. Выполните выделение диапазона ячеек. Затем выберите ГлавнаяÞСтилиÞСтили ячеек.

2. В появившемся окне выберите нужный формат.

Рис.11.3

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

Рис.11.4

Общий формат

Формат Общий -первая категория в списке Числовые форматы. Любое значение, введенное в ячейку, Excel отобразит в этом формате, если вы не внесете явных изменений. Этот формат отображает точно то, что вы ввели за редким исключением.

1. Длинные значения выводятся в экспоненциальной записи или округляются.

2. Этот формат не отображает не значащие нули.

3. Десятичная дробь, введенная без числа слева от десятичной запятой, выводится с нулем.

 

Дробные форматы

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

 

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

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

Таблица 11.1

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

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

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

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

 

Например: «Прибыль:»_-$# ##0,00; «Убыток:»- $# ##0,00; «Чистый счет.»; «Ошибка:»@

 

Введенное значение	Отображаемое значение
34,78	Прибыль: $34,78
-34,78	Убыток: -$34,78
	Чистый счет
Это текст	Ошибка: Это текст

 

Включение цвета в форматы

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

 

Например: [Красный]; [Синий]; [Зеленый]; [Желтый]

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

 

Можно задать цвет так: ЦВЕТn, где n – это число от 1 до 16.

 

Использование условий в пользовательских форматах

Вы можете также управлять отображением чисел в зависимости от выполнения условий сравнения. Для этого добавьте условия в первые две секции пользовательского формата, которые обычно предназначены для вывода положительных и отрицательных значений. Третья секция при этом становится используемым по умолчанию форматом для значений, которые не удовлетворяют первым двум условиям. При этом можно использовать операторы сравнения <, >, =, <=, >=, < >.

 

Например: [зеленый][>200]_-$###0,00;[красный][<0]-$# ##0,00

Зеленым Excel выведет сумму, превышающую 200, а красным отрицательные числа.

 

Условное форматирование

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