Для Чего Нужен Инструмент ms Excel Под Названием Таблица Подстановки

Абсолютные ссылки/адреса — ссылки, всегда указывающие на одни и те же ячейки рабочего листа. При копировании и перемещении формул с абсолютными ссылками в формулах используется содержимое прежних ячеек. В записи абсолютных ссылок заголовкам столбцов и номерам строк предшествует значок доллара ($). (См. также Стиль ссылок.)

Автоматическое обновление — способ обмена данными, при котором изменения данных сразу отражаются во всех связанных документах (см. Связи>.

Адрес ячейки — сочетание буквы, обозначающей столбец, и номера строки. Например, ячейка в столбце А и первой строке обозначается А1. Ячейка правее обозначается В1. Ячейки под А1, В1 имеют ссылки/адреса А2,В2.

Аргумент — значение, необходимое для вычисления по формуле.

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

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

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

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

Буфер обмена — область памяти, в которую временно помещается скопированный или вырезанный текст или графический объект. При вырезании или копировании содержимого ячеек рабочего листа в строке состояния появляется сообщение “Удаление выделенных ячеек в буфер обмена” или “Копирование выделенных ячеек в буфер обмена” соответственно.

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

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

Внешняя ссылка — ссылка на другой рабочий лист Мiсгоsoft Ехсеl. Внешняя ссылка может быть на одну ячейку или на блок ячеек. В формуле ссылки могут быть указаны имена блоков ячеек.

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

Графический объект — линия или фигура (кнопка, текстовое поле, овал, прямоугольник, дуга окружности или рисунок), нарисованные с помощью панели инструментов или вставленные в Microsoft Ехсеl из других приложений.

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

Зависимый лист — рабочий лист, содержащий формулу связи (внешней ссылки или удаленной ссылки). Формулы внешней ссылки используются для связи двух рабочих листов, а формулы удаленной ссылки применяются для связи листов Мiсгоsoft Ехсеl с документами, созданными в других программах.

Запись — строка базы данных (списка). Первая строка содержит поля списка, а остальные строки являются записями. Все записи списка состоят из одинаковых категорий или полей.

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

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

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

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

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

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

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

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

Лист-источник — рабочий лист Мicrosoft Ехсеl, на который ссылается формула внешних ссылок или формула удаленной ссылки. На листе-источнике находятся данные, которые с помощью формулы внешней ссылки или удаленной ссылки помещаются в ячейки зависимого листа.

Личная книга макросов — скрытая книга, которая открывается при каждом запуске Microsoft Ехсеl. Записав макрос, вы можете сохранить его в личной книге макросов.

Макрос — последовательность команд, записанных на листе модуля. С помощью макроса вы можете избежать многократного повторения одинаковых команд. Чтобы ускорить доступ к макросу, ему можно назначить кнопку, объект или команду в меню Сервис.

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

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

Область окна — участки рабочего листа, на которые разбито окно документа. Закрепив разбиение областей, вы можете прокручивать рабочий лист с данными, и при этом одновременно видеть данные и заголовки рабочего листа, независимо от того, как далеко вы прокрутили лист.

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

Обновление — изменение данных сводной таблицы в соответствии с данными листа-источника.

Операторы сравнения — в Microsoft Excel имеются шесть стандартных операторов сравнения: Выход

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

Относительные ссылки/адреса — ссылки/адреса ячеек, указывающие положение ячейки рабочего листа относительно текущей ячейки (см. также Абсолютные ссылки/адреса).

Перемещаемая панель инструментов — панель инструментов со строкой заголовка, не закрепленная у границ окна документа.

Подбор параметра — инструмент Microsoft Ехсеl, с помощью которого, введя фиксированное значение и указав адреса изменяемых ячеек, вы можете найти значение изменяемых ячеек (параметры), при котором достигается это фиксированное значение.

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

Поле имени — поле со стрелкой “вниз” слева в строке формул (см. Строка формул). С помощью поля имени вы можете присваивать имена ячейкам рабочего листа, а также выделять ячейки и блоки ячеек по имени.

Примечания к ячейкам — текст, поясняющий содержащиеся в ячейке данные.

Рабочая книга — файл, в котором хранятся документы, созданные в Microsoft Ехсеl. Рабочая книга может содержать несколько рабочих листов, а также листов модулей и диаграмм.

Рабочий лист — таблица, пересечения столбцов и строк которой образуют ячейки. В ячейки рабочего листа Microsoft Ехсеl можно вводить и обрабатывать данные. Если рабочая книга содержит более одного рабочего листа, то переходить между листами рабочей книги можно, щелкая мышью на их ярлычках.

Ряд данных — данные, отображаемые на диаграмме. Каждый элемент данных состоит из категории и значения. На диаграмме могут быть представлены один или несколько рядов данных.

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

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

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

Стиль ссылок — способ описания адресов ячеек рабочего листа. В Microsoft — Ехсеl существуют два стиля ссылок: А1 и К1С1.В стиле А1 столбцы озаглавлены буквами, а строки — цифрами. В стиле К1С1 столбцы и строки озаглавлены цифрами.

Столбец — расположенные друг под другом ячейки. Каждый столбец озаглавлен буквой или сочетанием букв. (Например В, , АВ).

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

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

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

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

Текстовое поле — графический элемент на рабочем листе или диаграмме. С помощью текстового поля можно пометить или прокомментировать любую часть рабочего листа или диаграммы.

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

Текущая ячейка — выделенная ячейка рабочего листа, отличить которую можно по “бегущей” рамке вокруг нее. Для того чтобы ввести или отредактировать данные в ячейке, ее необходимо выделить.

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

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

Формула — комбинация чисел, ссылок/адресов ячеек, имен, функций и арифметических операторов. Формулы в ячейках рабочего листа вычисляют значение, используя содержимое других ячеек. (В Microsoft Excel есть формулы, например, СЕГОДНЯ(), не требующие дополнительных аргументов.) Формулы всегда начинаются со знака равенства (=).

Формула связи — формула в ячейке рабочего листа, содержащая ссылки/ адреса ячейки, блока ячеек или имя блока ячеек другого рабочего листа. Все связи в Microsoft Excel создаются с помощью формул связи (см. Связи).

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

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

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

Ячейка ввода — ячейка, в которую подставляются значения таблицы подстановки данных.

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

Функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет старый текст на новый текст внутри текстовой строки. Функция заменит все повторения старого текста, пока не будет выполнено определённое условие. Она чувствительна к регистру.

Текстовые функции Excel. Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Все, вызываем функцию ВПР. Щелкаем в той ячейке, куда будет подставляться цена( С5 нашего примера), далее жмем значок fx на панели инструментов (либо Вставка-функция) и в открывшемся окошке выбираем ссылки и массивы и далее ВПР. Как показано на картинке.

Справочник в EXCEL

Создадим Справочник на примере заполнения накладной.

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

Таблица Товары

Эту таблицу создадим на листе Товары с помощью меню Вставка/ Таблицы/ Таблица , т.е. в формате EXCEL 2007 (см. файл примера ). По умолчанию новой таблице EXCEL присвоит стандартное имя Таблица1 . Измените его на имя Товары , например, через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен )

К таблице Товары , как к справочной таблице, предъявляется одно жесткое требование: наличие поля с неповторяющимися значениями. Это поле называется ключевым . В нашем случае, ключевым будет поле, содержащее наименования Товара. Именно по этому полю будут выбираться остальные значения из справочной таблицы для подстановки в накладную.

Для гарантированного обеспечения уникальности наименований товаров используем Проверку данных ( Данные/ Работа с данными/ Проверка данных ):

• выделим диапазон А2:А9 на листе Товары ;
• вызовем Проверку данных ;
• в поле Тип данных выберем Другой и введем формулу, проверяющую вводимое значение на уникальность:

Через меню Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные можно получить информацию о наличии данных, которые были введены с нарушением требований Проверки данных .

Для контроля уникальности также можно использовать Условное форматирование (см. статью Выделение повторяющихся значений ).

Теперь, создадим Именованный диапазон Список_Товаров, содержащий все наименования товаров :

• выделите диапазон А2:А9 ;
• вызовите меню Формулы/ Определенные имена/ Присвоить имя
• в поле Имя введите Список_Товаров ;
• убедитесь, что в поле Диапазон введена формула =Товары[Наименование]
• нажмите ОК.

Таблица Накладная

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

• выделите диапазон C4:C14 ;
• вызовите Проверку данных ;
• в поле Тип данных выберите Список;
• в качестве формулы введите ссылку на ранее созданный Именованный диапазон Список_товаров , т.е. =Список_Товаров .

Теперь товары в накладной можно будет вводить только из таблицы Товары .

Теперь заполним формулами столбцы накладной Ед.изм., Цена и НДС . Для этого используем функцию ВПР() :

Преимущество этой формулы перед функцией ВПР() состоит в том, что ключевой столбец Наименование в таблице Товары не обязан быть самым левым в таблице, как в случае использования ВПР() .

В столбцах Цена и НДС введите соответственно формулы: =ЕСЛИОШИБКА(ВПР(C4;Товары;3;ЛОЖЬ);»») =ЕСЛИОШИБКА(ВПР(C4;Товары;4;ЛОЖЬ);»»)

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

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

Таблицы подстановки данных — МегаЛекции

• выделите диапазон А2:А9 ;
• вызовите меню Формулы/ Определенные имена/ Присвоить имя
• в поле Имя введите Список_Товаров ;
• убедитесь, что в поле Диапазон введена формула =Товары[Наименование]
• нажмите ОК.

Если случайные величины независимы (independent), то их ковариация равна 0, и, следовательно, Var(Х+Y)=Var(Х)+Var(Y). Это свойство дисперсии используется при выводе стандартной ошибки среднего .

Как настроить книгу Excel, используемую по умолчанию

Изменение заданных по умолчанию значений в диалоговом окне Параметры Excel

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

Рис. 15.1. Раздел Общие диалогового окна Параметры Excel, используемый для изменения настроек новых книг

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

Создание шаблона книга.xltx

Чтобы установить параметры новых книг, которые не могут быть указаны в диалоговом окне Параметры Excel, необходимо создать специальный файл шаблона с именем книга.xltx, а затем сохранить его в правильном месте на жестком диске.

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

• Количество листов. Добавьте или удалите рабочие листы или даже измените их названия.
• Стили. Используйте галерею стилей (расположена в группе Стили вкладки Главная) для настройки стилей. По умолчанию для всех ячеек установлен стиль Обычный, поэтому, чтобы изменить шрифт но умолчанию (включая цвет заливки, формат чисел, размер и вид рамки), нужно изменить данный стиль. Для этого щелкните правой кнопкой мыши на его имени в галерее стилей и выберите в контекстном меню команду Изменить. Затем внесите необходимые изменения в диалоговое окно Стиль.
• Параметры печати. Измените эти параметры на вкладке Разметка страницы. Например, вы можете включить колонтитулы, установить размер полей.
• Ширина столбцов. Если вам не нравится ширина столбцов, заданная по умолчанию, измените ее.
• Графика. Вставьте один или несколько графических объектов, например логотип вашей компании или фотографию вашей кошки.

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

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

1. Из раскрывающегося списка Тип файла выберите формат Шаблон Excel (*.xltx). Если шаблон содержит макросы VBA, выберите Шаблон Excel с поддержкой макросов (*.xltm).
2. В поле Имя файла введите книга.xltx (или книга.xltm, если в нем есть макросы).
3. Сохраните файл в папке XLStart.

Excel предлагает сохранить файл в папке Templates, но он должен быть сохранен именно в папке XLStart.

Папка XLStart может располагаться где угодно. Чтобы определить ее местоположение, нажмите Alt+Fll для активизации VBEditor. Затем нажмите Ctrl+G, чтобы открыть окно Immediate, введите следующее выражение и нажмите клавишу Enter:
? Application.StartupPath .

Закройте сохраненный файл. Теперь каждый раз при запуске программы новая пустая рабочая книга будет использовать созданный вами шаблон. Кроме того, при нажатии Ctrl+N будет создана новая книга на основании вашего шаблона.

Если вам когда-нибудь понадобится использовать книгу с установками Excel по умолчанию, выберите команду Файл ► Создать, а затем укажите пункт Новая книга.

Размерность дисперсии соответствует квадрату единицы измерения исходных значений. Например, если значения в выборке представляют собой измерения веса детали (в кг), то размерность дисперсии будет кг 2 . Это бывает сложно интерпретировать, поэтому для характеристики разброса значений чаще используют величину равную квадратному корню из дисперсии – стандартное отклонение .

Как сделать подстановку текста в excel?

• Количество листов. Добавьте или удалите рабочие листы или даже измените их названия.
• Стили. Используйте галерею стилей (расположена в группе Стили вкладки Главная) для настройки стилей. По умолчанию для всех ячеек установлен стиль Обычный, поэтому, чтобы изменить шрифт но умолчанию (включая цвет заливки, формат чисел, размер и вид рамки), нужно изменить данный стиль. Для этого щелкните правой кнопкой мыши на его имени в галерее стилей и выберите в контекстном меню команду Изменить. Затем внесите необходимые изменения в диалоговое окно Стиль.
• Параметры печати. Измените эти параметры на вкладке Разметка страницы. Например, вы можете включить колонтитулы, установить размер полей.
• Ширина столбцов. Если вам не нравится ширина столбцов, заданная по умолчанию, измените ее.
• Графика. Вставьте один или несколько графических объектов, например логотип вашей компании или фотографию вашей кошки.

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