Top-office11.ru

IT и мир ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Работа с массивами в Excel

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

  • ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
  • диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
  • номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
  • интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
    • ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
    • ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

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

    Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

    Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

    В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение. Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель».

    Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

    Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

    Также данная функция может искать значения в массивах – =ВПР(1;<2;»Два»:1;»Один»>;2;ЛОЖЬ) – результат выполнения строка «Два».

    Использование функции ПОИСКПОЗ для поиска позиции ячейки с текстовым значением.

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

    В данном диалогом окне три поля:

    • Искомое_значение — здесь указываться значение, позицию которого мы хотим найти в указанном анализируемом диапазоне ячеек. Это может быть текст, число или ссылка на ячейку;
    • Просматриваемый_массив — здесь указывается анализируемый диапазон ячеек, в котором производиться поиск значения, которое указано в поле: Искомое_значение. Это может быть строка или столбец;
    • Тип_сопоставления — здесь можно указать три значения: 0, 1, -1;

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

    1 (один) — меньше — функция будет искать наибольшее значение, которое меньше значения указанного в пункте Искомое_значение. Или равное ему. Значения в анализируемом диапазоне должны быть отсортированы по возрастанию.

    — 1 (минус один) — больше — функция будет искать наименьшее значение, которое больше значения указанного в пункте Искомое_значение. Или равное ему. Значения в анализируемом диапазоне должны быть отсортированы по убыванию.

    ВАЖНО: позиция (порядковый номер) искомого значения в анализируемом диапазоне является относительным, так как функция рассчитывает позицию, отсчитывая порядковый номер от начала анализируемого диапазона ячеек.

    Функция ПОИСКПОЗ в Excel

    • Искомое_значение: Петр;
    • Просматриваемый_массив: В2:В13 — диапазон столбца;
    • Тип_сопоставления: 0точное совпадение.

    Нажимаем ОК.

    Функция ПОИСКПОЗ в Excel

    Функция вернула значение 5. Это значит, что имя Петр находиться в пятой по счету ячейки, в столбце В2:В13. При этом отсчёт видеться от ячейки В2.

    Данный список имен можно разместить в одной строке и применив функцию ПОИСКПОЗ, по такому же алгоритму, найти позицию (номер по порядку) того или иного значения.

    Суммирование ячеек по условию

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

    • Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
    • Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как «>5» . Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие «Иванов» ;
    • Диапазон суммирования – массив значений, которые нужно сложить.

    Для удобной работы с вычислительными операциями в таблицах Excel есть две функции, которые суммируют ячейки, если задать условия: «СУММЕСЛИ» и «СУММЕСЛИМН». Рассмотрим каждую из них более подробно.

    Т еперь несколько типичных примеров работы с массивами
    1. Переворачиваем массив.

    Здесь незнакомая для вас конструкция

    макрос. Во всём коде препроцессор автоматически заменит все вхождения SIZE на 10u.
    2. Удаление элемента, выбранного пользователем.

    Удаление элемента в данном случае, конечно, не происходит. Массив остаётся того же размера, что и раньше. Мы просто затираем удаляемый элемент следующим за ним и выводим SIZE-1 элементов.
    3. Пользователь вводит значения в массив. После этого вывести все разные значения, которые он ввёл.
    Пусть пользователь вводит конечное число элементов, допустим 10. Тогда заранее известно, что всего различных значений будет не более 10. Каждый раз, когда пользователь вводит число будем проходить по массиву и проверять, было ли такое число введено.

    4. Пользователь вводит число — количество измерений (от 2 до 10). После этого вводит все измерения. Программа выдаёт среднее значение, дисперсию, погрешность.

    5. Сортировка массива пузырьком

    6. Перемешаем массив. Воспользуемся для этого алгоритмом Fisher-Yates:
    Для i от N-1 до 1 выбираем случайное число j в пределах от 0 до i и меняем местами i-й и j-й элементы.

    email

    Всё ещё не понятно? – пиши вопросы на ящик

    =INDEX (array, row_num, [col_num]) – английская версия

    =INDEX (array, row_num, [col_num], [area_num]) – английская версия

    =ИНДЕКС(массив; номер_строки; [номер_столбца]) – русская версия

    =ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]) – русская версия

    • array (массив) – диапазон ячеек или массив данных для поиска;
    • row_num (номер_строки) – номер строки, в которой находятся искомые данные;
    • [col_num] ([номер_столбца])(необязательный аргумент) – номер колонки, в которой находятся искомые данные. Этот аргумент необязательный. Но если в аргументах функции не указаны критерии для row_num (номер_строки), необходимо указать аргумент col_num (номер_столбца);
    • [area_num] ([номер_области]) – (необязательный аргумент) – если аргумент массива состоит из нескольких диапазонов, то это число будет использоваться для выбора всех диапазонов.

    Допустим, нам нужно ввести двумерный массив после запуска нашей программы. Для этого мы можем создать программу, которая будет построчно считывать значения нашего массива, а также количество строк в нем. Рассмотрим на примере:

    Запускаем программу и сначала вводим количество строк в массиве (допустим, 3). Далее вводим строки в порядке их очереди. Например:

    После этого данная программа выведет наш двумерный массив: [[1, 1, 1], [1, 1, 1], [1, 1, 1]].

    То же самое можно сделать с помощью генератора двумерных массивов:

    Многомерные массивы

    Как вы уже знаете, элементы массива могут содержать в качестве значения данные любого типа, строки, числа, логические, в том числе и другие массивы. Массив состоящий из других массивов называется многомерным или вложенным массивом. На практике используется 2-3 уровня вложенности для хранения каких-либо связанных структурных данных (например данных о покупателях магазина или каталоге товаров), обработка массивов большей вложенности усложняется и используется не часто.

    Давайте разберёмся как создавать и взаимодействовать с многомерными массивами.

    Создание многомерных массивов

    В первую очередь создадим простой двумерный массив сотрудников организации:

    Массив $multilavelArray содержим в себе три других массива описывающих некоторые параметры сотрудников, а именно имя, возраст и должность. Эти данные могли бы быть получены из БД или сложной веб-формы.

    Обратите внимание что в «родительском» массиве мы не указывали символьных ключей, поэтому массивам внутри были просто присвоены числовые индексы 0, 1, 2. Давайте теперь распределим наших сотрудников по отделам и добавим ещё одного сотрудника.

    Теперь мы имеем многомерный массив в котором на «первом уровне» хранятся отделы компании, а уже внутри отделов сотрудники. При этом отдел имеет символьный ключ, например DEPARTMENT_IT — отдел IT. Немного усложним наш пример и добавим информацию о наименовании отделов и времени работы, а сотрудников отдела поместим на уровень ниже:

    Как видите используя многомерные массивы можно строить довольно сложные структуры данных с которыми в последствии предстоит работать нашему веб-приложению.

    Доступ к элементам многомерного массива

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

    Эта функция простая обёртка для просмотра содержимого переменной при помощи print_r() или var_dump() тегов <pre>. </pre> . Берите на вооружение.

    И так, давайте узнаем имена и возраст всех сотрудников из первого двумерного массива:

    В результате выполнения этого кода, получим такой список в html:
    Сотрудники компании
    Представьте что мы не знаем что содержится в переменной $multilevelArray3 , но нас просят как-то представить структуру компании на странице «О компании». Тут нам пригодится функция print_p() описанная выше. Вызовем следующий код:
    В результате, на экране мы увидим такое сообщение:
    Многомерный массив

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

    Получим такую страницу:
    Структура компании

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

    Обработка многомерного массива

    Один из примеров обработки массива был представлен выше, когда мы просто вывели на экран содержимое многомерного массива в удобном для пользователя виде. Однако это далеко не всё что можно делать с массивами. Например мы можешь осуществить некий поиск по элементам узнать сколько сотрудников старше 30 лет работает в нашей компании:

    Здесь мы применили функцию array_walk_recursive() которая обходит каждый элемент массива вне зависимости от его вложенности и применяет к элементу пользовательскую функцию. В данном случае лямбда функция проверяет что текущий элемент это возраст и что его значение больше 30 и наращивает определённый во вне счётчик. Чтобы получить доступ в область видимости где применён счётчик используется конструкция use и ссылка на переменную &$counter . В результате вы увидите сообщение: Количество сотрудников старше 30 лет: 3.

    Существует множество примеров обработки массивов в PHP, но они выходят за рамки данной статьи.

    Читать еще:  Как перенести фото на карту памяти Самсунг
Ссылка на основную публикацию
Adblock
detector