<<
>>

2.2. Работа со списками

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

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

2.2.1. Создание и списков

Excel создает списки из фрагментов таблицы, которые должны быть заполнены по определенным правилам. Поскольку списки размещаются на рабочем листе, размеры списка ограничены размерами листа Excel, то есть число столбцов не может превышать 256, а число строк 65535. При работе со списками принято строки называть записями, а столбцы — полями. Запись представляет собой элемент информации в списке, а поле представляет собой определенную категорию информации. Например, если представить в виде списка штатное расписание предприятия, то записью будет каждая отдельная строка, описывающая конкретного сотрудника, а полями — информация об этом сотруднике: фамилия, должность и так далее.

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

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

в столбце Зарплата. По умолчанию Excel не распознает заголовки в формулах. А В

Должность Фамилия Имя Отчество Должностные обязанности

'Директор Иванов Иван Иванович Руководить пердприятием Гл. Бухгалтер Петрова Галина Борисовна Вести бухгалтерский учет

4 Секретарь Сидорова Ирина Петрови)^ Отвечать на звонки

Поле списка_J Заголовок поляЛ Запись списка

Рис. 2.4. Список и его элементы

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

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

Вы можете изменить список по вашему желанию.

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

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

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

Должность ^ Фамилии Имя j Отчество^] Должностные обязанности^ Зарплата Щ

Директор Иванов Иван Иванович Руководить пердприятием 10 000,00р.

Гл. Бухгалтер Петрова Талина Борисовна Вести бухгалтерский учет 8000,00р.

Секретарь Сидорова Ирина Петровна Отвечать на звонки 6 000,00р

Рис. 2.5. Список с фильтром 2.2.2. Отбор записей

При работе с большими списками бывает удобнее отображать на экране не весь список, а лишь его подмножество. Отбор записей (или, как еще говорят, фильтрация), представляет собой выделение из списка определенных записей, которые удовлетворяют заданным критериям. В программу встроена возможность фильтрации данных в списках. Щелкните мышью на любой ячейке списка, а затем выберите команду меню Данные Фильтр Автофильтр. Обратите внимание, как изменился вид списка(Рис. 2.5).

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

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

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

Для полей списка при выборе условия Первые 10... действительно выводятся первые десять записей исходной таблицы. Если выбрать элемент Первые 10... в списке условий поля, в котором есть только цифры, будет открыт диалог наложения условий (Рис. 2.6). В поле ввода со счетчиками, расположенном в левой 11«4ать I10 Jfcl {«"tort

- выи y'j- JmHt РРРЗ 1 Рис.

2.6. Наложение условий

Рис. 2.7. Настройка автофильтра

части диалога, вы выбираете количество записей, которое нужно показать. В открывающемся списке расположенном правее, вы можете выбрать два варианта отбора: наибольших и наименьших. В последнем списке, расположенном в правой части диалога, вы можете выбрать: показать заданное количество элементов списка или заданное количе- ство процентов от общего количества элементов. Элемент элементов списка служит для отображения определенного числа строк с наибольшими или наименьшими значениями в данном поле, а параметр % от количества элементов служит для отображения строк с наибольшими или наименьшими значениями в процентном отношении. После ввода условия, нажмите кнопку ОК, чтобы закрыть диалог и применить условие к выбранному полю списка.

Можно настроить довольно сложные условия фильтрации. В открывающемся списке условий выберите элемент Условие, и будет открыт диалог настройки (Рис. 2.7). В левом верхнем списке выбираются логические операциирав- но, больше илиравно и так дшюе. Кроме того, есть ряд операций для тек

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

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

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

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

Л скопировать результат в арутое «ooioj

3 ИСХОДНЫЙ диапазон )SAS1:SFS* |

Диапазон усговий: j

Поместить результат а диапазон: j галько увикапавыв .сапвсв

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

Рис. 2.8. Дополнительные настройки фильтра

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

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

Флажок Только уникальные записи позволяет ограничить результат фильтрации только уникальными записями. Если диапазон условия не задан, установка этого флажка убирает из списка все повторяющиеся записи. В заключение нажмите кнопку ОК, чтобы закрыть диалог настройки расширенного фильтра.

2.2.3. Сортировка списка

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

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

Есть несколько ограничений при сортировке текстов. Текст не должен начинаться с символов @, +, =, -. Эти символы могут стоять, только начиная со второй позиции. Если в начале произвольного слова поставить, например, дефис, то программа выдаст ошибку поскольку этот символ в первой позиции оз

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

Должность Фамилия Имя Отчество Должностные обязанности Зарплата

Секретарь Сидорова Ирина Петровна Отвечать на звонки 6 000,00р. Гл. Бухгалтер Петрова Галина Борисовна Вести бухгалтерский учет 8000,00р.

4 Директор Иванов Иван Иванович Руководить пердприятием 10000,00р.

Рис. 2.9 Список после сортировки

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

Записи списка можно отсортировать на основе данных любого из полей списка. Следует различать сортировку, производимую по одному столбцу, и сортировку по нескольким столбцам. Чтобы отсортировать список по какому-либо полю, щелкните мышью на любой ячейке нужного поля списка и нажмите кнопку д j на панели инструментов. В результате будет проведена сортировка записей списка на основании выбранного поля по возрастанию значений в этом поле. В нашем примере проведена сортировка по размеру зарплаты (Рис. 2.9).

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

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

Сортировать ґій .

—Б ¦

по убыванию

39т«н па

Г 1»>^ЫВЛЖ1Ю

S Sочередь, по - -

^TJ <• по возрастанию

'

йде^ифицирквать поля по

* . (первая строка ди«лй:-.>":і •

обозначениям столбцов лисої

Если нужна более сложная сортировка, программа позволяет вам сортировать по одному, двум или трем столбцам одновременно. При этом, если строки имеют одинаковые значения в первом указанном столбце, сортировка выполняется по значениям второго столбца и так далее. Сначала программа должна обнаружить список в таблице, поэтому вначале необходимо щелкнуть мышью на любой ячейке, относящейся к списку. Далее выберите команду меню Данные i?> Сортировка. Программа просмотрит ячейки во всех направлениях, сама опреде- лит границы списка и выделит ячейки. Также появится диалог настройки сортировки, позволяющий настроить достаточно сложную сортировку данных (Рис. 2.10).

Рис. 2.10. Настройка сортировки

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

Часто бывает, что разные записи списка содержат одинаковые значения полей. Например, в телефонном справочнике может быть несколько Ивановых. Для решения подобных проблем в диалоге есть группа элементов управления Затем по и В последнюю очередь по. Группа Затем по используется, если есть не-сколько одинаковых элементов списка, а группа В последнюю очередь по применяется, если есть несколько элементов списка имеющих два одинаковых поля. С помощью этих групп можно определять несколько полей для сортировки. Обратите внимание, что каждый последующий уровень сортировки действует, только если произошло совпадение на предыдущем уровне. Трехуровневая сортировка позволяет успешно сортировать сложные таблицы.

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

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

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

Нажмите кнопку Параметры, чтобы открыть диалог дополнительных настроек сортировки (Рис. Открывающийся список Сортировка по первому ключу в верхней части диалога позволяет выбрать один из нестандартных порядков сортировки, отличающийся от сортировки по возрастанию или убыванию. Вы можете выбрать упорядочение данных по дням недели и по месяцам. Если установить флажок Учитывать регистр, при сортировке будут различаться строчные и прописные буквы, и слова, начинающиеся с прописной буквы, будут распола-гаться раньше слов, начинающихся со строчной буквы.

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

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

<< | >>
Источник: Коцюбинский А.О., Грошев С.В.. Excel для бухгалтера в примерах.. 2003

Еще по теме 2.2. Работа со списками:

  1. Составление и выверка списков клиентов
  2. Создание списка рассылки
  3. ОБРАЗЕЦ СПИСКА ЛИТЕРАТУРЫ
  4. Подготовка источников информации для составления списков клиентов
  5. Глава 1. Доказывание по делам о защите избирательных прав граждан и прав на участие в референдуме, связанным с неправильностью в списках избирателей (участников референдума)
  6. Глава 14. Возможны варианты. Временная работа, частичная занятость, работа на дому и другое
  7. § 2. Прекращение трудового договора в связи с восстановлением на работе другого лица, ранее выполнявшего эту работу
  8. 6.3. Привлечение к работе в нерабочее время (за пределами установленной продолжительности рабочего времени).Сверхурочная работа
  9. 6.1. Доплата за совмещение профессий (должностей), расширение зон обслуживания, увеличения объема работы, исполнение обязанностей временно отсутствующего работника без освобождения от основной работы
  10. Перечень тяжелых работ и работ с вредными или опасными условиями труда, при выполнении которых запрещается применение труда лиц моложе восемнадцати лет (извлечение)
  11. 2.7. Работа с формами
  12. 5. Защита курсовой работы
  13. 1. ПРЕДВАРИТЕЛЬНАЯ РАБОТА
  14. 7.7. Работа с формой из встроенного языка
  15. Упражнение 7. Области концептуальной работы
  16. 2.9. Повышение эффективности работы
  17. 64. ПРИЕМ НА РАБОТУ
  18. Работа с клиентами