Какой операцией в excel можно получить из списка его часть

Обновлено: 06.01.2025

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

Описание

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

Создание образца листа

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

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

Определения терминов

В этой статье для описания встроенных функций Excel используются указанные ниже условия.

Определение

Вся таблица подстановки

Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

Просматриваемый_массив
-или-
Лукуп_вектор

Диапазон ячеек, которые содержат возможные значения подстановки.

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

3 (третий столбец в инфо_таблица)

Ресулт_аррай
-или-
Ресулт_вектор

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

Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение "5" в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Функции

LOOKUP ()

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

Ниже приведен пример синтаксиса формулы подСТАНОВКи.

= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

Следующая формула находит возраст Марии на листе "образец".

= ПРОСМОТР (E2; A2: A5; C2: C5)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как "Мария" находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

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

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

Ниже приведен пример синтаксиса формулы ВПР :

= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)

Следующая формула находит возраст Марии на листе "образец".

= ВПР (E2; A2: C5; 3; ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется "3" в качестве Колумн_индекс (столбец C). Так как "Мария" находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:

INDEX () и MATCH ()

Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.

Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:

= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

Следующая формула находит возраст Марии на листе "образец".

= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как "Мария" находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

СМЕЩ () и MATCH ()

Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.

= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

Эта формула находит возраст Марии на листе "образец".

= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как "Мария" находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:

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

Строка списка – это запись базы данных;

Столбец списка – это поле базы данных.

Название столбца при работе с базой данных называется именем поля.

ПРАВИЛА формирования списка (базы данных):

1. Имя поля (название столбца) должно занимать ровно одну ячейку.

2. Имена полей должны занимать верхнюю строку списка.

3. Между именами полей и данными не должно быть пустых строк.

4. Между списком и другими данными (вспомогательными параметрами, диапазонами критериев, другим списком и т.п.) должно быть не менее одной пустой строки и одного пустого столбца.

Сортировка данных списка

Сортировка данных в списке – это расположение данных в определенном порядке. Сортировать данные списка в Excel можно по одному, двум или трем полям.

Рассмотрим базу данных Студенты вида:

3.2.1. Сортировка по одному полю

Щелкнуть мышью в любой ячейке нужного поля (столбца) внутри списка. Щелкнуть мышью по кнопке (сортировка по возрастанию) или (сортировка по убыванию). Например, если щелкнуть мышью в любой ячейке столбца А (с А1 по А10) и выполнить сортировку по возрастанию, данные будут расположены в алфавитном порядке по фамилиям.

3.2.2. Сортировка по нескольким полям

Щелкнуть мышью в любой ячейке внутри списка. Выбрать пункт меню Данные – Сортировка. В диалоговом окне Сортировка данныхв выпадающем списке Сортировать по выбрать поле (столбец) для первичной сортировки; в выпадающем списке Затем по выбрать поле (столбец) вторичной сортировки, а в выпадающем списке В последнюю очередь по выбрать поле (столбец) следующей сортировки.

Например, если поля для сортировки выбраны так: 1. Номер группы; 2. Год рождения; 3. Фамилия; то данные сначала будут отсортированы по номеру группы; внутри группы по году рождения и, наконец, при одинаковых номере группы и годе рождения, по фамилиям в алфавитном порядке. Результат такой сортировки приведен ниже:

3.3. Фильтрация (выборка) данных списка

Фильтрация данных в списке – это выбор данных из списка по заданному критерию (условию). Существуют два вида фильтра – автофильтр и расширенный фильтр. При фильтрации с помощью автофильтра на данные каждого столбца может быть наложено 1 или 2 условия, связанных между собой логическим ИЛИ либо логическим И. При фильтрации с помощью расширенного фильтра количество накладываемых условий практически не ограничено.

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

Рассмотрим таблицу базы данных Студенты вида:

3.3.1. Фильтрация данных с помощью автофильтра

Задание 1. Пусть необходимо из списка Студенты п. 3.3 выбрать студентов 12-й группы, получающих стипендию 800 рублей и более. Ход выполнения работы:

Щелкнуть мышью в любой ячейке внутри списка

Выбрать пункт меню Данные – Фильтр – Автофильтр. В заголовках столбцов списка появятся кнопки раскрывающегося списка ( ).

В столбце Группа щелкнем по такой кнопке и выберем из списка 12.Фильтрация по данному полю сразу будет произведена.

Примечание. Отменить результаты фильтрации данных в таблице можно, щелкнув и выбрав Показать все.

Задание 2. Пусть из списка Студенты п. 3.3 необходимо выбрать студентов 11-й и 12-й групп 1987-го года рождения.

Предварительно отменив результаты предыдущей фильтрации, выполним следующее:

В столбце Группа щелкнем по кнопке и выберем из спискаУсловие; затем в диалоговом окне Пользовательский автофильтр в верхних строках описанным в Задании 1 способом сформируем условие «равно 11».Далее установим переключатель ИЛИ. В нижних строках сформируем условие «равно 12».

Пояснение: условия – студенты 11-й и 12-й групп – связаны логическим ИЛИ (а не логическим И), т.к. номер группы 11 или 12, а не 11 и 12 одновременно.

В столбце Год рождения щелкнем по кнопке и выберем из раскрывающегося списка 1987.

Фильтр покажет записи, удовлетворяющие таким условиям.

3.3.2. Фильтрация данных с помощью расширенного фильтра

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

ПРАВИЛА формирования диапазона условий для расширенного фильтра:

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

Задание 1. Пусть из базы данных Студенты п. 3.3 необходимо выбрать студентов 11-й и 12-й групп 1987-го года рождения.

Для формирования диапазона условий (правило № 2) в ячейки А12 и В12 копируем заголовки нужных нам столбцов – группаи год рождения соответственно. 11-ю строку оставляем пустой (правило № 1).

Условия для номера группы (11) и года рождения (1987) связаны между собой логическим И, поэтому записываем их в одной строке (правило № 4), в нашем случае 13-ой. В ячейке В13 (под заголовком год рождения) записываем условие =1987или 1987. (Примечание: при записи условий знаки неравенств записывать обязательно, а знак равенства можно опустить). В ячейке А13 (под заголовком группа) записываем условие 11.

Теперь запишем условия для отбора студентов 12-й группы. С условиями для студентов 11-й группы они связаны логическим ИЛИ, поэтому должны быть записаны в другой строке (правило № 4). В 14-й строке записываем условия: для столбцагруппа (ячейка А14) – 12, а для столбца год рождения(ячейка В14 ) – 1987.

Диапазон условий сформирован:

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

Выполняем команду меню Данные – Фильтр – Расширенный фильтр.

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

Примечание: если перед выполнением команды меню Данные – Фильтр – Расширенный фильтр курсор поместить в любую ячейку внутри таблицы базы данных, исходный диапазон будет выделен автоматически.

Устанавливаем курсор в строке Диапазон условий и мышью в окне таблицы выделяем блок ячеек А12:В14 (или А12:В13 , если выбран второй вариант записи условия для номера группы), в которые мы записали условия фильтрации.

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

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

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

Примечание. Отменить результаты фильтрации данных в таблице можно, выполнив команду меню Данные – Фильтр - Показать все.

Задание 2. Пусть из базы данных Студенты п. 3.3 нужно выбрать студентов 11-й и 12-й групп, получающих стипендию в размере от 500 до 800 рублей включительно.

Для формирования диапазона условий в ячейки А12 и В12 копируем заголовки столбцов группа и стипендия соответственно (правило № 2). 11-ю строку оставляем пустой (правило № 1).

Условия для номера группы (11) и размера стипендии (от 500 до 800) связаны между собой логическим И, поэтому (правило № 4) записываем их в одной строке (в нашем случае 13-й). В ячейке А13 (под заголовком группа) записываем условие 11. Условие для размера стипендии может быть сформулировано так «больше или равно 500, но меньше или равно 800». Эти два условия связаны логическим И, т.к. требуется их одновременное выполнение. Каждое из этих условий должно быть записано в отдельной ячейке (правило № 3), причем ячейки (по правилу № 4)должны располагаться на одной строке (в нашем случае 13-й) и заголовок для ячеек с этими условиями одинаков – стипендия. Поэтому для формирования условия «от 500 до 800» в ячейку С12 копируем еще раз заголовок стипендия.Под любым заголовком стипендия в одной ячейке (например, В12) записываем условие >=500, а в другой (например, С12) условие <=800.

Аналогичным образом записываем условия для отбора студентов 12-й группы, которые связаны логическим ИЛИ с условиями для студентов 11-й группы, поэтому (правило № 4) должны быть записаны в другой строке (в нашем случае 14-й).

Диапазон условий сформирован:

Выполняем команду меню Данные – Фильтр – Расширенный фильтр,указываем Исходный диапазон(А1:F10)и Диапазон условий(А12:С14 или А12:С13). Нажимаем ОК. Фильтрация по заданным условиям будет выполнена.

Задание 3. Пусть из приведенного списка необходимо выбрать студентов 11-й группы:

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

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

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

3.3.3. Использование в расширенном фильтре вычисляемого критерия

Вычисляемый критерий представляет собой формулу, записанную в строке области условий, которая может принимать одно из логических значений ИСТИНА или ЛОЖЬ. Формула строится с использованием адресов ячеек, встроенных функций, математических знаков операций и знаков сравнения.

ПРАВИЛО № 5: Если в расширенном фильтре используется вычисляемый критерия сравнения, то имя столбца, содержащего формулу вычисляемого критерия, должно обязательно отличаться от названия столбца в списке.

Задание. Пусть необходимо из базы данных Студенты п. 3.3 выбрать студентов 11-й и 12-й групп, стипендия которых больше средней.

Если на экране находятся результаты предыдущей фильтрации, отменим их, выполнив команду Данные – Фильтр – Отобразить все.

Скопируем названия столбцов (правило № 2), по которым будут записываться условия отбора (это столбцы Группа и Стипендия) в ячейки А12 и В12 соответственно.

Записываем условия по выбору номера группы: в ячейку А13 (под заголовком Группа) записываем 11, а в ячейку А14 записываем 12(по правилу № 4).

Условие отбора студентов по размеру стипендии будем записывать с помощью вычисляемого критерия, т.к. необходимо осуществлять вычисление средней стипендии, поэтому название столбца Стипендия в ячейке В12 изменим, например, на СтипендияС(правило № 5). В ячейках В13 и В14 нужно записать условия отбора по размеру стипендии, а они для обеих групп одинаковы. Условие для вычисляемого критерия записывается по правилам формирования формул Excel, значит, оно должно начинаться со знака «равно», использовать функцию СРЗНАЧ (для вычисления средней стипендии) и содержать адрес ячейки F2 (самой первой из ячеек с данными по стипендии). Причем в функции СРЗНАЧ должны быть использованы абсолютные адреса ячеек, т.к. диапазон ячеек для расчета среднего неизменен, а при записи условия сравнения, наоборот, записывается относительный адрес, т.к. в процессе выборки должны просматриваться и сравниваться со средней величиной стипендии все ячейки, начиная с F2 . Таким образом, вычисляемый критерий должен быть записан так: =F2>СРЗНАЧ($F$2:$F$10).Диапазон условий сформирован:

Примечание: после завершения формирования вычисляемого критерия в ячейках будет отображен результат вычисления: ИСТИНА или ЛОЖЬ, а саму формулу критерия можно просмотреть лишь в строке формул.

Выполняем команду меню Данные – Фильтр – Расширенный фильтр,указываем Исходный диапазон(А1:F10)и Диапазон условий(А12:В14). Нажимаем ОК. Фильтрация по заданным условиям будет выполнена.

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

Приложение Excel, будучи составной частью популярного пакета (имеется в виду Microsoft Office), по массовости своего использования уступает, пожалуй, только другому приложению этого же пакета (имеется в виду редактор Word). Хотя это утверждение основано и не на статистических данных, однако, думается, выглядит вполне правдоподобно. В любом случае, малознакомым широкому кругу пользователей приложение Excel никак не назовешь. У Microsoft Excel есть существенный, и, как представляется, не до конца раскрытый потенциал, и задача данного пособия состоит в том, чтобы показать возможности MS Excel при решении прикладных задач.

Основные функции Excel:

− проведение различных вычислений с использованием мощного аппарата функций и формул;

− исследование влияния различных факторов на данные; − решение задач оптимизации;

− получение выборки данных, удовлетворяющих определенным критериям;

− построение графиков и диаграмм.

Общие сведения о книгах и листах Microsoft Excel

При запуске Excel открывается рабочая книга с пустыми рабочими листами. Рабочая книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Такие файлы имеют расширение - .xlsх. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи. Имена листов отображаются на ярлычках в нижней части окна книги. Для перехода с одного листа на другой следует указать соответствующий ярлычок. Название активного листа выделено жирным шрифтом. Рабочий лист - это собственно электронная таблица, основной тип документа, используемый в Excel для хранения и манипулирования данными. Он состоит из ячеек, организованных в столбцы и строки, и всегда является частью рабочей книги. В рабочем листе Excel 2007имеется 16 384 столбца, заголовки которых указаны в верхней части листа с помощью букв английского алфавита и 1048576 строк. Столбцы по умолчанию озаглавлены буквами, строки - цифрами. Щелкните мышью на любой ячейке вашего рабочего листа и, таким образом, вы сделаете ее текущей или активной (она пометится рамкой). В поле имени, будет показан адрес текущей ячейки, например В4. Ячейка - это основной элемент электронной таблицы только в ней может содержаться какая-либо информация (текст, значения, формулы).

Элементы экрана

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

Рис. 1.1. Структура рабочего листа

Строка меню.

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

Рис. 1.2. Вид строки меню

Ввод и редактирование данных

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

Строка формул Microsoft Excel, используется для ввода или редактирования значений или формул в ячейках или диаграммах. Здесь выводится постоянное значение или формула активной ячейки. Для ввода данных выделите ячейку, введите данные и щелкните по кнопке с зеленой «галочкой» или нажмите ENTER. Данные появляются в строке формул по мере их набора.

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

Рис. 1.3. Вид строки формул

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

Каждая ячейка имеет адрес, который обозначается именем столбца и именем строки. Например А2. Если надо задать адрес ячейки, которая находится на другом рабочем листе или в другой рабочей книге, перед именем ячейки задают имя рабочего листа, а во втором случае и имя рабочей книги. Например: Лист 1!А1 или =[Работа1]Задание1!$B$4 .

Наряду с понятием адреса ячейки в электронной таблице используется понятие ссылки. Ссылка– это элемент формулы, который используется тогда, когда надо сослаться на какую-нибудь ячейку таблицы. В этом случае адрес будет использоваться в качестве ссылки.

Есть два стиля представления ссылок в Microsoft Excel:

- Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).

Ссылки в Excel бывают 3-х видов:

- Относительные ссылки (пример:A1);

- Абсолютные ссылки (пример: $A$1);

- Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).

Относительные ссылки

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

Например, ячейка B2 содержит формулу = B5+C8, т. е. первый операнд находится на три ячейки ниже в том же столбце, а второй операнд находится на 6 строк ниже и один столбец правее ячейки B2. При копировании данной формулы и вставке ее в ячейку С3, ссылки в ней снова будет указывать на ячейки, расположенные: первая - на три ячейки ниже в том же столбце, вторая - на 6 строк ниже и один столбец правее ячейки С3. Так, если формула из ячейки B2 копируется в ячейку С3, то формула примет вид =С6 + D9, а если ско-пировать содержимое В2 в B3, то в ячейке B3 формула примет вид = B6+C9.

Рис. 1.4. Относительная ссылка

Абсолютные ссылки

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, используют абсолютные ссылки. Абсолютная ссылка всегда указывает на одну и ту же ячейку, независимо от расположения формулы, её содержащей. Для создания абсолютной ссылки на ячейку необходимо поставить знак доллара ($) перед той частью ссылки, которая не должна изменяться. Например, если в A1 находится формула =$B$5+$C$8, то при копировании содержимого ячейки A1 в ячейку В2 или A3 в этих ячейках также будетнаходиться формула =$B$5+$C$8, что говорит о том, что исходные данные всегда будут браться из ячеек В5 и С8.

Рис. 1.5. Абсолютная ссылка

Смешанные ссылки

Ссылки на ячейки могут быть смешанными. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов имеет вид $A1 или $B1. Абсолютная ссылка строки имеет вид A$1, B$1. При изменении позиции ячейки, содержащей формулу, относительная часть ссылки изменяется, а абсолютная не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная часть ссылки автоматически корректируется, а абсолютная остается без изменений.

Рис. 1.6. Смешанная ссылка

Кроме понятия ячейки используется понятие диапазона – прямоугольной области, состоящей из нескольких (или одного) столбцов и нескольких (или одной) строк. В качестве адреса диапазона указываются адреса левой верхней и правой нижней ячеек диапазона, разделенные знаком двоеточие ( : ). Например, диапазон A1:C4 содержит 12 ячеек (по 3 ячейки в строках и 4 ячейки в столбцах).

Для работы с несколькими ячейками сразу необходимо выделить блок ячеек. Это выполняется следующим образом: для смежных ячеек щелкните на ячейке и удерживая кнопку мыши, протяните по листу указателем. При этом будет произведено выделение всех смежных ячеек. Блок описывается двумя адресами, разделенными знаком двоеточия - адресом верхней-левой и нижней-правой ячеек. На рисунке, например, выделен блок: A2:D4.

Рис. 1.7. Диапазон ячеек

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

Автозаполнение

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

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

Однако, если ячейка содержит число, дату или период времени, то при копировании с помощью средства Автозаполнение происходит приращение значения её содержимого. Например, если ячейка имеет значение «Январь», то существует возможность быстрого заполнения других ячеек строки или столбца значениями «Февраль», «Март» и так далее. Могут создаваться пользовательские списки автозаполнения для часто используемых значений, например, названий районов города или списка фамилий студентов группы.

Рис. 1.8. Пример автозаполнения по месяцам

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

Для создания рядов данных необходимо выполнить следующие действия:

- введите в ячейку первый член ряда;

- подведите указатель мыши к черной точке в правом нижнем углу выделенной ячейки (в этот момент белый крестик переходит в черный) и нажмите на левую кнопку мыши;

- удерживая нажатой кнопку мыши, выделите нужную часть строки или столбца;

- после того как вы отпустите кнопку мыши, выделенная область заполнится данными.

Понятие формулы

Формулы – это выражение, начинающееся со знака равенства «═» и состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены знаками арифметических операций. К знакам арифметических операций, которые используются в Excelотносятся:сложение; вычитание; умножение; деление; возведение в степень.

Некоторые операции в формуле имеют более высокий приоритет и выполняются в такой последовательности:

возведение в степень и выражения в скобках;

умножение и деление;

сложение и вычитание.

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

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

Перемещение и копирование формул

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

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

Рис.1. 9. Пример автозаполнения формул

Таблица 1

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

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

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

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

Неверная ссылка на ячейку

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

Результат поиска пересечений двух непересекающихся областей, то есть неверная ссылка

Функции Excel

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

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

Синтаксис функций

Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции, например СУММ, - описывает операцию, которую эта функция выполняет. Аргументы задают значения или ячейки, используемые функцией. В формуле, приведенной ниже: СУММ - имя функции; В1:В5 - аргумент. Данная формула суммирует числа в ячейках В1, В2, В3, В4, В5.

Знак равенства в начале формулы означает, что введена именно формула, а не текст. Если знак равенства будет отсутствовать, то Excel воспримет ввод просто как текст.

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

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

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

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

Чтобы создать выпадающий список, выделите ячейку, где он должен появиться (или группу ячеек) и перейдите на вкладку Данные -> Проверка данных.

В появившемся окне укажите тип данных – Список, поставьте галочку рядом со строкой «Список допустимых значений».

Источником данных может быть:

Текст – пишется через точку с запятой «;» и без знака равно «=», например
Материалы;Заработная плата;Амортизация

Ссылки на ячейки:
=$A$1:$A$7

Именованный диапазон:
=ИмяДиапазона

Связанные выпадающие списки

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

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

Способ 1. Названия групп в заголовках столбцов, в строках – элементы групп.

Способ 2. Названия групп – в первом столбце, элементы групп – во втором столбце.

Способ 1. Связанные выпадающие списки из таблицы с группами в заголовках столбцов

Исходные данные: таблица с названиями групп в заголовках столбцов.

Справка:

Форматированная («умная») таблица Excel

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

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

Создать форматированную таблицу просто: выделите диапазон ячеек и перейдите в меню Главная -> Форматировать как таблицу -> выберите понравившийся вид таблицы. Готово – форматированная таблица создана.

Формула ДВССЫЛ

Формула ДВССЫЛ передает значения из ячейки, адрес которой записан в самой формуле в виде текстовой строки.

Например, записываем в ячейке B1 адрес ячейки А1. Формула ДВССЫЛ( B1 ) «увидит», какой адрес записан в ячейке B1, а результатом вычисления формулы будет текст, записанный в ячейке А1. Эту же формулу можно записать, указав адрес ячейки в кавычках – ДВССЫЛ( « А1 » ).

С помощью ДВССЫЛ можно обратиться к ячейке по адресу с помощью других формул, например СЦЕПИТЬ, & или ЕСЛИ и т.д. Так, формула на рисунке ДВССЫЛ( B1 & C1 ) обращается к тексту в ячейке А1. После нажатия Enter в ячейке, где вводилась формула ДВССЫЛ( B1 & C1 ) появится значение из ячейки A1, в нашем случае это «текст».

Пошаговая инструкция по созданию связанных выпадающих списков

Шаг 1. Создайте справочник исходных данных в виде форматированной smart-таблицы.

Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: выберите в меню Главная -> Форматировать как таблицу.

В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.

Столбец таблицы: = Источник[Материалы]

Чтобы появилась такая формула, нажмите равно = и выделите столбец, его имя появится в строке формул.

Шаг 2. Создайте выпадающий список с группами.

Готово! В столбце «группа» появился выпадающий список.

Шаг 3. Создайте выпадающий список со статьями.

Готово! В столбце «статья» появляется только список статей, входящих в группу.

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

Способ 2. Связанные выпадающие списки из таблицы с группами в первом столбце и элементами - во втором

Исходные данные: таблица с названиями групп в первом столбце, элементами групп – во втором столбце.

На самом деле в сети можно найти несколько вариантов реализации этого способа. Но у них у всех есть один недостаток: такой список нужно «администрировать». Потому что таблица должна быть всегда отсортирована по названиям групп – нельзя, чтобы группы располагались произвольно. Если группы будут идти «как попало», то формула, с помощью которой это всё сделано (СМЕЩ) не сработает, и список будет создаваться с ошибкой. Т.е. пользователю нужно все время сортировать первый столбец или добавлять туда данные в алфавитном порядке. А еще потребуется записывать где-то отдельно сами названия групп и это тоже нужно будет делать «вручную».

Мы придумали, как обойти эту проблему: с помощью сводных таблиц , которые будут за нас упорядочивать данные. Благодаря этому постоянное «администрирование» будет заменено на простую процедуру «обновить».

Для создания списков используем форматированные (умные) таблицы, сводные таблицы, формулы СМЕЩ + ПОИСКПОЗ + СЧЁТЗ, СЧЁТЕСЛИ и диспетчер имен.

Справка:

Формула СМЕЩ

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

Синтаксис формулы СМЕЩ такой:

СМЕЩ(ссылка ; смещ_по_строкам ; смещ_по_столбцам ; [высота] ; [ширина] ), где

ссылка – ссылка, от которой вычисляется смещение, может быть адресом ячейки или группы ячеек;
смещ_по_строкам – количество строк, которые требуется отсчитать вверх или вниз от начальной ссылки;
смещ_по_столбцам – количество столбцов, которые требуется отсчитать влево или вправо от начальной ссылки;
[высота] – число строк возвращаемой ссылки (необязательный);
[ширина] – число столбцов возвращаемой ссылки (необязательный).

Формула ПОИСКПОЗ

Ищет нужный нам элемент в диапазоне ячеек и выдает его порядковый номер в диапазоне.

Синтаксис ПОИСКПОЗ такой:

ПОИСКПОЗ( искомое_значение ; просматриваемый_массив ; [тип_сопоставления] )

Подробнее про эту формулу можно посмотреть в видеоинструкции: Какая формула лучше ВПР и работает с несколькими критериями

Формула СЧЁТЗ

СЧЁТЗ просто считает количество непустых ячеек в диапазоне.

Формула СЧЁТЕСЛИ

Почти тот же СУММЕСЛИ, только проще – подсчитывает количество значений, соответствующих определенному условию.

Пошаговая инструкция по созданию списков

Шаг 1. Преобразуйте исходные данные в форматированную smart-таблицу.

Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: перейдите в меню Главная -> Форматировать как таблицу.

В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.

Форматированная таблица «статьи» создана.

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

Создайте вторую сводную таблицу со статьями: меню Вставка -> Сводная таблица. В область строк поместите группы и статьи.

Форматируем сводную таблицу со статьями и придаем ей вид справочника.
Выделите любую ячейку таблицы, перейдите на вкладку Конструктор -> Макет отчета -> Показать в табличной форме. У нас получится почти та таблица, которая нам нужна, но в ней автоматом появятся промежуточные суммы. Чтобы их отключить, идем: Промежуточные итоги -> Не показывать промежуточные суммы.

Скройте строку «Общий итог» в обеих таблицах справочников. Перейдите на вкладку Конструктор -> Общие итоги -> Отключить для строк и столбцов.

В итоге получатся два справочника, как на рисунке ниже. Для удобства разместите таблицы рядом на одном листе – с первой строки и в столбцах A, C и D, как на рисунке (это поможет разобраться с формулой СМЕЩ).

Шаг 3. Создайте именованные диапазоны с помощью диспетчера имен.

Откройте диспетчер имен: в меню Формулы -> Диспетчер имен.

- Введите имя «ГруппыСписок» и формулу, которая будет определять диапазон:
  =СМЕЩ($A$1;1;0;СЧЁТЗ($A:$A)-1;1)
Пояснения к формуле:

СМЕЩ ( $A$1 ; 1 ; 0 ; СЧЁТЗ( $A:$A ) – 1 ; 1 ) – определяет адрес ячеек с названиями групп.
- - $A$1 – это первая ячейка в справочнике групп.
  - Следующие цифры 1 ; 0 – это отступ от первой ячейки на 1 строку и 0 столбцов (отступ нужен, потому что в первой ячейке название столбца).
  - СЧЁТЗ( $A:$A ) – 1 Считаем число непустых ячеек в столбце А. Вычитаем -1, потому что название столбца не должно быть в списке.
  - Последнее число 1 в формуле – это количество столбцов.
  Нажмите ОК. Названия листов в формуле появятся сами.
  - Точно так же создайте в диспетчере имен список статей.
    Введите имя ГруппыСтатей, а для диапазона – формулу:
    =СМЕЩ($C$1;ПОИСКПОЗ($G2;$C:$C;0)-1;1;СЧЁТЕСЛИ($C:$C;$G2);1)
  Пояснения к формуле:
  
  СМЕЩ ( $C$1 ; ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 ; 1 ; СЧЁТЕСЛИ( $C:$C ; $G2 ) ; 1 ) – определяет адрес ячеек с названиями статей из группы с помощью ПОИСКПОЗ, которая ищет группы статей.
  - $С$1 – это первая ячейка в столбце с группами.
  - ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 Определяет, на сколько строк нужно отступить от первой ячейки. ПОИСКПОЗ ищет название группы, выбранной в таблице с данными (столбец $G) среди ячеек справочника (столбец $C). В адресе ячейки $G2 не «закрепляем» номер строки с помощью знака $, чтобы формула работала для каждой ячейки в столбце.
  - Следующая цифра 1 – это отступ на 1 столбец вправо, т.е. переходим к столбцу «статьи», откуда нужно брать данные.
  - СЧЁТЕСЛИ( $C:$C ; $G2 ) – считаем число ячеек в столбце $C, в которых названия групп такие же, как в столбце с данными. Здесь тоже не «закрепляем» номер строки у ячейки $G2 с помощью знака $.
  - Последнее число 1 в формуле – это количество столбцов.
  Шаг 4. Создайте выпадающие списки.
  
  Выделите ячейки в столбце «группы», перейдите в меню Данные -> Проверка данных. Задайте тип данных Список, источник =ГруппыСписок.
  
  То же самое – для статей. Тип данных – список, источник =ГруппыСтатьи
  
  Выпадающие списки готовы. Форматированные smart-таблицы позволят «захватить» все данные, а сводные таблицы – избежать ошибок, отсортировать справочник и создать список групп.
  
  Читайте также: