Как сделать таблицу квадратов в excel одной формулой

Лабораторная работа №3.

• Установка точного значения ширины столбца.
• Работа с Мастером функций.
• Введение понятия «абсолютный адрес»,

ЗАДАНИЕ 10. Необходимо получить таблицу квадратов двузначных чисел,используя Мастер функций и возможности автозаполнения. 1. Назовите Лист1 именем «Квадрат». 2. В ячейку А1 введите текст «Таблица квадратов двузначных чисел». 3. Выровняйте текст по центру выделения ячеек А1:К1. (ГЛАВНАЯ – Выравнивание –Выравнивание—По горизонтали) 4. В ячейки В2:К2 введите числа от 0 до 9 . (Не забудьте – с клавиатуры вводятся только первые 2 числа, остальные цифры последовательности заполняются с помощьюмаркера заполнения.) 5. В ячейки А3:А11 аналогично введите числа от 1 до 9. 6. Выделите столбцыА:К и установите для них одинаковую ширину – 5 символов (ГЛАВНАЯ — Ячейки —Формат—Ширина столбца). Получается следующий вид таблицы:Предполагается, что: А) цифры по вертикали (в столбце А) обозначают число десятковдвузначного числа, Б) цифры по горизонтали (в строке 2) – число единиц двузначного числа. Например, в ячейкеН3(строка 1, столбец 6) подразумеваетсячисло 16, а в ячейке Е11 – число 93. 7. Необходимо в ячейку В3 поместить, формулу, возводящую в квадрат число 10. Для этого воспользуемся Мастером функций. 7.1 Выполните команду «ФОРМУЛЫ – Вставить функцию»7.2Категория функции – МАТЕМАТИЧЕСКИЕ.Имя функции – СТЕПЕНЬ. Нажмите кнопку «ОК«. 7.3. В окне для задания аргументов функции:

• в поле Число введите выражение А3*10+В2 (число десятков * 10 + число единиц).

. Адреса ячеек в выражении формулы нужно вводить щелчком мыши на ячейке

• в поле Степень введите цифру 2 (квадрат – это вторая степень)нажмите «ОК»

Далее нам необходимо каким-то образом указатьMSExcel, что число десятков можнобрать только из столбца А, а число едиництолько из строки 2. При необходимости фиксирования определенных позиций адресов влияющих ячеек необходимо применить абсолютные ссылки (абсолютный адрес). Для создания абсолютной ссылки на ячейку с данными, перед той позицией адреса (строка/столбец), которая не должна изменяться, ставится знак доллара ($). Например: $A1 – фиксирует столбец; A$1 – фиксирует строку; $A$1 – фиксирует столбец и строкуУстановка абсолютного адреса (ссылки):

1. Дважды щелкнуть мышью на ячейке, содержащей формулу.
2. Установить курсор мыши перед закрепляемой позицией.
3. Поставить знак доллара одним из следующих способов:

• Комбинацией клавишShift+4 на латинском регистре
• Однократным нажатием клавиши F4 – полный абсолютный адрес, при копировании фиксируются позиции и строки, и столбца (Например, $A$1)
• Двукратным нажатием F4 — неполный абсолютный адрес, закреплена (при копировании не меняется) позиция строки (Например, C$3)
• Трехкратным нажатием F4 — неполный абсолютный адрес, закреплена (при копировании не меняется) позиция столбца (Например, $B1)
• Четырехкратное нажатие F4 превращает адрес ячейки в относительный (А1)

8. Щелкните дважды на ячейке В3 и приведите формулу с помощью клавиши F4 к следующему виду =СТЕПЕНЬ($A3*10+B$2;2).9. Распространим действие формулы из ячейки В3 на остальные ячейки таблицы, для этого заполните формулой свободные ячейки таблицы с помощью маркера заполнения. 10. Оформите таблицу: заголовок, границы, заливка отдельных ячеек по образцу. 11. Используя ФОРМУЛЫ – Влияющие ячейки проверьте правильность произведенных расчетов. ЗАДАНИЕ 11. Знакомство с категорией статистических функцийMSExcel, использованиеабсолютного адреса. Повторение приемов форматирования таблиц, измененияформатов ячейки, построения диаграмм 1. Назовите новый лист именем «Налог». 2. Создайте таблицу, приведенную ниже: 2.1 При оформлении первых трех строк используйте параметр горизонтального выравнивания «По центру выделения» (Главная – Ячейки – Формат – Формат ячеек –Выравнивание) 2.2 Для заполнения ячеек с заголовками столбцов используйте автоподстановкумаркером заполнения.2.3 Ячейки с заголовками столбцов таблицы выровняйте по горизонтали и вертикали «По центру» и примените отображение «Переносить по словам» или для более точного переноса слов используйте Alt — Enter 2.4 Ширина столбца B = 30 символов, для остальных– 12 символов (Главная – Ячейки – Формат –Ширина столбца…). 2.5 Параметры шрифта для всей таблицы: TimesNewRoman, обычный, 12. 2.6 Введите данные и установите внешние и внутренние границы созданной таблицы (Главная – Ячейки -Формат – Формат ячеек –Граница)

1. Произведите расчет показателей:
   1. Итоговые суммы рассчитайте с помощью функции Автосумма — кнопка∑
   2. Средние значения рассчитайте с помощью Мастера функций (Вставка – Функция):
      1. Шаг 1. Категория — Статистические, Функция – СРЗНАЧ.
      2. Шаг 2. Укажите мышью диапазон ячеек для расчета среднего:

• C6:E6 для подсчета средней суммы по району

• C6:E13 для расчета среднего значения по городу

1. При расчете доли необходимо:

• В столбце F получены итоговые результаты по каждому из районов и в целом по городу.
• Всего по городу составляет 100%. Определяем долю каждого района составлением пропорции:

2768,5 – 100% 420 — Х(%) Следовательно

• При применении процентного формата ячеек MS Excel автоматически производит умножениесодержимого ячейки на 100и добавляет знак %.
• Поэтому в ячейке G6 устанавливаем процентный формат

(Главная – Ячейки – Формат – Формат ячеек .. — Число –Процентный); И вводим формулу =F6/F14

• Полученную формулу нельзя распространить вниз в диапазоне G6:G14. т.к. в ней должны меняться цифры по районам (числитель), а знаменатель (Всего по городу) должен оставаться без изменений. Поэтому необходимо использовать абсолютный адрес. В ссылке на адрес ячейки F14 нужно закрепить позицию строки=F6/F$14
• Вернитесь в ячейку G6, возьмитесь за маркер заполнения и заполните полученной формулой диапазонG6:G14.
• Определите влияющие на формулу ячейки

В результате таблица примет следующий вид:

1. Постройте диаграмму:

• круговую,
• отображающую итоговые данные сбора налогов по районам города

(Исходными данными являются ячейки столбца B и столбца G.)

• снабженную заголовком, легендой и подписями данных (доля)
• размещенную на отдельном листе с именем «Всего налогов».

Диаграмма должна выглядеть примерно так: ЗАДАНИЕ 12. Повторение применения абсолютных ссылок, автозаполнение диапазона ячеек. 1. Назовите новый лист именем «Сводка продаж». 2. Создайте следующую таблицу: Указания по оформлению таблицы:

• Слово «Дата» введите в ячейку А2 и выровняйте по центру диапазона ячеек А2:В2
• В ячейку С2 введите дату в формате 10 февраля 2008 г. Выровняйте дату по центру диапазона ячеек С2:Е2
• Ячейки В4:В6 содержат цены в денежном формате и обозначениями $ Английский (США)
• К содержимому ячеек D4:D8 примените денежный формат и обозначение р.
• К содержимому ячеек Е4:Е6 примените процентный формат в 2-мя десятичными знаками
• Для ячеек А2:А6, В2:Е2, С7 примените отображение «Переносить по словам».
• Для всей таблицы настройте внешние и внутренние границы
• Ячейку D7 залейте произвольным цветом.

1. Определите сумму в руб. для одного дня.:

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

Предъявите преподавателю конструкцию формулы в ячейке D4. Если формула построена правильно, то:

• Распространите ее с помощью маркера заполнения в ячейкиD5:D6.
• Дневную выручку получите в ячейке D7 с помощью автосуммирования.

1. Рассчитайте % от дневной выручки в ячейке Е4 (см. расчет доли налогов по району в задании 11.). При построении формулы учитывайте необходимость закрепления позиции строки7.

• Если формула верна, распространите ее в ячейки Е5:Е6 с помощью маркера заполнения.

После проведения вычислений таблица должна иметь следующий вид:

1. Получите сводку продаж на 11 и 12 февраля. Для этого:

• Выделите диапазон ячеек С2:Е8
• Распространите диапазон с помощью маркера заполнения на 6 столбцов вправо.

Обратите внимание, что:

• Дата продажи автоматически увеличивается на 1 день
• Курс у.е. автоматически увеличивается ежедневно на 1 рубль.

• Произвольно измените курс у.е., прибавив или убавив несколько копеек.
• Произвольно измените данные за 11 и 12 февраля в столбцах «Продано за день»

В результате таблица должна иметь примерно такой вид: Проверьте влияющие ячейки (в меню Формулы). Результаты работы предъявите преподавателю

формула таблицы квадратов двузначных чисел в экселе

одной формулой незнаю.. . рецепт: по горизоптали пишешь1 и 2 далее растягиваешь скок надо, по вертикали тоже самое, затем в ячейке 2В пишешь =B$1*$A2 и растягиваешь и вниз и в сторону.. .

тфу ты таблицу умножения написал )))

а квадратов вот что надо вписать: =(B$1&$A2)^2

Источник: ))))
Остальные ответы

В ячейке А1 пишешь 10, в А2 — 11, потом выбираешь обе и растягиваешь вниз до 99.
В ячейке В1 пишешь =А1^2, выбираешь её и тянешь вниз до 99.

Похожие вопросы
Ваш браузер устарел

Мы постоянно добавляем новый функционал в основной интерфейс проекта. К сожалению, старые браузеры не в состоянии качественно работать с современными программными продуктами. Для корректной работы используйте последние версии браузеров Chrome, Mozilla Firefox, Opera, Microsoft Edge или установите браузер Atom.

Использование формул массива: рекомендации и примеры

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

Начиная с обновления для Microsoft 365 за сентябрь 2018 г., любая формула, которая может возвращать несколько результатов, автоматически переносит их вниз или в соседние ячейки. Это изменение поведения также сопровождается несколькими новыми функциями динамического массива. Формулы динамического массива, независимо от того, используют ли они существующие функции или функции динамического массива, необходимо вводить только в одну ячейку, а затем подтверждать, нажав ВВОД. Ранее в устаревших формулах массива сначала требовалось выбрать весь выходной диапазон, а затем подтвердить формулу с помощью CTRL+SHIFT+ВВОД. Их часто называют формулами CSE.

Формулы массива позволяют выполнять сложные задачи, например следующие:

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

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

Скачивание примеров

Массивы с несколькими ячейками и одной ячейкой

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

• Формула массива с несколькими ячейками
• Здесь мы вычисляем совокупный объем продаж купе и седанов для каждого продавца, вводя =F10:F19*G10:G19 в ячейку H10. При нажатии клавиши ВВОД вы увидите, как результаты распределяются по ячейкам H10:H19. Обратите внимание, что диапазон переноса выделяется границей при выделении любой ячейки в пределах диапазона. Вы также можете заметить, что формулы в ячейках H10:H19 неактивны. Они нужны только для справки, поэтому, если вы хотите изменить формулу, вам нужно выбрать ячейку H10, в которой находится основная формула.
• Формула массива с одной ячейкойВ ячейке H20 образца книги введите или скопируйте и вставьте =СУММ(F10:F19*G10:G19), а затем нажмите ВВОД. В этом случае будет выполнено перемножение значений массива (диапазон ячеек F10–G19), а затем — при помощи функции СУММ — сложение полученных результатов. Общий итог продаж составит 1 590 000 рублей. В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 1000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке, а не перетаскивать формулу на 1000 строк ниже. Кроме того, обратите внимание на то, что формула с одной ячейкой в ячейке H20 полностью независима от формулы с несколькими ячейками (формула в ячейках H10–H19). Это указывает на еще одно преимущество использования формул массива — их гибкость. Можно изменить формулы в столбце H, и это не повлияет на формулу в ячейке H20. Также может быть полезно иметь независимые итоги, как это, так как это помогает проверить точность результатов.
• Ниже перечислены дополнительные преимущества, которые обеспечивает использование формул динамического массива.
  • Согласованность Если щелкнуть несколько ячеек ниже ячейки H10, вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.
  • Безопасность Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку H11 и нажмите клавишу DELETE. Excel не будет изменять выходные данные массива. Чтобы их изменить, необходимо выбрать левую верхнюю ячейку массива или ячейку H10.
  • Меньший размер файлов Зачастую вместо нескольких промежуточных формул можно использовать одну формулу массива. Например, в примере с продажей автомобилей для вычисления результатов в столбце E используется одна формула массива. Если бы вы прибегли к обычным формулам (таким как =F10*G10, F11*G11, F12*G12 и т. д.), вам понадобилось бы 11 разных формул для получения таких же результатов. Это не очень важно, но что, если вам необходимо суммировать тысячи строк? Тогда это может иметь большое значение.
  • Эффективность Функции массива могут быть эффективным способом создания сложных формул. Формула массива =СУММ(F10:F19*G10:G19) та же: =СУММ(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
  • Перенос Динамические формулы массива будут автоматически перенесены в диапазон вывода. Если исходные данные хранятся в таблице Excel, тогда формулы динамического массива будут автоматически изменять размер при добавлении и удалении данных.
  • Ошибка #ПЕРЕНОС! Динамические массивы ввели Ошибка #ПЕРЕНОС!, что означает, что предполагаемый диапазон переноса по какой-либо причине заблокирован. При устранении блока формула будет автоматически перенесены.

Создание одной и двухмерных констант массива

Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок (< >) вокруг списка, следующим образом:

Если элементы списка разделяются точками с запятой, будет создан горизонтальный массив (строка). Если элементы разделяются двоеточиями, будет создан вертикальный массив (столбец). Чтобы создать двумерный массив, следует разделить элементы строк точками с запятой, а строки — двоеточиями.

В описанных ниже процедурах вы потренируетесь создавать горизонтальные, вертикальные и двумерные константы. Мы покажем примеры использования функции ПОСЛЕД для автоматического создания констант массива, а также для введенных вручную констант массива.

• Создание горизонтальной константы Воспользуйтесь книгой из предыдущих примеров или создайте новую книгу. Выберете любую пустую ячейку и введите =ПОСЛЕД(1,5). Функция ПОСЛЕД создает массив из 1 строки на 5 столбцов, как = . Будет отображен следующий результат: Вы также можете ввести: или=, но обратите внимание, где вы ставите точки с запятой вместо запятых. Как видите, параметр ПОСЛЕД имеет значительные преимущества по сравнению с вводом значений констант массива вручную. В основном это экономит время, но также помогает сократить количество ошибок, возникающих при вводе вручную. Его также проще читать, особенно потому, что точки с запятой трудно отличить от разделителей запятой.

Синтаксис константы массива

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

В ячейке D9 мы ввели =ПОСЛЕД(1,5,3,1), но вы также можете ввести 3, 4, 5, 6 и 7 в ячейки A9:H9. В этом конкретном выборе числа нет ничего особенного, мы просто выбрали что-то другое, кроме 1-5.

В ячейке E11 введите =СУММ(D9:H9*ПОСЛЕДОВАТЕЛЬНОСТЬ(1,5)) или =СУММ(D9:H9*). Формулы возвращают 85.

Функция ПОСЛЕД создает эквивалент константы массива . Поскольку Excel в первую очередь выполняется операции с элементами, заключенными в скобки, далее будут использоваться значения ячеек в D9: H9 и оператор умножения (*). На этом этапе формула выполнит умножение значений в сохраненном массиве на соответствующие значения в константе. Это эквивалент следующей формулы:

=СУММ(D9*1;E9*2,F9*3,G9*4;H9*5) или =СУММ(3*1,4*2,5*3,6*4,7*5)

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

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

Элементы, которые можно использовать в константах массива

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

Именование констант массива

Один из лучших способов использовать константы массива — присвоить им имена. С именованными константами проще работать, и они позволяют частично скрыть сложность формул массива от других пользователей. Чтобы присвоить имя константе массива, а затем использовать ее в формуле, сделайте следующее.

Выберите Формулы > Присвоить имена > Определить имена. В поле Имя введите Квартал 1. В поле Диапазон введите следующую константу (не забудьте ввести скобки вручную):

Диалоговое окно должно выглядеть следующим образом:

Нажмите кнопку ОК, выберите любую строку с тремя пустыми ячейками и введите =Квартал1.

Будет отображен следующий результат:

» />

Если вы хотите, чтобы результаты переносились по вертикали, а не по горизонтали, можно использовать =ТРАНСП(Квартал1).

Если вы хотите отобразить список из 12 месяцев, например, который вы можете использовать при создании финансового отчета, вы можете использовать функцию ПОСЛЕД в качестве основы для текущего года. Отличительной особенностью этой функции является то, что, несмотря на то, что отображается только месяц, за ним стоит допустимая дата, которую можно использовать в других вычислениях. Эти примеры можно найти на листах Константа именованного массива и Быстрый образец набора данных в книге примера.

=ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()),ПОСЛЕД(1,12),1),»ммм»)

При этом функция ДАТА используется для создания даты на основе текущего года, функция ПОСЛЕД создает константу массива от 1 до 12 с января по декабрь, затем функция ТЕКСТ преобразует формат отображения в «ммм» (январь, февраль, март и т..д.). Если вы хотите отобразить полное название месяца, например «Январь», используйте «мммм».

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

Использование констант массива

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

• Умножение каждого из элементов массива Введите =ПОСЛЕД(1,12)*2 или =*2 Вы также можете выполнить деление с помощью (/), сложить с помощью (+) и вычесть с помощью (—).
• Возведение в квадрат элементов массива Введите =ПОСЛЕД(1,12)^2 или =^2
• Поиск квадратного корня из квадрата элементов массива Введите =КОРЕНЬ(ПОСЛЕД(1,12)^2) или =КОРЕНЬ(^2)
• Транспонирование одномерной строки Введите =ТРАНСП(ПОСЛЕД(1,5)) или =ТРАНСП() Даже если была введена горизонтальная константа массива, функция ТРАНСП преобразует константу массива в столбец.
• Транспонирование одномерного столбца Введите =ТРАНСП(ПОСЛЕД(5,1)) или =ТРАНСП() Даже если была введена вертикальная константа массива, функция ТРАНСП преобразует константу массива в строку.
• Транспонирование двумерного массива Введите =ТРАНСП(ПОСЛЕД(3,4)) или =ТРАНСП() Функция ТРАНСП преобразует каждую из строк в последовательность столбцов.

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

В данном разделе приводятся примеры использования основных формул массива.

• Создание массива на основе существующих значений В следующем примере объясняется, как использовать формулы массива для создания нового массива из существующего массива. Введите =ПОСЛЕД(3,6,10,10) или = Не забудьте ввести < (открывающую фигурную скобку) перед числом 10 и>(закрывающую фигурную скобку) после числа 180, так как вы создаете массив чисел. Затем введите =D9# или =D9:I11 в пустую ячейку. Появится массив ячеек 3 x 6 с одинаковыми значениями, которые отображаются в D9:D11. Знак # называется оператором диапазона переноса, и это способ ссылки Excel на весь диапазон массива вместо того, чтобы вводить его.
• Создание константы массива на основе существующих значений Вы можете получить результаты формулы перенесенного массива и преобразовать его в компоненты. Выберите ячейку D9 и нажмите клавишу F2, чтобы переключиться в режим правки. Затем нажмите клавишу F9, чтобы преобразовать ссылки на ячейки в значения, которые Excel преобразует в константу массива. При нажатии клавиши ВВОД формула =D9# теперь должна быть =.
• Подсчет знаков в диапазоне ячеек В примере ниже демонстрируется, как подсчитать число знаков в диапазоне ячеек. К ним относятся пробелы. =СУММ(LEN(C9:C13)) В данном случае функция ДЛСТР возвращает длину каждой текстовой строки в каждой из ячеек диапазона. Затем функция СУММ складывает эти значения и выводит полученный результат (66). Если вы хотите получить среднее количество символов, можно использовать: =СРЗНАЧ(ДЛСТР(C9:C13))
• Содержимое самой длинной ячейки в диапазоне C9:C13=ИНДЕКС(C9:C13;ПОИСКПОЗ(МАКС(ДЛСТР(C9:C13)),ДЛСТР(C9:C13);0);1) Эта формула работает только в том случае, если диапазон данных содержит один столбец ячеек. Рассмотрим эту формулу подробнее, начиная с элементов в скобках. Функция ДЛСТР возвращает длину каждого элемента в диапазон ячеек D2:D6. Функция МАКС вычисляет наибольшее значение среди этих элементов, соответствующее самой длинной текстовой строке, которая находится в ячейке D3. Следующие функции немного сложнее. Функция ПОИСКПОЗ вычисляет смещение (относительную позицию) ячейки, которая содержит строку текста максимальной длины. Для этого требуется три аргумента: искомое значение, массив для поиска и тип соответствия. Функция ПОИСКПОЗ выполняет поиск указанного искомого значения в массиве поиска. В данном случае искомое значение представляет собой строку максимальной длины: МАКС(ДЛСТР(C9:C13) из следующего массива: ДЛСТР(C9:C13) Аргумент типа соответствия в этом случае равен 0. Тип соответствия может иметь значение 1, 0 или -1.
  • Значение 1 возвращает наибольшее значение, которое не превышает искомое значение.
  • Значение 0 возвращает первое значение, равное искомому.
  • Значение -1 возвращает наименьшее из значений, которые превышают искомое значение или равно ему
  • Если тип соответствия не указан, предполагается, что он равен 1.

  Наконец, функция ИНДЕКС имеет следующие аргументы: массив, номер строки и номер столбца в этом массиве. Массив образуется диапазоном ячеек C9:C13, функция ПОИСКПОЗ задает адрес ячейки, а последний аргумент (1) указывает, что значение должно быть найдено в первом столбце массива.

  Если вы хотите получить содержимое из наименьшей текстовой строки, замените значение МАКС в примере выше на МИН.

  

• Поиск n наименьших значений в диапазоне В этом примере показано, как найти три наименьших значения в диапазоне ячеек, где массив образцов данных в ячейках B9:B18 был создан с помощью: =ЦЕЛОЕ(СЛМАССИВ(10,1)*100). Обратите внимание, что ФУНКЦИЯ СЛМАСИМВ — это переменная функция, поэтому вы будете получать новый набор случайных чисел при каждом вычислении в Excel. Введите =НАИМЕНЬШИЙ(B9#,ПОСЛЕД(D9), =НАИМЕНЬШИЙ(B9:B18,) В этой формуле константа массива используется для трехкратной оценки функции НАИМЕНЬШИЙ и возврата 3 наименьших членов массива, содержащихся в ячейках B9:B18, где 3 — это переменное значение в ячейке D9. Чтобы найти дополнительные значения, можно увеличить значение в функции ПОСЛЕД или добавить дополнительные аргументы в константу. С этой формулой также используются дополнительные функции, например СУММ или СРЗНАЧ. Например: =СУММ(НАИМЕНЬШИЙ(B9#;ПОСЛЕД(D9))=СРЕДНИЙ(НАИМЕНЬШИЙ(B9#;ПОСЛЕД(D9))
• Поиск n наибольших значений в диапазоне Чтобы найти наибольшие значения в диапазоне, следует заменить функцию НАИМЕНЬШИЙ функцией НАИБОЛЬШИЙ. Кроме того, в следующем примере используются функции СТРОКА и ДВССЫЛ. Введите =НАИБОЛЬШИЙ(B9#,СТРОКА(ДВССЫЛ(«1:3»))) или =НАИБОЛЬШИЙ(B9:B18,СТРОКА(ДВССЫЛ(«1:3»))) На этом этапе может быть полезно ознакомиться с функциями СТРОКА и ДВССЫЛ. При помощи функции СТРОКА можно создать массив последовательных целых чисел. Например, выберите пустое и введите: =СТРОКА(1:10) В результате выполнения этой формулы будет создан столбец, содержащий 10 последовательных целочисленных значений. Чтобы понять, где может возникнуть проблема, вставьте строку над диапазоном, содержащим формулу массива (то есть над строкой 1). Ссылки на строки будут изменены и в результате выполнения формулы теперь вы получите числа от 2 до 11. Чтобы избежать возникновения этой проблемы, следует добавить в формулу функцию ДВССЫЛ. =СТРОКА(ДВССЫЛ(«1:10»)) В функции ДВССЫЛ в качестве аргументов используются текстовые строки (именно поэтому диапазон 1:10 заключен в кавычки). Текстовые значения не изменяются при вставке строк или перемещении формулы массива. В результате при выполнении функции СТРОКА всегда будет создаваться нужный массив целочисленных значений. Вы можете так же легко использовать функцию ПОСЛЕД: =ПОСЛЕД(10) Рассмотрим формулу, которую вы использовали ранее — =НАИБОЛЬШИЙ(B9#,СТРОКА(ДВССЫЛ(«1:3»))), начиная с внутренних скобок и работая наружу: функция ДВССЫЛ возвращает набор текстовых значений, в данном случае значения от 1 до 3. Функция СТРОКА, в свою очередь, создает массив столбцов из трех ячеек. Функция НАИБОЛЬШИЙ использует значения в диапазоне ячеей B9:B18 и вычисляется три раза для каждой ссылки, возвращаемой функцией СТРОКА. Если вы хотите найти дополнительные значения, добавьте к функции ДВССЫЛ более широкий диапазон ячеек. Как и в примерах с функцией НАИМЕНЬШИЙ, эту формулу можно использовать и с другими функциями, такими как СУММ и СРЗНАЧ.

Работа с ошибками

• Суммирование диапазона, который содержит значения ошибки При попытке просуммировать диапазон, содержащий значения ошибки (например, #ЗНАЧЕН), функция СУММ в Excel не работает. или #Н/Д. В приведенном ниже примере демонстрируется, как просуммировать значения в диапазоне «Данные», который содержит ошибки:
• =СУММ(ЕСЛИ(ЕОШИБКА(Данные);»»;Данные)) Формула создает новый массив, содержащий исходные значения за вычетом любых значений ошибок. Начиная с внутренних функций и работы наружу, функция ЕОШИБКА выполняет поиск ошибок в диапазоне ячеей (Данные). Функция ЕСЛИ возвращает определенное значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ. В этом случае он возвращает пустые строки («») для всех значений ошибок, так как они оцениваются как ИСТИНА, и возвращает оставшиеся значения из диапазона (Данные), так как они оцениваются как ЛОЖЬ, то есть они не содержат значений ошибок. Функция СУММ затем вычисляет итог для отфильтрованного массива.
• Подсчет количества значений ошибки в диапазоне Формула в этом примере похожа на формулу предыдущего примера, однако она возвращает количество значений ошибки в диапазоне с именем «Данные» вместо исключения таких значений. =СУММ(ЕСЛИ(ЕОШИБКА(Данные);1;0)) В результате выполнения этой формулы создается массив, в котором для ячеек со значениями ошибки указывается значение 1, а для других ячеек — значение 0. Для получения аналогичного результата указанную формулу можно упростить, удалив третий аргумент функции ЕСЛИ следующим образом. =СУММ(ЕСЛИ(ЕОШИБКА(Данные);1)) Если этот аргумент не задан и ячейка не содержит значения ошибки, функция ЕСЛИ возвращает значение ЛОЖЬ. Формулу можно упростить еще больше: =СУММ(ЕСЛИ(ЕОШИБКА(Данные)*1)) Такой вариант формулы допустим, поскольку ИСТИНА*1=1 и ЛОЖЬ*1=0.

Суммирование значений с учетом условий

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

Например, в указанной ниже формуле массива суммируются только положительные целые числа в диапазоне с именем «Продажи», который представляет ячейки E9:E24 в приведенном выше примере:

=СУММ(ЕСЛИ(Продажи>0;Продажи))

В результате выполнения функции ЕСЛИ создается массив положительных и ложных значений. Функция СУММ будет игнорировать ложные значения, поскольку 0+0=0. Используемый в этой формуле диапазон ячеек может включать любое количество строк и столбцов.

Можно также выполнить суммирование значений с учетом нескольких условий. Например, эта формула массива вычисляет значения больше 0 И меньше 2500:

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

Доступно создание формул массива с использованием условия типа ИЛИ. Например, можно суммировать значения, которые больше 0 ИЛИ меньше 2500:

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

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

=СРЗНАЧ(ЕСЛИ(Продажи<>0,Продажи))

В результате выполнения функции ЕСЛИ создается массив значений, не равных 0, которые затем передаются в функцию СРЗНАЧ.

Подсчет количества различающихся ячеек в двух диапазонах

В данной формуле массива сравниваются значения в двух диапазонах ячеек с именами МоиДанные и ДругиеДанные, в результате чего возвращается количество различающихся ячеек между этими двумя диапазонами. Если содержимое двух диапазонов идентично, формула возвращает 0. Эту формулу можно использовать только для диапазонов ячеек с одинаковым размером и одинаковой размерностью. Например, если МоиДанные — это диапазон из 3 строк и 5 столбцов, то диапазон ДругиеДанные тоже должен состоять из 3 строк и 5 столбцов:

=СУММ(ЕСЛИ(МоиДанные=ДругиеДанные;0;1))

В результате выполнения формулы создается массив, имеющий такой же размер, что и сравниваемые диапазоны. Функция ЕСЛИ заполняет массив значениями 0 и 1 (0 для ячеек с различными значениями, 1 — для ячеек с одинаковыми значениями). Затем функция СУММ возвращает сумму значений в массиве.

Эту формулу можно упростить следующим образом:

=СУММ(1*(МоиДанные<>ДругиеДанные))

Подобно формуле, подсчитывающей количество значений ошибки в диапазоне, эта формула работает благодаря тому, что ИСТИНА*1=1, а ЛОЖЬ*1=0.

Следующая формула массива возвращает номер строки максимального значения в диапазоне с именем «Данные», состоящем из одного столбца:

=МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);»»))

В результате выполнения функции ЕСЛИ создается новый массив, соответствующий диапазону с именем «Данные». Если соответствующая ячейка содержит максимальное значение в диапазоне, массив будет содержать номер этой строки. В обратном случае массив содержит пустую строку («»). Функция МИН использует полученный массив в качестве своего второго аргумента и возвращает наименьшее значение, соответствующее номеру строки с максимальным значением в диапазоне с именем «Данные». Если диапазон «Данные» содержит одинаковые максимальные значения, формула возвращает строку первого значения.

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

=АДРЕС(МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);»»));СТОЛБЕЦ(Данные))

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

Формулы массива с несколькими ячейками и одной ячейкой

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

• Формула массива с несколькими ячейками

Скопируйте всю таблицу, приведенную ниже, и вставьте ее на пустой лист книги, начиная с ячейки A1.

Тип автомобиля

Число проданных единиц

Цена за единицу

Итоги продаж

СРЗНАЧ (функция СРЗНАЧ)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции С AVERAGE в Microsoft Excel.

Описание

Возвращает среднее арифметическое аргументов. Например, если диапазон A1:A20 содержит числа, формула =СПБ(A1:A20) возвращает среднее из этих чисел.

Синтаксис

Аргументы функции СРЗНАЧ описаны ниже.

• Число1 Обязательный аргумент. Первое число, ссылка на ячейку или диапазон, для которого требуется вычислить среднее значение.
• Число2. Необязательный. Дополнительные числа, ссылки на ячейки или диапазоны, для которых нужно вычесть среднее значение, не более 255.

Замечания

• Аргументы могут быть числами, именами или ссылками на диапазоны или ячейки, содержащие числа.
• Логические значения и текстовые представления чисел, которые непосредственно введите в список аргументов, не учитываются.
• Если аргумент является ссылкой на диапазон или ячейку, содержащую текст или логические значения, или ссылкой на пустую ячейку, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.
• Аргументы, являющиеся значениями ошибок или текстом, которые не могут быть преобразованы в числа, вызывают ошибки.
• Если логические значения и текстовые представления чисел необходимо учитывать в расчетах, используйте функцию СРЗНАЧА.
• Если требуется вычислить среднее значение только для тех значений, которые удовлетворяют определенным критериям, используйте функцию СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН.

Примечание: Функция СРЗНАЧ вычисляет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения, описанных ниже.

• Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
• Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
• Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

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

Совет: При вычислении средних значений ячеек следует учитывать различие между пустыми ячейками и ячейками, содержащими нулевые значения, особенно если в диалоговом окне Параметры Excel настольного приложения Excel снят флажок Показывать нули в ячейках, которые содержат нулевые значения. Если этот флажок установлен, пустые ячейки игнорируются, но нулевые значения учитываются.

Местонахождение флажка Показывать нули в ячейках, которые содержат нулевые значения

• Откройте вкладку Файл , а затем нажмите кнопку Параметры и в категории Дополнительно найдите группу Показать параметры для следующего листа.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Похожие публикации:

1. Как записать образ на диск
2. Как обновить ани деск на компьютере
3. Как применить патч в git
4. Как сделать систему строительства в unity