Форматирование рабочих листов. Excel.

Форматирование ячеек

Основные средства для форматирования ячеек расположены на панелях Щрифт, Выравнивание, Стили, Число, Ячейки, вкладки Главная, в диалоговом окне Формат ячеек, открываемой с панеди инструментов, либо из контекстного меню, мини-панели, вызываемой при нажатии на ячейку ЛКМ.
Формат по образцу

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

Форматирование с помощью стилей

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

Наложение определённого формата на ячейку при выполнении заданного условия. Выполнить по команде: Главная/Стили/Условное форматирование
Также ячейки со значениями могут быть выделены цветовыми гистограммами, шкалами(цветовыми) или значками.
Форматирование строк и столбцов

Изменение высоты строк и ширины столбцов.
Главная/Ячейки/Формат/(Высота строки/Ширина столбца)

 

Автоматизация ввода: автозавершение, автозаполнение числами, автозаполнение формулами.Excel.

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

1)Использование прогрессий. Если ячейка содержит число, дату или период времени, который может являться частью ряда, то при копировании происходит приращение её значения (получается арифметическая или геометрическая прогрессия, список дат.).

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

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

4)Использование сочетания клавишь Ctrl+Enter для ввода повторяющихся значений.
Для введения одних и тех же значений в несколько ячеек, нужно выделить их, вести значения в одну ячейку и нажать Ctrl+ Enter/

75. Характерные форматы сжатия без потери информации

Сжатие данных без потерь (англ. Lossless data compression) — метод сжатия данных: видео, аудио, графики, документов представленных в цифровом виде, при использовании которого закодированные данные могут быть восстановлены с точностью до бита. При этом оригинальные данные полностью восстанавливаются из сжатого состояния. Этот тип сжатия принципиально отличается от сжатия данных с потерями. Для каждого из типов цифровой информации, как правило, существуют свои оптимальные алгоритмы сжатия без потерь.

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

Сжатие без потерь используется, когда важна идентичность сжатых данных оригиналу. Обычный пример — исполняемые файлы и исходный код. Некоторые графические файловые форматы, такие как PNG, используют только сжатие без потерь; тогда как другие (TIFF, MNG) или GIF могут использовать сжатие как с потерями, так и без.

76. Электронная таблица Excel. Построение диаграмм и графиков. Мастер диаграмм.

Для созд. диагр. нужно нажать Диаграммы-вкладки Вставка. Затем ввести данные для её построения. После ввода данных на странице отобразится построенная диагр. при этом в ленте Главного меню появится контекстное меню работы с диагр. содерж. 3 ленты: конструктор, макет, формат.

77. Электронная таблица Excel. Типы диаграмм.

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

78. Электронная таблица Excel. Относительные ссылки.

Относительная ссылка указывает расположение нужной ячейки относительно активной (т.е. текущей). При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.

 

79. Электронная таблица Excel. Абсолютные ссылки.

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

 

80. Формулы в Excel. Примеры.

А1-относительная ссылка, $А$1-абсолютная ссылка.

81. Функции в Excel. Основные категории функции. Мастер функций.

Существуют математические, логические, статистические, текстовые и др. функции. Функции можно вводить вручную, но в Excel предусмотрен мастер функций, позволяющий вводить их в полуавтоматическом режиме и практически без ошибок. Для вызова мастера функций необходимо нажать кнопку Вставка функции на стандартной панели инструментов, выполнить команду Вставка/Функция или воспользоваться комбинацией клавиш [Shift+F3]. После этого появится диалоговое окно Мастер функций, в котором можно выбрать нужную функцию.

82. Электронная таблица Excel. Построение сводных таблиц.

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

83. Электронная таблица Excel. Создание отчетов с помощью консолидации данных.

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

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

 

84. Электронная таблица Excel. Выбор данных с помощью фильтров.

Фильтрация позвл. ввести только необходимые в данный момент строки. При этом строки не удовлетворяющие заданным условиям просто не будут отображаться на экране и из файла удаляться не будут. Главная-Редактиравание – Сортировка и фильтр – Фильтр.

85. Электронная таблица Excel. Подведение промежуточных итогов.

Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя команду Итоги в меню Данные. Но если список с промежуточными итогами уже создан, его можно модифицировать, редактируя формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Синтаксис

ПРОМЕЖУТОЧНЫЕ.ИТОГИ (номер_функции; ссылка1;ссылка2;...)

Номер_функции — это число от 1 до 11 (включая скрытые значения) или от 101 до 111 (за исключением скрытых значений), которое указывает, какую функцию использовать при вычислении итогов внутри списка.

НОМЕР_ФУНКЦИИ (ВКЛЮЧАЯ СКРЫТЫЕ ЗНАЧЕНИЯ)	НОМЕР_ФУНКЦИИ (ЗА ИСКЛЮЧЕНИЕМ СКРЫТЫХ ЗНАЧЕНИЙ)	ФУНКЦИЯ
		СРЗНАЧ
		СЧЁТ
		СЧЁТЗ
		МАКС
		МИН
		ПРОИЗВЕД
		СТАНДОТКЛОН
		СТАНДОТКЛОНП
		СУММ
		ДИСП
		ДИСПР

Ссылка1; Ссылка2; — от 1 до 29 интервалов или ссылок, для которых подводятся итоги.

Заметки

· Если уже имеются формулы подведения итогов внутри аргументов ссылка1;ссылка2;... (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

· Для диапазона констант номер_функции от 1 до 11, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ включает значения строк, скрытых при помощи команды Скрыть (меню Формат подменю Строка). Эти константы используются для получения промежуточных итогов для скрытых и не скрытых чисел списка. Для диапазона констант номер_функции от 101 до 111, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых при помощи команды Скрыть (меню Формат подменю Строка). Эти константы используются для получения промежуточных итогов только для не скрытых чисел списка.

· Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки не включенные в результат фильтра независимо от используемого значения константы номер_функции.

· Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ разработана для столбцов данных или вертикальных наборов данных. Она не предназначена для строк данных или горизонтальных наборов данных. Например, при определении промежуточных итогов горизонтального набора данных при помощи константы номер_функции 101 или большей (например так SUBTOTAL(109,B2:G2)), скрытие столбца не повлияет на результат. А скрытие строки при подведении промежуточного итога для вертикального набора данных повлияет на результат.

· Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.

86. Электронная таблица Excel. Структурирование таблиц.

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

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

Структурирование выполняется с помощью команды Данные, Группа и Структура, а затем выбирается конкретный способ — автоматический или ручной.

При ручном способе структурирования необходимо предварительно выделить область — смежные строки или столбцы. Затем вводится команда Данные, Группа и Структура, Группировать, которая вызывает окно «Группирование» для указания варианта группировки—по строкам или столбцам.

В результате создается структура таблицы (рис. 3.41) со следующими элементами слева и/или сверху на служебном поле:

линии уровней структуры, показывающие соответствующие группы иерархического уровня;

кнопка <плюс>— для раскрытия групп структурированной таблицы;

кнопка <минус> — для скрытия групп структурированной таблицы

кнопки <номера уровней 1, 2, 3> — для открытия или скрытия соответствующего уровня.

Для открытия (закрытия) определенного уровня иерархии необходимо щелкнуть на номере уровня кнопки с номерами 1, 2, 3 и т.д. Для открытия (закрытия) иерархической ветви нажимаются кнопки плюс, минус.

 

87. Электронная таблица Excel. Сортировка данных.

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

88. Электронная таблица Excel. Поиск и редактирование данных с помощью формы данных.

Форма данных обеспечивает удобный способ ввода и отображения строк данных из диапазонов или таблиц, при котором не нужна горизонтальная прокрутка. Использовать форму данных может быть проще, чем перемещаться между столбцами, когда их слишком много для одновременного отображения на экране. Форму данных можно применять, когда достаточно простой формы с текстовыми полями, в качестве подписей которых используются заголовки столбцов, и когда не требуются сложные или настраиваемые функции, например списки и счетчики. Microsoft Excel автоматически создает встроенную форму данных для диапазона или таблицы. Такая форма представляет собой диалоговое окно, в котором заголовки столбцов отображаются в виде подписей. Рядом с каждой подписью расположено пустое текстовое поле, в которое можно вводить данные для столбцов (не более 32). Форма данных позволяет вводить, искать с помощью навигации, а также (в зависимости от содержимого ячеек) обновлять и удалять строки. Если в ячейке содержится формула, то в форме данных отображается только результат ее вычисления. Саму формулу невозможно изменить с помощью формы данных.

ПРИМЕЧАНИЕ. Форму данных нельзя распечатать. Кроме того, поскольку эта форма является модальным диалоговым окном, то, пока она открыта, в Excel невозможно воспользоваться командой Печать и кнопкой Печать. При необходимости можно сделать снимок формы (с помощью клавиши Windows Print Screen) и скопировать его в Microsoft Paint или другую программу.

Создание формы данных

1. При необходимости добавьте заголовки во все столбцы диапазона или таблицы. На основе этих заголовков создаются подписи для всех полей формы.

ВАЖНО. Убедитесь, что в диапазоне данных нет пустых строк.

2. Щелкните ячейку в диапазоне или таблице, куда нужно добавить форму.

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

1. Щелкните стрелку рядом с панелью быстрого доступа и выберите пункт Другие команды.

2. В поле Выбрать команды из выберите пункт Все команды и в списке найдите кнопку Форма .

3. Нажмите кнопку Добавить, а затем — ОК.

4. На панели быстрого доступа нажмите кнопку Форма .

ВАЖНО. Если отображается сообщение «В форме данных слишком много полей», необходимо сократить число столбцов: в форме данных их должно быть не более 32. Один из способов решения этой проблемы (при работы с диапазоном столбцов) — вставить пустой столбец, разбив диапазон на два. Затем, если необходимо, создайте отдельную форму данных для столбцов справа от пустого столбца.

Использование формы данных

Форму данных можно использовать для добавления, поиска, изменения и удаления строк в диапазоне или таблице.

Добавление строки данных

1. В форме данных нажмите кнопку Добавить.

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

Переход к следующему полю выполняется с помощью клавиши TAB, а к предыдущему — с помощью сочетания SHIFT+TAB.

3. Указав нужные данные, нажмите клавишу ВВОД, чтобы сохранить изменения и добавить строку в нижнюю часть диапазона или таблицы.

ВАЖНО. Если отображается сообщение «Невозможно расширить список или базу данных», это может означать, что существующие данные будут перезаписаны при продолжении. При добавлении строк с помощью формы данных таблицы и диапазоны расширяются вниз. Если при этом могут быть перезаписаны какие-либо данные, то появляется сообщение Невозможно расширить список или базу данных. Измените расположение данных так, чтобы диапазон или таблица могли расширяться вниз от последней строки.

ПРИМЕЧАНИЕ. До нажатия клавиши ВВОД отменить любые изменения можно с помощью кнопки Вернуть на форме данных. При этом из полей удалятся все внесенные данные.

Поиск строки с помощью навигации

Выполните одно или несколько действий, указанных ниже.

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

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

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

· Чтобы перейти к предыдущей строке в диапазоне или таблице, нажмите кнопку Назад.

89.. Электронная таблица Excel. Подбор параметра. Поиск решения.

Подбор параметра – способ поиска определенного значения ячейки

путем изменения значения в другой ячейке. При подборе параметра при-

ложение изменяет значение в одной конкретной в ячейке до тех пор, пока

формула, зависящая от этой ячейки, не вернет требуемый результат.

Команда Подбор параметра находит только одно решение, даже ес-

ли задача имеет несколько решений, и только для одной ячейки.

ИСПОЛЬЗОВАНИЕ ИНСТРУМЕНТА «ПОДБОР ПАРАМЕТРОВ»

1. Cоставить таблицу, отвечающую требованиям:

 ячейка, в которой должен быть получен желаемый результат (це-

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

наиболее близким к тому, которое требуется получить;

 ячейка, в которой должно быть выведено искомое значение,

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

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

исходным для формулы, находящейся в целевой ячейке.

2. Применить инструмент Подбор параметра.

3. В появившемся диалоговом окне Подбор параметра заполнить

пустые поля.

Поиск решения(надстройка)

Microsoft Office 2007:

1. Щелкнуть по кнопке Microsoft Office, выбрать Параметры Excel,

а затем выбрать категорию Надстройки.

2. В поле Управление выбрать Надстройки Excel и нажать Перейти.

3. В поле Доступные надстройки установить флажок рядом с пунк-

том Поиск решения и нажать ОК

 

Постановка задачи и оптимизация модели

1. Microsoft Office 2007: на ленте Данные в группе Анализ щелкните

Поиск решения.

Microsoft Office ‘97-2003: команда Сервис \ Поиск решения.

2. В появившемся диалоговом окне (рис. 3.1) в поле Установить це-

левую ячейку ввести ссылку на ячейку или имя конечной ячейки, которая

должна содержать формулу. 23

Рис. 3.1. Диалоговое окно инструмента Поиск решения

3. В поле Равной установить переключатель в одно из положений:

 максимальному значению – чтобы максимизировать значение ко-

нечной ячейки путем изменения значений влияющих ячеек;

 минимальному значению – чтобы минимизировать значение ко-

нечной ячейки путем изменения значений влияющих ячеек;

 значению – чтобы установить значение в конечной ячейке рав-

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

4. В поле Изменяя ячейки ввести имена или ссылки на изменяемые

ячейки, разделяя их точкой с запятой. Изменяемые ячейки должны быть

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

до 200 изменяемых ячеек.

Примечание. Чтобы автоматически найти все ячейки, влияющие на

формулу модели, надо нажать кнопку Предположить.

5. В поле Ограничения ввести все ограничения, накладываемые на

поиск решения.

6. Перейти в диалоговое окно Параметры (рис. 3.3). Здесь можно

установить флажки Линейная модель и Неотрицательные значения.

7. Выбрать кнопку ОК.

8. В диалоговом окне Поиск решения (рис. 3.1) нажать кнопку Вы-

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

(рис. 3.4) выбрать либо Сохранить найденное решение, чтобы установить

найденное решение на листе, либо Восстановить исходные значения, что-

бы вернуть исходные данные.

В поле Тип отчета выбрать тип отчета (Результаты, Устойчи-

вость, Пределы), чтобы создать отчет, основанный на найденном

решении (если решение не найдено, данные параметры будут не-

доступны), а затем нажать кнопку ОК.

 Сохранить сценарий, чтобы сохранить значения изменяющейся

ячейки в качестве сценария, который можно будет отобразить

позже. В появившемся диалоговом окне в поле Название сцена-

рия ввести имя для этого сценария.

Чтобы просмотреть, изменить или выполнить сценарий, надо:

Microsoft Office 2007: на ленте Данные в группе Работа с дан-

ными выберите команду Анализ «что-если», а затем выбрать в

списке пункт Диспетчер сценариев.

Microsoft Office ‘97-2003: команда Сервис / Сценарии.

 

3.1.2. Изменение способа поиска решения

1. В диалоговом окне Параметры поиска решения (рис. 3.3) задать

один или несколько параметров:

 в поле Максимальное время задается время в секундах, отведен-

ное на поиск решения;

 в поле Предельное число итераций задается максимальное коли-

чество итераций (повторений), отводимое на достижение конеч-

ного результата;

 в поле Относительная погрешность задается необходимая по-

грешность - чем меньше значение, тем выше точность результата;

 в поле Допустимое отклонение задается значение допустимого

отклонения в процентах;

 в поле Сходимость задается значение относительного изменения,

при достижении которого в последних пяти итерациях поиск ре-

шения прекращается - чем меньше значение, тем выше точность.

2. Нажать ОК.

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

Закрыть.

Примечание. Чтобы восстановить параметры по умолчанию в диа-

логовом окне Поиск решения выбрать кнопку Восстановить.

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

1. Настроить лист электронных таблиц для решения задачи.

2. Вызвать инструмент Поиск решения.

Microsoft Office 2007: на ленте Данные в группе Анализ щелкните

Поиск решения.

Microsoft Office ‘97-2003: команда Сервис \ Поиск решения.26

3. Чтобы просмотреть значения всех найденных решений, в диало-

говом окне Параметры поиска решения (рис. 3.3) установить флажок По-

казывать результаты итераций, а затем нажать кнопку ОК.

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

5. В диалоговом окне Текущее состояние поиска решения нажать

одну из кнопок: Стоп, чтобы остановить поиск решения и вывести на

экран диалоговое окно Результаты поиска решения; Продолжить, чтобы

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

3.1.4. Сохранение и загрузка модели оптимизации

Значения элементов управления диалогового окна Поиск решения

записываются на лист при сохранении. Чтобы использовать на листе не-

сколько моделей оптимизации, нужно сохранить их с помощью команды

Сохранить модель. Чтобы загрузить модель, необходимо сначала сохра-

нить хотя бы одну модель.

90. Электронная таблица Excel. Мастер текстов. Копирование списка из Word в Excel.

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

2 Выполнить команду Правка –»Копировать

3 Перейти на нужный лист книги Excel и выполнить команду Правка –» Вставить