Продавець
Продажу
Белов
>3000
Батурін
>1500
Рис. 10.
В умові фільтрації можна використовувати значення, що повертається формулою. При завданні формул в умовах не використовуйте як заголовок умови заголовки стовпців списку. Уведіть заголовок, що не є заголовком стовпця чи списку залишіть заголовок умови незаповненим. Наприклад, для наступного діапазону умов будуть відображені рядки, у яких значення в стовпці G перевищує середнє значення в осередках E5: E14; заголовок умови не використовується (рис.11.).
=G5>СРЗНАЧ($E$5:$E$14)
Рис.11.
Використовувана в умові формула повинна посилатися або на заголовок стовпця (наприклад, «Продажу»), або на відповідне поле в першому записі. У приведеному прикладі G5 посилається на відповідне поле (стовпець G) першого запису (рядок 5) списку.
Існує декілька засобів пошуку. Можна шукати дані безпосередньо. Для цього треба виконати наступні дії:
1 Виділите діапазон осередків, у якому буде проводитися пошук.
Щоб провести пошук по всьому листі, наведіть покажчик на будь-який осередок і натисніть кнопку миші.
2 В меню Виправлення виберіть команду Знайти.
3 В поле Що введіть чи текст числа, які потрібно знайти.
4 В списку Область пошуку виберіть тип даних, серед яких буде вироблятися пошук.
5 Виберіть команду Знайти далі.
Щоб зупинити пошук, натисніть клавішу ESC.
Можна використовувати деякі вмонтовані функції:
Функція Пошук повертає позицію першого входження чи символу текстового рядка при пошуку ліворуч праворуч. Функція ПОШУК використовується для пошуку входження чи символу рядка тексту в інший рядок тексту, для того щоб застосувати функції ПСТР чи ЗАМІНИТИ для зміни тексту.
Синтаксис:
ПОШУК(шуканий_текст;текст_для_пошуку;нач_позиція)
Шуканий_текст - це шуканий текст. Можна використовувати символи шаблона знак питання (?) і зірочка (*) в аргументі шуканий_текст. Знак питання відповідає будь-якому символу; зірочка відповідає будь-якой послідовності символів. Якщо потрібно знайти власне знак питання чи зірочку, то варто поставити перед ними знак тильда (~). Якщо шуканий_текст не знайдений, то повертається значення помилки #ЗНАЧ!.
Текст_для_пошуку - це текст, у якому потрібно знайти шуканий_текст.
Нач_позиція - це номер символу в тексті текст_для_пошуку (вважаючи ліворуч), з якого варто почати пошук.
Якщо нач_позиція опущена, то передбачається, що вона дорівнює 1.
Якщо нач_позиція не більше 0 чи більше, ніж довжина аргументу текст_для_пошуку, то повертається значення помилки #ЗНАЧ!.
Аргумент нач_позиція можна використовувати, щоб пропустити потрібну кількість символів з лівого краю тексту. Наприклад, припустимо, що Ви працюєте з текстовим рядком "МДС0093.Месячныепродажи". Щоб знайти перше входження "М" в описову частину текстового рядка, укажіть, що нач_позиція дорівнює 8, так що в тій частині тексту, що є серійним номером, пошук вироблятися не буде. Функція ПОШУК починає із символу 8, знаходить шуканий_текст у наступному символі і повертає число 9. Функція ПОШУК завжди повертає номер символу, вважаючи від лівого краю тексту, а не від значення аргументу нач_позиція.
ПОШУК не розрізняє регістра при пошуку (тобто рядкові і заголовні букви не розрізняються).
Функція ПОШУК подібний функції ЗНАЙТИ, за тим виключенням, що функція ЗНАЙТИ працює з урахуванням регістра.
Приклади:
ПОШУК("р";"Оператори";6) дорівнює 8
Якщо осередок B17 містить слово "доход", а осередок A14 містить "Сума Доходів", то:
ПОШУК($B$17;$A$14) дорівнює 7
Функція ПОШУК використовується разом з функцією ЗАМІНИТИ для того, щоб задати функції ЗАМІНИТИ коректне значення аргументу нач_позиція, з яким потрібно вставити новий текст. Використовуючи осередку попереднього приклада, одержимо:
ЗАМІНИТИ($A$14;ПОШУК($B$17;$A$14);7;"Витрат") повертає текст "Сума Витрат".
Функція Знайти знаходить входження одного текстового рядка (шуканий_текст) в інший текстовий рядок ( що, переглядається_текст) і початкову положення початку шуканого тексту щодо крайнього лівого символу тексту, що переглядається. Для пошуку входжень одного текстового рядка в інший текстовий рядок можна використовувати також функцію ПОШУК, але на відміну від функції ПОШУК, функція ЗНАЙТИ враховує регістр і не допускає символів шаблона.
ЗНАЙТИ(шуканий_текст; щопереглядається_текст;нач_позиція)
Шуканий_текст - це шуканий текст.
Якщо шуканий_текст - це "" (порожній рядок), то функція ЗНАЙТИ вважає придатним перший символ у рядку, що переглядається, (тобто поверне значення аргументу нач_ чипозиція 1).
Шуканий_текст не повинний містити ніяких символів шаблона.
Що переглядається_текст - це текст, що містить шуканий текст.
Нач_позиція - це позиція символу, з яким варто починати пошук. Перший символ в аргументі що переглядається_текст має номер 1. Якщо аргумент нач_позиція опущена, то він покладається рівним 1.
Якщо шуканий_текст не входить у що переглядається_текст, то функція ЗНАЙТИ повертає значення помилки #ЗНАЧ!.
Якщо нач_позиція чи менше дорівнює нулю, то функція ЗНАЙТИ повертає значення помилки #ЗНАЧ!.
Якщо нач_позиція більше довжини рядка що переглядається_текст, то функція ЗНАЙТИ повертає значення помилки #ЗНАЧ!.
ЗНАЙТИ("М";"Мадам Смирнова") дорівнює 1
ЗНАЙТИ("м";"Мадам Смирнова") дорівнює 5
ЗНАЙТИ("м";"Мадам Смирнова";6) дорівнює 8
Припустимо, що на робочому листі зберігається список деталей з їхніми серійними номерами, і потрібно виділити список найменувань деталей без серійних номерів. Можна використовувати функцію ЗНАЙТИ для пошуку символу #, а потім функцію ПСТР, щоб виключити серійний номер. Нехай осередку A2:A4 містять наступний список деталей із серійними номерами: "Керамічні ізолятори #124-6745-87", "Мідні котушки #12-671-6772", "Перемінні опори #116010".
ПСТР(A2;1;ЗНАЙТИ(" #";A2;1)-1) повертає "Керамічні ізолятори"
ПСТР(A3;1;ЗНАЙТИ(" #";A3;1)-1) повертає "Мідні котушки"
ПСТР(A4;1;ЗНАЙТИ(" #";A4;1)-1) повертає "Перемінні опори"
Microsoft Query дозволяє одержати зовнішні дані декількома способами. Щоб скористатися цими можливостями і забезпечити необхідний результат, візьміть до уваги наступне:
Щоб створити простий запит, використовуйте майстер запитів. Майстер запитів дозволяє вибирати таблиці і поля. Коли майстер запитів виявляє поле первинного ключа в одній таблиці і полі з таким же ім'ям в іншій таблиці, він автоматично створює внутрішнє об'єднання. Майстер запитів також дозволяє провести просту фільтрацію і сортування діапазону зовнішніх даних перед тим як передати дані в Microsoft Excel. За допомогою майстра запитів можна змінювати раніше створені в ньому запити. Запуск запитів виробляється з Microsoft Excel і Microsoft Query.
Щоб створити більш складний запит, використовуйте Microsoft Query. Цей метод дозволяє звузити таблицю результатів шляхом створення внутрішнього чи зовнішнього об'єднання, а також за допомогою умов і виражень. Вираженнями називаються обчислення, у яких застосовуються оператори, функції, імена полів, константи. Для проведення більш складних фільтраций використовуються умови. Перед відправленням даних у Microsoft Excel переконаєтеся, що таблиця результатів містить потрібні зведення. Запуск запитів виробляється з Microsoft Excel чи Microsoft Query.
Запитом з параметрами називається запит, що при запуску вимагає ввести умови вибірки даних. Створити запит з параметрами можна тільки в Microsoft Query. Умова служить для вибірки даних з таблиць. Запуск запитів виробляється з Microsoft Excel чи Microsoft Query.
1 Створіть запит, що включає таблиці і поля потрібних записів. Не повертайте результати запиту в Microsoft Excel.
2 Переконаєтеся, що кнопка Автоматичний режим не натиснутий.
3 Переконаєтеся, що кнопка Відображення умов не натиснута.
4 Виберіть осередок у рядку Умова і клацніть стрільцю, щоб вибрати зі списку поле, яке варто використовувати як параметр запиту.
5 Виділите перший осередок у рядку Значення. Уведіть [ (відкриваюча квадратна дужка) і текст, що Microsoft Query буде виводити при запуску запиту. Потім уведіть ] (закриваюча квадратна дужка).
Текст повідомлення повинний відрізнятися від імені поля, однак ім'я поля може входити в нього. Більш докладні зведення про типи умов, використовуваних для вибірки даних, можна знайти в довідці по Microsoft Query.
6 Натисніть кнопку ENTER.
7 Щоб запустити запит з Microsoft Query, виберіть Виконати запит .
Щоб передати таблицю результатів у Microsoft Excel, виберіть вихід з поверненням даних.
Зведена таблиця -- це таблиця, що використовується для швидкого підведення підсумків чи об'єднання великих обсягів даних. Змінюючи місцями рядки і стовпці, можна створити нові підсумки вихідних даних; відображаючи різні сторінки можна здійснити фільтрацію даних, а також відобразити детальні дані області.
Зведену таблицю можна створити на основі даних, що знаходяться в списку чи в базі даних Microsoft Excel, декількох аркушах Microsoft Excel, у зовнішній базі даних, а також в іншій зведеній таблиці.
У зведеній таблиці містяться поля, що підводять підсумки вихідних даних у декількох рядках. Перемістивши кнопку поля в інше місце зведеної таблиці, можна змінити представлення даних. Наприклад, можна розмістити імена продавців у рядку чи в стовпці.
Страницы: 1, 2, 3, 4, 5
