задание excel

Тема: Инструменты Excel для продвинутого пользователя

1. Удаление похожих записей в столбцах Excel

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

1. Создаём новый лист в рабочей книге Excel и называем его.

2. Помещаем список в столбец А.

3. В столбец Е копируем из начала, середины и конца списка 4-5 записей

4. Теперь в столбце С будем сравнивать построчно записи и записывать результат с помощью функции

=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A3;$E$3:$E$1000,0)); "Не найдено"; "Найдено")

Важно, чтобы номера строк при этом совпадали.

Теперь поставьте фильтр на ДУБЛИКАТЫ и почувствуйте удобства выявления похожих записей, которые теперь при необходимости можно удалять, но при условии совпадения строк по номерам.

2. Удаление похожих записей в столбцах Excel на разных листах.

Усложним задачу и представим, что сравниваем записи, находящиеся на разных листах.

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

=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(А3;'удалить похожее задача!'$A$3:'удалить похожее задачаЙ $A$1000;0));"";"Дубликат").

Таким образом, способ сравнения работает даже если записи находятся в разных файлах Excel.

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

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

Создаём новй лист и размещаем структуру нашей таблицы.

Теперь с помощью функции ВПР из категории ссылочных функций указываем цену и наименование канцтовара.

=ВПР(В3:В35;'Q:\#PUBLIC\РИСО_ИОН_2015\Группа 3 инф\[!!!!!СпецификацияКАНЦТОВАРЫ 08-08-15 от 31.03.15.xlsx] Лист1"!$D$7:$F$1771;3;ЛОЖЬ)

Связывая записи с файлом !!!!!Спецификаций КАНЦТОВАРЫ 08-08-15 от 31.03.15, который я вам разместил.

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

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

4. Зависимые списки

Ещё одна полезная задачка связана с возможностью создавать зависимые списки и извлекать записи в соответствии с выбранными данными.

Создадим списки в четырёх столбцах.

 В А сформируем 3 направления подготовки (можно использовать любые наименования), присвоим имя этому столбцу "направления".

B. C и  D заполним произвольными ФИО и присвоим имена каждому столбцу, соответствующие названиям направлений (пробелов в именах не должно быть).

А ячейку А11 запишем выбор направления, а в ячейку С11 - список студентов.

Теперь в ячейке В11 сформируем список из меню Данные - проверка данных и свяжем список с ячейками "направления".

В ячейке D11 проделаем то же самое, но связь организуем через ссылочную функцию ДВССЫЛ с ячейкой В11.

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

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