Excel电子表格 [英] Excel Spreadsheet

问题描述

您好，

我目前处于这种情况。我需要一个电子表格，我似乎无法自我发展。创建此电子表格一直是一个漫长且精神疲惫的经历。我想要创建的是一个电子表格，它有多个列，
一个项目和一个价格，我希望excel列出可用的组合，并将这些单独组合的总计相加。示例：

I am currently in a situation. I am in need of a spreadsheet, that I can't seem to develop myself. It has been a long and mentally tiring experience to create this spreadsheet. What I am looking to create is a spreadsheet, that has multiple columns with a item and a price, and I want excel to list the possible combinations available and add up the totals for those individual combinations. Example:

白面包$ 3    Mayo $ 2     生菜$ 2 NBSP; 白面包$ 3梅奥$ 2，生菜$ 2 = $ 7

White bread $3   Mayo $2      Lettuce $2  White bread $3, Mayo $2, Lettuce $2= $7

小麦面包$ 4 NBSP; 芥末$ 1 NBSP;泡菜$ 3  麦面包$ 4中，芥末$ 1，生菜$ 2，酱菜$ 3 = $ 10 LT; - 理想

Wheat bread $4  Mustard $1  Pickles $3   Wheat bread $4, Mustard $1, Lettuce $2, Pickles $3= $10 <-ideal

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
 西红柿$ 4

                                              Tomatoes $4

我还希望它能够从一列中选择多个项目，所以如果我想要它要从第3列到第3列选择2种调味品，它会。所以我想我想要创建的是一个电子表格，它列出了我创建的给定总数中所有可能的组合
，但它也让我可以选择在给定列中添加多个项目。

I also want it to be able to pick multiple items from one column, so if I want it to pick 2 condiments from column 3 to ad to the combination, it will. So in all I guess what I am trying to create is a spreadsheet, that list all possible combinations within a given total number I create, but it also gives me the option to add more than one item in a given column.

请有人帮助我，我一直试图创建这个电子表格两周了!!

Please can someone help me, I have been trying to create this spreadsheet for two weeks now!!

推荐答案

尝试以下代码。它是一个非常准确的结构，它可以生成一些集合的所有可能组合（数学集合的动力集）。

Try below code. It is a very barebone structure which generates all possible combination of some collection (Mathematically Power Set of a Set).

这不是你想要的，但接近它。上传一个示例文件，向我们展示给定输入的代码运行后输出的外观。我将做必要的修改。

It is not exactly what you want but close to it . Upload a sample file where show us how the output will look after code run for a given input. I shall do required modification.

Sub CreateC()


    'loop variable
    Dim i As Long
    Dim k As Long
    
    'Inuse Variable
    Dim sNum As String
    Dim n As Long
    Dim c As Long
    
    Dim aNum(1 To 4) As String
    
    
    
    Application.ScreenUpdating = False
    
    'Getting number of element in input set
    n = 4
    'Getting number of elements in power set.
    c = 2 ^ n - 1
    
    'Creating New Worksheet and formatting for output
    Worksheets.Add
    

    aNum(1) = "Mustard"
    aNum(2) = "Lettuce"
    aNum(3) = "Pickles"
    aNum(4) = "Mayo"
    
    'Creating Power Set of input set
    For i = 1 To c
        
        sNum = CStr(Format(WorksheetFunction.Dec2Bin(i), String(n, "0")))
        
        For k = 1 To n
            If Mid(sNum, k, 1) = 1 Then
                Cells(i + 1, k + 1).Value = aNum(k)
            End If
        Next k
        
    Next i
        
    
    ActiveSheet.UsedRange.EntireColumn.AutoFit
    
End Sub

这篇关于Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！