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

Обновлено: 28.06.2024

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

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

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

строка списка – запись базы данных;
столбец списка – поле базы данных.

Ячейки верхней строки с именами полей образуют область имен полей.

Рис. 1. Структурные элементы списка (базы данных Excel)

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

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

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

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

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

В среде Microsoft Excel предусмотрено три уровня сортировки, которые определяются в диалоговом окне Сортировка диапазона (Рис. 2).

В том же окне устанавливается порядок сортировки в столбцах – по возрастанию или убыванию. При сортировке по возрастанию упорядочение идет:

для чисел - от меньшего к большему;
для символьных данных – по алфавиту от А до Я;
для дат – в хронологическом порядке.

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

Рис. 2. Параметры сортировки

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

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

Кнопка Параметры выводит диалоговое окно Параметры сортировки, в котором задаются дополнительные установки сортировки: с учетом регистра или без учета; по столбцам или по строкам; порядок сортировки – обычный или специальный, выбранный из предлагаемого списка. Этот список можно сформировать самостоятельно с помощью Сервис/Параметры/вкладка Списки.

4.4.3. Фильтрация

Фильтрация данных в списке – это отбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные/Фильтр. Имеется две разновидности этой команды, задаваемых параметрами: Автофильтр и Расширенный фильтр.

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

4.4.4. Автофильтрация

Команда Данные/Фильтр/Автофильтр для каждого столбца строит список значений, который используется для задания условий фильтрации (Рис. 3). В каждом столбце появляется кнопка списка, нажав которую можно ознакомиться со списком критериев отбора.

Рис. 3. Список с автофильтром

Для каждого столбца в списке критериев отбора предусматриваются следующие варианты:

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

Рис. 4. Наложение условия по списку

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

Условие для отбора записей по конкретным значениям в определенном столбце может состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ (Рис. 5). Каждая часть условия включает один из операторов отношения:

равно;
не равно;
больше;
больше или равно;
меньше;
меньше или равно;
начинается с;
содержит значение, которое может выбираться из списка или содержать шаблонные символы “*”, “?” и т.п.

Рис. 5. Задание условия фильтрации

Примеры условий

Для поля Код предмета можно сформировать условия:

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

отобрать все записи, которые не содержат кода предмета п1

4.4.5. Расширенный фильтр

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

Расширенный фильтр позволяет задать условия отбора двух типов критериев:

критерий сравнения;
вычисляемый критерий.

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

Технология использования расширенного фильтра состоит из двух этапов:

1-й этап – формирование области условий;
2-й этап – фильтрация записей списка.

Технология формирования области условий

Правила формирования Критерия сравнения

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

С помощью Формы можно осуществить поиск конкретных записей на основании сложных критериев.

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

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

1) сделать текущей первую запись списка;

2) выполнить команду Данные, Форма;

3) нажать кнопку Критерии. Появится диалоговое окно формы данных без записей;

4) ввести в поле Количество студентов в группе в качестве критерия условие "> 20" и нажать кнопку Правка(рис. 2). В диалоговом окне отобразится первая запись, соответствующая заданному критерию;

При задании критериев можно применять символы подстановки: звёздочку (*) для замены произвольного количества неизвестных символов и знак вопроса (?) для замены одного неизвестного символа.

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

Оператор	Функция
=	Равно
Больше
<>	Не равно
=	Больше или равно

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

Рис. 2. Диалоговое окно Форма

Функция Автофильтра

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

Чтобы воспользоваться Автофильтром:

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

2) выполнить команду Данные, Фильтр, Автофильтр;

3) ячейки с названиями полей превращаются в раскрывающиеся списки (рис. 3).

Рис. 3. Вид полей списка после активизации Автофильтра

Чтобы отфильтровать записи:

1) раскрыть список, соответствующий полю, которое следует включить в критерий (рис. 3);

2) выбрать желаемый критерий из раскрывшегося списка, пункты которого представлены в табл. 4;

3) задать параметры в диалоговом окне Пользовательский автофильтр, если выбран критерий Условие.

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

КритерииАвтофильтра

Критерии	Описание
Все	Вывод на экран всех записей (снятие ограничений на поле)
Первые 10	Вывод на экран заданного числа или процента первых или последних элементов списка
Условие	Вывод на экран диалогового окна Пользовательский автофильтрдлязаданияодного или двух условий с использованием шаблонов, операцийсравненияи логических операций И, ИЛИ
Точное значение	Вывод на экран записей, поля которых в точности совпадают с выбранным значением

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

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

Расширенный фильтр

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

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

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

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

Как можно создать новую таблицу значений?

Как добавить новую колонку с определенным типом и заголовком?

Имя (необязательный, тип Строка , по умолчанию пустая строка). Имя колонки.
Тип (необязательный, тип ОписаниеТипов ). Объект, описывающий допустимые типы значений для колонки. Если параметр не указан, в колонке можно будет хранить значение любого типа.
Заголовок (необязательный, тип Строка , по умолчанию пустая строка). Заголовок колонки таблицы значений. Используется при визуальном отображении таблицы значений.
Ширина (необязательный, тип Число , по умолчанию ноль). Ширина колонки в символах. Используется при визуальном отображении таблицы значений.

Как добавить новые строки?

Как выгрузить результат запроса в таблицу значений?

Как обойти в цикле все строки таблицы значений?

Как сделать выборку строк в таблице значений с отбором?

ПараметрыОтбора (обязательный, тип Структура ). Задает условия поиска: ключ структуры определяет имя колонки, по которой будет осуществляться поиск, а значение структуры — искомое значение.

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

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

Как выполнить сортировку?

Как вычислить итоги по колонке?

Как сделать копию таблицы значений?

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

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

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

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

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

Как найти строку в таблице значений по значению?

Значение (обязательный, тип Произвольный ). Искомое значение.
Колонки (необязательный, тип Строка , по умолчанию пустая строка). Список имен колонок, разделенных запятыми, по которым производится поиск. Если параметр не указан, поиск осуществляется по всей таблице значений.

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

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

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

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

Создание новой таблицы значений

ТабЗначений = Новый ТаблицаЗначений;

По результатам данной команды будет создана таблица, в которой имеется три столбца (колонки).

Добавление колонок

ФорматЧисла = Новый КвалификаторыЧисла(9, 2)

Данная команда предполагает создание нового столбца, обладающего размерностью в 9 символов (точность 2 символа).

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

ВыбНоменклатура = Справочники.Номенклатура.Выбрать();

Пока ВыбНоменклатура.Следующий() Цикл

НоваяСтрока = ТабЗначений.Добавить();

НоваяСтрока. КолКод = ВыбНоменклатура.Код;

НоваяСтрока. КолНоменклатура = ВыбНоменклатура.Ссылка;

НоваяСтрока. КолКоличество = 1;

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

Выгрузка данных запроса в табличном виде

НовыйЗапрос = Новый Запрос;

НовыйЗапрос.Текст = «ВЫБРАТЬ

| СправочникНоменклатура.Ссылка КАК СсылкаНоменклатура

ТабЗначений = НовыйЗапрос.Выполнить().Выгрузить();

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

Выборка строк (может быть проведена двумя способами)

Для Каждого СтрокаТаб Из ТабЗначений Цикл

Сообщить(СтрокаТаб.КолНоменклатура);

// Узнаем количество строк в таблице, так как индекс начинается с нуля, уменьшаем количество строк на 1 (одну).

КоличествоСтрок = ТабЗначений.Количество() — 1;

// Выполняем цикл по количеству строк

Для Н = 0 по КоличествоСтрок Цикл

Сообщить(ТабЗначений [Н]. КолНоменклатура);

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

Выборка строк с отбором

// Создаем отбор

ОтборСтруктура = Новый Структура;

ОтборСтруктура.Вставить(«КолНоменклатура «, СсылкаНоменклатура);

// Используем отбор в поиске строк

ТабЗначенийСтроки = ТабЗначений.НайтиСтроки(ОтборСтруктура);

Для Каждого СтрокаТаб Из ТабЗначенийСтроки Цикл

Сообщить(СтрокаТаб.КолКоличество);

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

Определение итогов в колонке

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

Копирование таблицы

Данная команда позволяет создать полноценную копию таблицы.

Свернуть таблицу по значению и провести суммирование результатов

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

Поиск нужной строки по значению

Команда позволяет найти необходимую строку с данными.

Удаление строк или очистка всей таблицы

ТабЗначений.Удалить(0); // Удалит первую строку по индексу

ТабЗначений.Очистить(); // Полностью очистить таблицу

Установка и получение значений

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

Вставка значений

НоваяСтрока = ТабЗначений.Вставить(1);

НоваяСтрока.КолНоменклатура = ТекНоменклатура;

НоваяСтрока.КолКоличество = 5;

Команда позволяет на основе индекса вставить строку и провести ее заполнение.

Дополнительный функционал для упрощения работы

Читайте также: