Оптимизация формулы

@Kiriver · Регистрация: 09.04.2018

Author24 — интернет-сервис помощи студентам

Добрый день!

Помогите, пожалуйста оптимизировать работу. Есть таблица в ней необходимо подсчитать количество аппаратуры. Сам подсчет я сделал (ячейки С20-G20). Вопрос в том как это сделать "более правильно", что ли? Ведь пока наименований четыре, то все нормально, а если наименований будет 30-40, то прописывать такую формулу - это жесть.

И еще подскажите, как в Excel'е убрать лишние пробелы в конце строки в указанном диапазоне? Trim подойдет? И, если - да, то как это реализовать правильно? VBA осваиваю понемногу, но больше применительно к Аксессу.

@Dinoxromniy · 30.07.2018, 21:44

Сообщение от Kiriver

Ведь пока наименований четыре, то все нормально, а если наименований будет 30-40,

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

@Kiriver · 30.07.2018, 21:53 **[ТС]**

Dinoxromniy, спасибо, что-то не подумал даже про сводную таблицу! а по поводу пробелов поможете?

@Dinoxromniy · 30.07.2018, 22:21

Сообщение от Kiriver

а по поводу пробелов

Каждая тема согласно правилам посвящается одному вопросу, на это правило вы можете ориентироваться в будущем при создании новых тем.
Стандартная функция Excel СЖПРОБЕЛЫ(ссылка_на_ячейку_с_текстом) уберет все пробелы в тексте, за исключением одиночных пробелов между словами (то есть все двойные, а также в начале и конце текста).
Если убрать нужно только в конце - например формулами (в примере - для ячейки А1)

Код

=ЕСЛИ(ПРАВСИМВ(A1;1)=" "; ЛЕВСИМВ(A1;ДЛСТР(A1)-1);A1)

или кодом на VBA сразу для всех выделенных ячеек:

Visual Basic

Public Sub remove_r_s()
Dim rngCell As Range
For Each rngCell In Selection.Cells
    rngCell.Value = RTrim(rngCell.Text)
Next rngCell
End Sub

@Kiriver · 30.07.2018, 22:24 **[ТС]**

Dinoxromniy, понял, спасибо. Насколько я понимаю, из данного кода надо сделать макрос, выделить ячейки и запустить его?

@Dinoxromniy · 30.07.2018, 22:33

Сообщение от Kiriver

из данного кода надо сделать макрос

Скопировать код в VBA редактор (alt+f11), в модуль/книгу/лист, выделить ячейки и запустить через F5.
Следует учитывать, что код меняет любое содержимое ячейки на отображаемый текст, включая например текст из формата. Если в ячейках текст - это не имеет большого значения, а вот с числами нужно быть осторожным - могут перестать складываться.

@Kiriver · 31.07.2018, 21:43 **[ТС]**

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

@Dinoxromniy · 31.07.2018, 22:15

Сообщение от Kiriver

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

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

Код

=L22 & "-" &  ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Наименование";$L$21;"Наименование";L22) & ","

которая даст результат "клавиатура-4,"
Затем протянуть ее вниз по всем строкам - результатом будут аналогичные строчки по каждому наименованию.
Затем этот новый столбик нужно просто склеить, если не ошибаюсь, в 2016 excel это может функция "СЦЕПИТЬ" - достаточно просто выделить столбец. Если у вас более ранние версии, то нужно вводить еще один допстолбец (в вашем примере - O22 и ниже). В нем верхняя ячейка будет с формулой

Код

=ячейка_слева

или оно же для O22

Код

=N22

вторая и следующие ячейки столбца O будут с формулой

Код

=ячейка_выше & ячейка_слева

или оно же для ячейки O23

Код

 =O22 & N23

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

@Kiriver · 31.07.2018, 22:21 **[ТС]**

Спасибо, пошел пробовать.

@Dinoxromniy Динохромный 1375 / 749 / 271 Регистрация: 22.12.2015 Сообщений: 2,341
	30.07.2018, 21:44	2
	Сообщение от Kiriver Ведь пока наименований четыре, то все нормально, а если наименований будет 30-40, Человеку нужен был телефон - и он конечно же был изобретен. Вам нужна сводная таблица. Выделите данные в столбце "С" включая заголовок "Наименование". На вкладке "вставка" нажмите кнопку "сводная таблица" (самая левая), укажите вставку в существующий лист. В диалоге "Поля сводной таблицы" поставьте галочку на единственное доступное поле - "Наименование", затем ухватите его мышкой и перетащите в область "значения" (самая правая нижняя). По умолчанию оно отобразит количество, т.к. суммировать нечего (сие можно настроить как вам удобно). При изменении данных сводную таблицу нужно будет обновить (она сама отследит как изменения в количестве, так и изменения в перечне наименований). 1

@Kiriver 431 / 239 / 86 Регистрация: 09.04.2018 Сообщений: 1,023
	30.07.2018, 21:53 [ТС]	3
	Dinoxromniy, спасибо, что-то не подумал даже про сводную таблицу! а по поводу пробелов поможете? 0

@Kiriver 431 / 239 / 86 Регистрация: 09.04.2018 Сообщений: 1,023
	30.07.2018, 22:24 [ТС]	5
	Dinoxromniy, понял, спасибо. Насколько я понимаю, из данного кода надо сделать макрос, выделить ячейки и запустить его? 0

@Dinoxromniy Динохромный 1375 / 749 / 271 Регистрация: 22.12.2015 Сообщений: 2,341
	30.07.2018, 22:33	6
	Сообщение от Kiriver из данного кода надо сделать макрос Скопировать код в VBA редактор (alt+f11), в модуль/книгу/лист, выделить ячейки и запустить через F5. Следует учитывать, что код меняет любое содержимое ячейки на отображаемый текст, включая например текст из формата. Если в ячейках текст - это не имеет большого значения, а вот с числами нужно быть осторожным - могут перестать складываться. 0

@Kiriver 431 / 239 / 86 Регистрация: 09.04.2018 Сообщений: 1,023
	31.07.2018, 21:43 [ТС]	7
	Dinoxromniy, подскажите еще такой вопрос: возможно ли из сводной таблицы перенести данные так, чтобы на выходе получился вид, как в ячейке С20...? Просто важен не только подсчет, но и его представление, нужно именно как в таблице. 0

@Dinoxromniy Динохромный 1375 / 749 / 271 Регистрация: 22.12.2015 Сообщений: 2,341
	31.07.2018, 22:15	8
	Сообщение от Kiriver возможно ли из сводной таблицы перенести данные так, чтобы на выходе получился вид, как в ячейке С20...? Ну полагаю, что полностью автоматический перенос будет труднореализуем, не уверен, что это в принципе можно сделат - возможно коллеги подскажут метод. Ну а с костылями - почему нет. Допустим, ваша сводная таблица вставлена в ячейку L21, то вы можете правее нее в N22 вбить формулу Код =L22 & "-" & ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Наименование";$L$21;"Наименование";L22) & "," которая даст результат "клавиатура-4," Затем протянуть ее вниз по всем строкам - результатом будут аналогичные строчки по каждому наименованию. Затем этот новый столбик нужно просто склеить, если не ошибаюсь, в 2016 excel это может функция "СЦЕПИТЬ" - достаточно просто выделить столбец. Если у вас более ранние версии, то нужно вводить еще один допстолбец (в вашем примере - O22 и ниже). В нем верхняя ячейка будет с формулой Код =ячейка_слева или оно же для O22 Код =N22 вторая и следующие ячейки столбца O будут с формулой Код =ячейка_выше & ячейка_слева или оно же для ячейки O23 Код =O22 & N23 самая нижняя ячейка столбца O и будет содержать запись в нужном вам виде. Понятно, что при пересчете наименований вся эта конструкция развалится, ну тут что попишешь. Вероятно, существует какое-то более элегантное решение, но мне оно на ум не идет. 1

@Kiriver 431 / 239 / 86 Регистрация: 09.04.2018 Сообщений: 1,023
	31.07.2018, 22:21 [ТС]	9
	Спасибо, пошел пробовать. 0