Загрузить массив в таблицу значений

Загрузить массив в таблицу значений

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

Что такое массив

Представление таблицы в языках программирования называется массивом. Вот, например, таблица, содержащая среднемесячные значения температуры в Перми в 2000 году:

Такую таблицу называют линейной. Она представляет собой последовательность упорядоченных чисел. Для обозначения этих чисел используют индексированные имена. Например, через Т[1] обозначается температура в январе (первом месяце года), Т[5] — температура в мае и т. д.

В программировании линейная таблица называется одномерным массивом. В нашем примере Т — это имя массива. Элементы массива пронумерованы. Порядковый номер элемента называется его индексом. Каждый элемент массива обозначается индексированным именем в следующей форме:

Индекс записывается в квадратных скобках: Т[2], Т[10], Т[12]. Индексы могут представляться не только в виде констант, но и в виде целых переменных и даже выражений целого типа: T[i], T[k], T[i+k], T[2*k]. Важно следить, чтобы значения индексов не выходили за допустимые границы. В примере с температурами они должны лежать в диапазоне от 1 до 12.

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

Массив — это пронумерованная конечная последовательность однотипных величин.

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

Описание и ввод значений в массив на Алгоритмическом языке

Запишем алгоритм ввода значений в массив температур. Сначала посмотрим, как это делается на АЯ. Рассмотрим два варианта алгоритмов на АЯ, использующих разные способы организации цикла.

алг Ввод массива, вариант 1
вещ таб Т[1:12]
цел I
нач I:=1
пока I от до шаг повторять
нц

кц

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

Расчет среднего значения элементов массива

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

Эту задачу легко решить с помощью электронных таблиц. На рис. 3.13 показана такая таблица. В ячейки В2:В13 заносятся значения температур. В ячейку В14 помещается формула: =СРЗНАЧ(В2:В13). Результат вы видите в ячейке В14.

Табличный процессор — это программа, составленная программистами на некотором языке программирования. Вот мы и разберемся, как программируется вычисление среднего значения числового массива, реализованное в функции СРЗНАЧ.

Запишем алгоритм в полном виде (с вводом, вычислениями и выводом), используя в нем для организации циклов структуру цикла с параметром.

алг Средняя температура
вещ таб Т[1:12]
цел I, вещ Tsred
нач
<Цикл ввода>
для I от 1 до 12 шаг 1 повторять
нц
вывод «Т[I», I, «] Среднегодовая температура center»>Вопросы и задания

1. Что такое массив?

2. Самостоятельно придумайте примеры данных, которые можно организовать в виде массива. В каждом примере отметьте: каким именем можно обозначить массив, как пронумеровать его элементы, какой тип будет иметь массив? Опишите массивы по правилам Алгоритмического языка.

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

4. Вы посетили магазин и купили 10 видов товара. В таблицу Т[1:10] вы записали количество купленного товара каждого вида. В таблицу С[1:10] записали цены единиц каждого вида товара соответственно. Составьте алгоритм вычисления общей стоимости всех покупок.

SQL INSERT INTO: примеры вставки строк в таблицу БД MySQL

SQL оператор INSERT используется для вставки записей в существующую таблицу.

Синтаксис этого оператора следующий:

Создадим тестовую таблицу

Давайте создадим таблицу table1 со столбцами a, b, c в нашей MySQL базе данных:

Запрос на вставку строки

Простой запрос, который вставляет строку со столбцами 111, 222 и 333 выглядит так:

Еще один способ сделать то же самое:

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

У таблиц обычно есть поле id с первичным ключом (PRIMARY KEY) таблицы. Если этому полю установлено значение AUTOINCREMENT т.е. оно заполняется автоматически, то в таком случае вы не должны его перечислять в списке столбцов оператора INSERT.

Вставка без перечисления столбцов

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

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

Вставка сразу нескольких строк с помощью INSERT INTO

Если нам нужно вставить несколько строк, то мы просто перечисляем группы значений через запятую выглядит это так:

Таким образом мы вставили 3 строки в нашу таблицу table1. Их может быть и больше. В MySQL четкого предела нет, однако он все таки существует и зависит от параметра max_allowed_packet который ограничивает размер запроса. Если вы установите SET GLOBAL max_allowed_packet=524288000; то размер запроса будет ограничен 500MB но делайте это в очень крайнем случае. Обычно всегда можно найти решение и разделить 1 большой запрос, на несколько более мелких и вставлять например не больше 1000 строк за один цикл.

Как вставить значение из другой таблицы INSERT INTO . SELECT .

Допустим у нас есть еще одна таблица table2 которая по структуре точно такая же как и первая. Нам в таблицу table2 нужно вставить все строки из table1.

Вставляем значения из table1 в таблицу table2:

Вам следует позаботиться об уникальности ключей, если они есть в таблице, в которую мы вставляем. Например при дублировании PRIMARY KEY мы получим следующее сообщение об ошибке:

Если вы делаете не какую-то единичную вставку при переносе данных, а где-то сохраните этот запрос, например в вашем PHP скрипте, то всегда перечисляйте столбцы.

Как не рекомендуется делать (без перечисления столбцов):

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

Либо еще хуже: значения вставятся не в те столбцы.

Вставка из другой таблицы с условием INSERT INTO . SELECT . WHERE .

А теперь представим, что нам нужно вставить только те строки из table1, у которых столбец «c» равен 333. Тогда наш запрос будет выглядеть так

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

Теперь представим, что у нас в таблице table2 — 4 столбца, а в table1 — 3. При этом четвертый столбец в table2 обязательный. Чтобы выйти из этой ситуации, нужно передать какое-нибудь подходящее значение в этот лишний столбец. У нас чисто абстрактная задача, поэтому давайте передадим туда просто единицу.

Теперь в столбец d у нас записалась единица и проблема решена.

Вставка в определенный раздел INSERT INTO . PARTITION .

Если вам нужно вставить строки в определенный раздел таблицы, то нужно после таблицы указать PARTITION (название раздела), например так:

Вставка в несколько разделов. Первая строка вставляется в раздел p1, а вторая в p2

Вставка строк, некоторые из которых уже существуют в целевой таблице

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

Игнорирование INSERT IGNORE INTO

Например если мы вставляем строку с PK = 1, и при этом в таблице уже есть PK = 1 то MySQL выдаст ошибку:

Выполнение запроса на этом прервется, однако нам в некоторых случаях хотелось бы просто вставить данные, игнорируя ошибки. В этом нам поможет INSERT IGNORE INTO:

Просто добавляем IGNORE в наш запрос и ошибки будут игнорироваться

Вставка с заменой существующих значений REPLACE INTO

REPLACE работает также INSERT, но если совпадают уникальные ключи, то старая строка (или строки!) удаляется до вставки новой.

В таком случае наш пример выглядит следующим образом:

Обновление некоторых полей, при существовании строк ON DUPLICATE KEY UPDATE

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

Наш запрос будет выглядеть так:

В данном примере если у нас какой-то уникальный ключ совпадает, то мы не производим вставку, а обновляем существующую строку или строки путем присваивания столбцу «c» значения, которое у нас перечислено в VALUES.

Иными словами, если ключ совпадает, то мы просто обновим данные столбца «с» а остальные столбцы трогать не будем.

Иногда нам нужно при совпадении ключей обновить все значения. Этом можно сделать просто перечислив все столбцы:

При обновлении столбцов мы также можем использовать разные выражения, например:

Выражения для вставляемых значений в VALUES

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

Пример использования выражений:

Таким образом мы для формирования столбца «c» использовали столбцы «a» и «b».

Приоритет вставки INSERT LOW_PRIORITY / HIGH_PRIORITY

Установление приоритета нужно для решение проблем с конкурентными вставками. При вставках происходит блокировка строк и если 2 INSERT запроса требуют блокировки одних и тех же строк, для своего выполнения, то иногда может потребоваться повысить или понизить приоритет некоторых запросов, по отношению к другим. Это можно сделать указав приоритет LOW_PRIORITY или HIGH_PRIORITY

Excel. Преобразование массива в столбец или строку

Если необходимо преобразовать массив в столбец или строку (рис. 1), можно воспользоваться функцией Индекс.

Рис. 1. Массив преобразован в столбец

Скачать статью в формате Word, примеры в формате Excel

Функция ИНДЕКС возвращает значение, хранящееся в ячейке (являющейся элементом массива). Какую ячейку выбрать, функция указывает по горизонтальному и вертикальному номеру ячейки, отсчитываемому от левого верхнего угла массива. Например (рис. 2):

Рис. 2. Пример «работы» функции ИНДЕКС

где A1:G16 – область массива

3 – номер строки массива

5 – номер столбца массива

то есть, индекс задал ячейку Е3

Чтобы легче понять формулу ИНДЕКС, преобразующую массив в столбец, выполним последовательные шаги (см. также лист «Рис. 3» Excel-файла):

1. Функция СТРОКА() возвращает номер строки той ячейки, в которой она забита (рис. 3); столбец I;
2. Функция ЧИСЛСТОЛБ($A$1:$G$16) возвращает число столбцов в массиве;
3. Функция ОКРВВЕРХ(СТРОКА()/ЧИСЛСТОЛБ($A$1:$G$16);1) возвращает номер столбца, ячейки, значение который мы хотим получить, столбец J; дает значение 1 для первых 7 значений (в общем случае, для первых N значений, где N – число столбцов в массиве);
4. Функция ОСТАТ(СТРОКА();ЧИСЛСТОЛБ($A$1:$G$16)+0,0001) возвращает номер от 1 до 7 (в общем случае, от 1 до N, где N – число столбцов в массиве); получается, что идет перебор индексов: сначала первый индекс равен 1 (строка), а второй изменяется от 1 до 7 (столбец); далее строка = 2, а столбец перебирается от 1 до 7 и т.д., пока не пройдемся по всем строкам массива; +0,0001 – это маленькая хитрость; без этой добавочки при делении 7/7 будет получаться 0 в остатке, а нам нужно получить 7; эта формула расположена в столбце K;
5. Функция ИНДЕКС($A$1:$G$16;ОКРВВЕРХ(СТРОКА()/ЧИСЛСТОЛБ($A$1:$G$16);1); ОКРВВЕРХ(ОСТАТ(СТРОКА();ЧИСЛСТОЛБ($A$1:$G$16)+0,0001);1)) возвращает значение из ячейки; см. столбец L
   1. Массив, где ищется значение, выделен желтым
   2. Номер строки внутри массива – зеленым
   3. Номер столбца внутри массива – серым

   В Excel-файле представлено преобразование массива в столбец и строку. При этом приведено два варианта преобразования:

   • Сначала по строкам, затем по столбцам массива
   • Сначала по столбцам, затем по строкам массив

   31 комментарий для “Excel. Преобразование массива в столбец или строку”

   всюду слово возвращает — не понятно значение этого слова в данном контексте. Что возвращает, куда?
   Такое чувство что взяли статью на англ языке и кинули в переводчик сократ. затем выложили.

   Не знаю, что вас так смутило… ? Функция либо принимает значение, либо возвращает значение. Например, введите в Goggle «функция возвращает значение» и получите около 500 млн. ссылок. Функция — это черный ящик, на входе параметры, которые обрабатываются функцией и возвращается результат. Мне не режет слух… ? Статья конечно же оригинальная…

   Отличная статья! Всё ясно и понятно. Давно задавался вопросом транспонирования таблицы, но нужное решение нашлось только сейчас.
   Большое Вам спасибо!
   to Дмитрий.
   Если не шаришь — лучше попытайся разобраться, прежде чем комент писать.

   Таблица значений. Основные приемы работы.

   рубрики: Язык программирования 1С | Дата: 13 июля, 2017
   Скачать обработку с примерами из статьи: ValueTable.epf
   Платформа: 8.3; Тип формы: управляемая.

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

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

   Дата	Наименование товара	Количество
   01.07.2017	Ручка	10
   02.07.2017	Карандаш	7
   03.07.2017	Карандаш	8
   04.07.2017	Лампа	2

   А потом рассмотрим различные приемы работы с ней.

   Ограничение использования

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

   И конечно же мы не сможем вернуть таблицу значений из серверной функции в клиентскую.

   Создание таблицы значений

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

   Обход строк таблицы значений

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

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

   Поиск значения

   Метод Найти(ИскомоеЗначение, [СписокКолонок]) возвращает строку таблицы в которой есть поле с нужным нам значением. Если значение не найдено, возвращается Неопределено. Список колонок в которых будет производиться поиск указывать необязательно, в этом случае поиск будет производиться во всех полях таблицы значений. Данный метод как правило используется для проверки наличия в таблице строки с искомым значением. Например, в момент обхода в цикле какой-то коллекции, мы формируем таблицу значений в которой значения некоторого поля не должны повторяться дважды. Тогда мы с помощью метода Найти() проверяем наличие строки с этим значением, и если получили Неопределено, тогда добавляем новую строку, в противном случае не делаем ничего. Применительно к нашему примеру давайте сделаем поиск строки со значением «Карандаш»:

   В исходной таблице у нас две строки с товаром «Карандаш», но найдена будет только первая из них. Поэтому данный метод не представляет особой ценности, если нам надо получить все строки с заданным значением. Следует понимать, что найденная строка — это по сути ссылка на строку таблицы значений, и если мы проделаем с ее полями какие-либо манипуляции, то это отразится на исходной таблице. Например если выполнить вот такой код:

   то мы обнаружим, что во второй строке нашей исходной таблицы соответствующим образом изменилось количество товара:

   Дата	Наименование товара	Количество
   01.07.2017	Ручка	10
   02.07.2017	Карандаш	100
   03.07.2017	Карандаш	8
   04.07.2017	Лампа	2

   Поиск нескольких строк по условию

   В отличие от предыдущего метода, метод НайтиСтроки(СтруктураПоиска) возвращает не одну, а сразу все строки таблицы значений, которые удовлетворяют условию поиска. Точнее возвращается массив, каждый элемент которого представляет собой ссылку на строку таблицы значений. В качестве параметра метод использует структуру, где ключом является имя колонки, а значением — искомое значение. Удобно использовать, когда нам надо выбрать несколько строк по определенному условию, и проделать с ними какие-то действия. Допустим, нам надо в нашей таблице заменить наименование «Карандаш» на «Карандаш автоматический». В этом случае нам поможет следующий код:

   Структура для поиска может быть и более сложной. Например, мы можем добавить еще одно условие по колонке Дата:

   Удаление строк из таблицы значений

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

   Но в итоге мы наблюдаем следующую картину

   Дата	Наименование товара	Количество
   01.07.2017	Ручка	10
   03.07.2017	Карандаш	8
   04.07.2017	Лампа	2

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

   И наконец попробуем с помощью метода НайтиСтроки() сразу получить все нужные строки, и удалить их поочередно:

   И в результате получаем:

   Дата	Наименование товара	Количество
   01.07.2017	Ручка	10
   04.07.2017	Лампа	2

   То есть такой метод тоже работает.

   Группировка

   Допустим, что нас больше не интересует дата, и мы хотим получить по каждому наименованию общее количество. То есть привести исходную таблицу вот к такому виду:

   Наименование товара	Количество
   Ручка	10
   Карандаш	15
   Лампа	2

   В этом случае нам надо сгруппировать строки таблицы. Напомню, что группировка в языке запросов также активно используется. А в таблице значений для этого используется метод Свернуть(КолонкиГруппировок, КолонкиСуммирования). В общем то, чтобы достигнуть результата нам потребуется всего одна строчка кода:

   Сериализация таблиц значений

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

   Сериализация с использованием XML

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

   Сериализация с использованием JSON

   Начиная с версии платформы 8.3.7.1759 появилась возможность сериализации прикладных типов 1С:Предприятия в JSON. Принцип использования тот же, что и для XML:

   Хочу обратить внимание на параметр НазначениеТипаXML.Явное в методе ЗаписатьJSON без кот
   орого при чтении из строки будет выдаваться ошибка, либо надо будет при чтении указать тип объекта.

   5 основных функции для работы с массивами

   Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

   

   В зависимости от расположения элементов различают массивы:

   • одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
   • двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

   Одномерные массивы бывают:

   • горизонтальными (данные – в строке);
   • вертикальными (данные – в столбце).

   Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

   

   Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

   С помощью формул массива реально:

   • подсчитать количество знаков в определенном диапазоне;
   • суммировать только те числа, которые соответствуют заданному условию;
   • суммировать все n-ные значения в определенном диапазоне.

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

   Классика жанра – товарный чек

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

   1. выделяем ячейку С7
   2. вводим с клавиатуры =СУММ(
   3. выделяем диапазон B2:B5
   4. вводим знак умножения (звездочка)
   5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:
   6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

   Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

   Разрешите Вас… транспонировать?

   При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

   Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

   • Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
   • вводим функцию транспонирования =ТРАНСП(
   • в качестве аргумента функции выделяем наш массив ячеек A1:B8

   жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:

   Функция СТРОКА

   Определяет и возвращает номер строки указанной ссылкой ячейки.

   Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

   =СТРОКА(D4) – результат 4.
   =СТРОКА() – функция вернет номер строки, в которой она расположена.

   Функция СТОЛБЕЦ

   Возвращает номер столбца ячейки, указанной ссылкой.

   Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

   =СТОЛБЕЦ(C4) – формула вернет значение 3.
   =СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

   Функция АДРЕС

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

   Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

   • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
   • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
   • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
     • 1 – значение по умолчанию, когда закреплены все индексы;
     • 2 – закрепление индекса строки;
     • 3 – закрепление индекса столбца;
     • 4 – адрес без закреплений.
     • ИСТИНА – формат ссылок «A1»;
     • ЛОЖЬ – формат ссылок «R1C1».

     =АДРЕС =АДРЕС =АДРЕС =АДРЕС

     Функция ДВССЫЛ

     Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

     Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

     • адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, “C3”, “R3C3” или “D8:D9”.
     • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
       • ИСТИНА – стиль A1. Является значением по умолчанию;
       • ЛОЖЬ – стиль R1C1.

       =ДВССЫЛ(“a3”) – возвращает ссылку на ячейку A3.
       =ДВССЫЛ(“r3c3”) – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
       =ДВССЫЛ(“r3c3”; ЛОЖЬ) – возвращает ссылку на ячейку C3.
       =ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
       Вложение функции ДВССЫЛ со ссылкой на диапазон:

       

       Функция ВЫБОР (CHOOSE)

       Позволит вам выбрать значение из общего списка по указанному номеру позиции:

       =ВЫБОР(2;”Стул”;”Стол”;”Шкаф”;”Диван”)

       Функция ИНДЕКС (INDEX)

       Эта функция возвращает указанное значение из одно- или двумерного диапазона:

       =ИНДЕКС(A1:C6;4;3)

       Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

       =ИНДЕКС(С1:С6;2)

       Функция ПОИСКПОЗ (MATCH)

       Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:

       =ПОИСКПОЗ(B3;B2:B5;0)

       С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A) .

       Редактирование формулы массива

       Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

       Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

       Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

       Описание категории «Ссылки и массивы»

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

       Ярким представителем функций данной категории является самая популярная функция ВПР, которая позволяет найти значение из ячейки диапазона, выполнив поиск в первом столбце этого же диапазона

       

       Принцип работы функции ВПР из категории «Ссылки и массивы»

       Функция ВПР (VLOOKUP) ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.

       Вставляем функцию ВПР:

       

       • Функция ВПР ищет значение ID (104) в крайнем левом столбце диапазона $E$4:$G$7 и возвращает значение из третьего столбца той же строки (так как третий аргумент функции имеет значение 3).
       • Четвёртый аргумент функции равен ЛОЖЬ (FALSE) – это значит, что либо будет найдено точное совпадение, либо будет показано сообщение об ошибке #Н/Д (#N/A).

       Пример применения формулы массива

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

       Как бы мы решали данную задачу стандартным образом?

       Во-первых, мы бы получили итоговую сумму по каждому товару, перемножив количество товара на его цену.

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

       Ту же самую задачу можно решить с помощью простейшей формулы массива.

       Нам нужно получить сумму, поэтому воспользуемся соответствующей функцией СУММ . А вот суммировать мы должны произведение цены товара на его количество, что мы и сделаем – выбираем диапазон значений из столица B и умножаем его на аналогичный диапазон значений столбца C.

       Если сейчас нажать Enter , то появится ошибка.

       Так как мы в формуле использовали диапазоны (массивы) данных, то и формула должна быть формулой массива. Для этого нужно нажать сочетание клавиш Ctrl + Shift + Enter и получим результат.

       Что произошло и как работает формула массива? Это важно понять, так как в дальнейшем можно будет применять формулы массива для решения намного более заковыристых задач…

       Итак, при вычислении формулы массива Excel произвел попарное умножение значений диапазонов B2:B5 и C2:C5. В результате получился массив значений который был просуммирован соответствующей функцией. Мы получили только одно значение итоговой суммы без вспомогательных вычислений.

       Обратите внимание на формулу в строке формул. Она заключена в фигурные скобки, которые указывают нам, что мы имеем дело с формулой массива.

       Это не текстовые скобки, то есть нельзя их ввести с клавиатуры, чтобы сделать формулу формулой массива. Они появляются автоматически при нажатии сочетания клавиш Ctrl + Shift + Enter . Если после создания формулы массива вам необходимо ее отредактировать, то в конце необходимо вновь нажать сочетание клавиш, а не просто клавишу Enter .

       Похожим образом работает и функция ГПР (HLOOKUP):

       

       Функции для работы со ссылками и массивами

       В Excel имеется ряд функций для обработки ссылок и массивов: вычисление номеров строки или столбца таблицы по имени ссылки, определение количества столбцов (строк) ссылки или массива, выбор значения по номеру индекса и т. д.

       Таблица 4.10. Функции для работы со ссылками и массивами.

       АДРЕС	Создает адрес ячейки в виде текста, используя номер строки и номер столбца.
       ВПР	Просматривает левый столбец массива в поисках определенного значения и возвращает значение из указанной ячейки.
       ВЫБОР	Использует номер индекса, чтобы выбрать и вернуть значение из списка аргументов-значений.
       ГИПЕРССЫЛКА	Создание ссылки, открывающей документ, находящийся на жестком диске, сервере сети или Internet.
       ГПР	Просматривает верхнюю строку массива в поисках определенного значения и возвращает значение из указанной ячейки.
       двссыл	Возвращает ссылку, заданную аргументом ссылка на ячейку.
       ИНДЕКС	Выбирает по индексу значение из ссылки или массива.
       ОБЛАСТИ	Возвращает количество областей в ссылке. Область – это интервал смежных ячеек или отдельная ячейка.
       ПОИСКПОЗ	Возвращает относительную позицию элемента массива.
       ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ	Получение данных сводной таблицы
       ПРОСМОТР	Ищет значения в векторе или массиве.
       СМЕЩ	Возвращает ссылку заданной высоты и ширины, отстоящую от другой ссылки на заданное количество строки и столбцов.
       СТОЛБЕЦ	Возвращает номер столбца по заданной ссылке.
       СТРОКА	Возвращает номер строки, определяемой ссылкой.
       ТРАНШ	Возвращает транспонированный массив.
       ЧИСЛСТОЛБ	Возвращает количество столбцов в ссылке или массиве.
       ЧСТРОК	Возвращает количество строк в ссылке или массиве.

       Функция ГПР

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

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

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

       голоса

       Рейтинг статьи