Соединение двух списков

Чтобы рассчитать премию за выслугу лет, нам нужно знать стаж работы каждого служащего на предприятии и его должностной оклад. Эти сведения, как вы помните, хранятся в разных местах. Для решения задачи необходимо связать базу данных сотрудников и список с окладами. Существует множество методов соединения двух списков. Более приемлемым в данном случае представляется метод копирования с последующей сортировкой, который ниже будет рассмотрен подробнее.
Итак, в нашем распоряжении имеется два документа:
— рабочий лист, где содержатся список сотрудников и их оклады (присвоим этому листу имя Оклады, а файл, в котором он находится, назовем Штат)’,
— рабочий лист с базой данных сотрудников, рассмотренный в предыдущей главе. Из него мы можем взять информацию о непрерывном стаже работы сотрудника на данном предприятии .
Для соединения двух таблиц откройте две книги, содержащие списки, В книге База на листе Сотрудники выделите столбцы A:U и скопируйте их в буфер обмена. Перейдите в книгу Штат и, выделив столбец G на листе Оклады, произведите вставку из буфера. База данных сотрудников разместится в диапазоне G1:AA11.
ПРИМЕЧАНИЕ

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

Теперь из таблицы необходимо удалить все лишние данные. Для дальнейших расчетов нам понадобится следующая информация из табличной базы данных сотрудников: табельные номера, фамилии, названия отделов, названия должностей, дата приема на работу и, возможно, дата увольнения.
Чтобы удалить ненужные столбцы, сначала следует их выделить (одновременное выделение столбцов можно произвести, удерживая нажатой клавишу [Ctrl]), a затем вызвать контекстное меню и выбрать в нем команду Удалить. Удаление можно выполнить и посредством команды Правка/Удалить.
Лист, который мы получим после вставки фрагмента табличной базы данных и удаления лишних столбцов, изображен на.
Теперь на одном листе электронных таблиц Excel собраны все необходимые нам данные:
— табельные номера из штатного расписания и табельные номера из базы данных сотрудников;
— фамилии из штатного расписания и фамилии из базы данных;
— даты приема на работу — для автоматического расчета стажа работы;
— даты увольнения — для контроля;
— оклады — для расчета премии за выслугу лет.
Однако данные расположены в строках двух таблиц в разной последовательности. Наша следующая задача — отсортировать списки на этом листе таким образом, чтобы они соответствовали друг другу (то есть чтобы фамилии работников из разных списков находились в одной строке).
Сортировка данных
Довольно часто данные требуется представить в соответствии с некоторым заданным критерием: в порядке возрастания, убывания либо в алфавитном порядке. Изменение порядка расположения строк называется сортировкой.
Для выполнения сортировки необходимо поместить курсор в любую ячейку поля, где будет происходить данная операция, и щелкнуть на одной из кнопок сортировки (по возрастанию или по убыванию), расположенных на стандартной панели инструментов.
Выполните сортировку по фамилиям в обоих списках, и вы увидите, что после этого данные, касающиеся одного сотрудника, будут находиться в одной строке.
Иногда возникает необходимость производить сортировку по нескольким столбцам одновременно. В нашем случае, например, может потребоваться отсортировать список сначала по сумме оклада в порядке убывания, а затем по фамилиям, по алфавитному признаку в порядке возрастания.
Используя только кнопки сортировки, вы можете не получить желаемого результата. Для того чтобы произвести одновременную сортировку более чем по одному полю, активизируйте команду Данные/Сортировка. В результате ее выполнения появится диалоговое окно Сортировка диапазона . В раскрывающемся списке Сортировать по выберите столбец и установите переключатель сортировки (по убыванию или по возрастанию). То же нужно сделать и для второго поля.
Сортировка по второму полю (по фамилии) будет производиться только для тех строк, у которых в столбце "Оклад" имеются одинаковые значения. В нашем случае один и тот же размер оклада имеют работники Карпова и Петров. В соответствии с условием второй сортировки в списке сначала будет идти фамилия Карпова, а затем Петров. Полученный в результате такой сортировки список представлен на.
В таблице, показанной на, содержится небольшой объем информации (всего о 10 человеках). Но, как правило, приходится оперировать со значительно большим списком сотрудников, поэтому подход к процессу обработки должен быть таким, чтобы полностью исключить возможность появления и распространения ошибки.
Проверка данных
Взгляните на еще раз, более внимательно. Как видите, в штатном расписании в строке 11 указан сотрудник, который уже уволился. Кроме того, в рабочие листы Оклады и Сотрудники внесены ошибки: в строках 3 и 10 для одного и того же сотрудника указаны различные табельные номера. (Если произвести сортировку по табельным номерам, то в одной строке будут находиться данные, касающиеся конкретного табельного номера.) Наша задача — автоматизировать процессы поиска и исправления этих ошибок.
Формулы, которые мы используем для этой цели, расположим в диапазоне ячеек N2:S11. Они основаны на логических функциях и функциях свойств и значений. Данные функции возвращают логические значения ИСТИНА (при совпадении каких-либо условий) или ЛОЖЬ (при несовпадении таковых).
Если работник уволен
Формула, с помощью которой можно определить, числился ли сотрудник в списке работников на момент расчета премии, основана на функции ЕПУСТО, относящейся к категории Проверка свойств и значений (окно мастера функций приведено на).
Синтаксис функции имеет вид:

ЕПУСТО(значение)

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

=ЕПУСТО(L2)

Заполните такими формулами диапазон ячеек N2:N11 (можете воспользоваться методом копирования). После этого вы увидите, что в ячейке N11 появится значение ЛОЖЬ, а в остальных ячейках — значение ИСТИНА. Следовательно, в столбце "Дата увольнения" введено какое-то значение. Остается только проверить, действительно ли сотрудник Щукина уволена.
Сравнение табельных номеров
Теперь давайте проведем сравнение табельных номеров сотрудников в таблицах и тем самым проверим, нет ли здесь расхождений. Для этого применим функцию ЕСЛИ, которая относится к категории Логические.
С помощью функции ЕСЛИ можно добиться того, что значение в ячейке будет зависеть от выполнения определенных условий. Синтаксис функции имеет вид:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
здесь лог_выражение (первый аргумент) — это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ. Второй аргумент, значение_если_истина, — это значение, которое возвращается, если лог__выраже-ние имеет значение ИСТИНА. Третьим аргументом, значение_если_ложь, является значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Панель функции приведена на.
Таким образом, функция ЕСЛИ возвращает значение, находящееся во втором аргументе, если условие в первом аргументе дает значение ИСТИНА, и значение, находящееся в третьем аргументе, если заданное условие при вычислении дает значение ЛОЖЬ.
В нашем случае в качестве логического выражения необходимо задать операцию сравнения на равенство табельных номеров из разных таблиц (ячейки D2 и G2). Если они одинаковы, то значением результата сравнения в первом аргументе является ИСТИНА, если нет — ЛОЖЬ.
Чтобы продемонстрировать работу функции ЕСЛИ на практике, выделите диапазон ячеек О2:О11, вызовите панель функции ЕСЛИ и задайте в качестве ее аргументов следующие выражения и числовые значения: D2=G2 (1-й аргумент), 1 (2-й аргумент) и 2 (3-й аргумент).
После нажатия комбинации клавиш [Ctrl+Enter] в ячейке О2 появится следующая формула:

=ЕСЛИ(D2=G2;1;2)

Как выглядит лист Оклады после ввода в диапазон О2:О11 указанной формулы, показано на.
Обратите внимание на ячейки ОЗ и О10, в которых находится выделенное жирным шрифтом значение 2, а также на строки 3 и 10. Табельные номера в этих строках не совпадают. Поэтому первый аргумент функции ЕСЛИ при сравнении значений возвратил значение ЛОЖЬ, а ему соответствует значение, внесенное в третий аргумент, то есть число 2. Там же, где табельные номера совпадают, функция возвращает значение второго аргумента — число 1.
Сравнение фамилий
Следующий шаг — сравнение фамилий, находящихся в базе данных сотрудников и в списке с окладами. Однако в базе данных указаны фамилия, имя и отчество, а в штатном расписании — лишь фамилия и инициалы. По этой причине при сравнении будем принимать во внимание только фамилии сотрудников. Чтобы обеспечить такую возможность, мы можем:
— вручную отредактировать каждую ячейку диапазона С2:С11;
— заставить Excel проделать эту работу с помощью формул.
Первый путь нас не устраивает, поэтому воспользуемся специальными текстовыми функциями Excel. С помощью этих функций мы должны:
— сосчитать количество символов в ячейке С2 (где находится фамилия и инициалы сотрудника анализируемой нами строки) до первого пробела;
— извлечь из ячейки С2 количество символов, расположенных слева от первого пробела.
Для определения количества символов, предшествующих первому пробелу, можно применить текстовую функцию НАЙТИ. Синтаксис ее следующий:
НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)
где искомый_текст — текст, который мы ищем, просматриваемый_текст — текст, содержащий искомый текст, нач_позиция — позиция, с которой следует начинать поиск.
Данная функция находит вхождение одной текстовой строки (искомый_текст) в другую (просматриваемый_текст) и начальное положение искомого текста относительно крайнего левого символа просматриваемого текста.
Первый символ в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он считается равным 1.
Вызвав панель функции НАЙТИ , в поле Найти_текст введите с клавиатуры пробел. В поле Внутр_текст введите ссылку на ячейку С2, а третий аргумент оставьте незаполненным. В ячейке Р2 появится следующая формула:

=НАЙТИ (" ";С2)

Применив далее функцию ЛЕВСИМВ

=ЛЕВСИМВ(С2;Р2-1)

вы получите в ячейке Q2 фамилию из ячейки С2, но без инициалов. (Панель функции ЛЕВСИМВ приведена в главе 4, на.)
Первым аргументом функции ЛЕВСИМВ является адрес ячейки, где расположена фамилия с инициалами. Вторым аргументом должно быть число символов, из которого состоит фамилия. Поэтому, если определено положение пробела, следующего после фамилии, то число символов фамилии будет равно номеру позиции этого пробела минус 1. Таким образом, во втором аргументе функции ЛЕВСИМВ из значения ячейки Р2, определенного функцией НАЙТИ, следует вычесть значение 1.
Теперь, используя функцию ЕСЛИ, в ячейке R2 можно сравнить фамилию, находящуюся в базе данных сотрудников, и фамилию из списка с окладами, полученную в результате обработки.
В ячейке R2 должна использоваться такая формула:

=ЕСЛИ(Н2=Q2;ИСТИНА;ЛОЖЬ)

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

И(логическое_значение1; логическое_значение2; ...)

где логическое_значение 1, логическое_значение2, … — это проверяемые условия (их может быть от 1 до 30), которые имеют либо значение ИСТИНА, либо значение ЛОЖЬ.
Аргументы функции должны быть логическими значениями, массивами или ссылками, содержащими логические значения.
В нашем случае результаты сравнений находятся в ячейках N2, О2 и R2. Вызвав панель функции И , введите в качестве ее аргументов ссылки на эти ячейки. В ячейке S2 появится новая формула:

=И(N2;O2;R2)

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